IBM Cognos 8 Report Studio Cookbook

By Abhishek Sanghani

Written in cookbook style, this book offers learning and techniques through recipes. It contains step-by-step instructions for Report Studio 8 users to author effective reports. The book is designed in such a way that you can refer to things chapter by chapter, and read them in no particular order.You will see a new fictional business case in each recipe that will relate to a real-life problem and then you will learn how to crack it in Report Studio.If you are a Business Intelligence or MIS Developer (programmer) working on Cognos Report Studio who wants to author impressive reports by putting to use what this tool has to offer, this book is for you. You could also be a Business Analyst or Power User who authors his own reports and wants to look beyond the conventional features of Report Studio 8.

• Language: English
• Publisher: Packt Publishing
• Release date: Jun 1, 2010
• ISBN: 9781849680356

Book preview

Table of Contents

IBM Cognos 8 Report Studio Cookbook

Credits

About the Author

About the Reviewers

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Errata

Piracy

Questions

1. Report Authoring Basic Concepts

Introduction

Summary filter and detail filter

Getting ready

How to do it...

How it works...

Detail filter

Summary filter

There's more...

Before/After aggregation

See also

Sorting grouped values

Getting ready

How to do it...

How it works...

There's more...

Aggregation and rollup aggregation

Getting ready

How to do it...

How it works...

Aggregation property

Rollup aggregation

Multiple aggregations

There's more...

Implementing IF THEN ELSE in filters

Getting ready

How to do it...

How it works...

There's more...

Data formatting options: Dates, numbers, and percentages

Getting ready

How to do it...

How it works...

CAST function

Numerical format

% Margin

There's more...

Creating sections

Getting ready

How to do it...

How it works...

There's more...

See also

Hiding column in crosstab

Getting ready

How to do it...

How it works...

There's more...

Prompts: Display value versus use value

Getting ready

How to do it...

How it works...

There's more...

2. Advanced Report Authoring

Introduction

Adding a cascaded prompt

Getting ready

How to do it...

How it works...

Cascaded source

There's more...

Auto submit

More info

See also

Creating nested report: Defining master detail relationship

Getting ready

How to do it...

How it works...

Master Detail relationship

There's more...

See also

Writing back to the database

Getting ready

How to do it...

How it works...

There's more...

Adding conditional formatting

Getting ready

How to do it...

How it works...

String variable

Boolean variable

Report language variable

There's more...

Style variable property

Text source variable property

Running difference: Negative values in brackets and red

Getting ready

How to do it...

How it works...

Running difference

Showing negative values in red and brackets

New conditional styling with v8.3 onwards

Getting ready

How to do it...

How it works...

There's more...

Conditional block: Many reports in one

Getting ready

How to do it...

How it works...

There's more...

Drill-through from crosstab intersection

Getting ready

How to do it...

How it works...

Overriding crosstab intersection drill-through definition

Getting ready

How to do it...

How it works...

There's more...

3. Tips and Tricks: JavaScripts

Introduction

Dynamic default value for prompt

Getting ready

How to do it...

How it works...

SPAN

GetElementsByTagName

SelectedIndex

CanSubmitPrompt

There's more...

Changing title of the dropdown box

Getting ready

How to do it...

How it works...

There's more...

Listbox: Applying Deselect All on rerun

Getting ready

How to do it...

How it works...

There's more...

Validating textbox prompts

Getting ready

How to do it...

How it works...

Show/Hide prompt controls at runtime

Getting ready

How to do it...

How it works...

There's more...

More Info

Automatic selection and submission of values

Getting ready

How to do it...

How it works...

There's more...

Manipulating Date Time control

Getting ready

How to do it...

How it works...

There's more...

See also

Variable width bar chart using JavaScript

Getting ready

How to do it...

How it works...

There's more...

4. Tips and Tricks: Report Page

Introduction

Showing images dynamically (Traffic Light report)

Getting ready

How it works...

There's more...

More info

Handling missing image issue

Getting ready

How to do it...

How it works...

There's more...

Dynamic links to external website (Google Map example)

Getting ready

How to do it...

How it works...

There's more...

Alternating drill link

Getting ready

How to do it...

How it works...

Render variable

There's more...

Showing tool tip on reports

Getting ready

How to do it...

How it works...

There's more...

See also

Achieving minimum column width

Getting ready

How to do it...

How it works...

Merged cells in Excel output

Getting ready

How to do it...

How it works...

Worksheet name in Excel output

Getting ready

How to do it...

How it works...

There's more...

Conditional column titles

Getting ready

How to do it...

How it works...

5. XML Editing

Introduction

Quickly changing drill targets

Getting ready

How to do it...

How it works...

There's more...

Copy-pasting drill link

Getting ready

How to do it...

How it works...

There's more...

Removing references to old package and namespaces

Getting ready

How to do it...

How it works...

A hidden gem in XML—row level formatting

Getting ready

How to do it...

How it works...

There's more...

See also

6. Writing Printable Reports

Introduction

Container size and rows per page

Getting ready

How to do it...

How it works...

There's more...

Eliminating orphan rows

Getting ready

How to do it...

How it works...

There's more...

Defining page orientation and size (also override for one page)

Getting ready

How to do it...

How it works...

Avoiding page title repetition

Getting ready

How to do it...

How it works...

There's more...

Horizontal pagination

Getting ready

How to do it...

How it works...

There's more...

Choosing output format from prompt

Getting ready

How to do it...

How it works...

There's more...

Choosing right colors

Getting ready

How to do it...

How it works...

There's more...

Defining page set

Getting ready

How to do it...

How it works...

Caution about HTML items and JavaScript

Getting ready

How to do it...

How it works...

7. Working with Dimensional Models

Introduction

List report or crosstab report

Getting ready

How to do it...

How it works...

Filtering rows or columns

Getting ready

How to do it...

How it works...

There's more...

Filtering whole report

Getting ready

How to do it...

How it works...

There's more...

Adding prompt into slicer and its limitation

Getting ready

How to do it...

How it works...

There's more...

More Info

New feature for zero suppression

Getting ready

How to do it...

How it works...

There's more...

Level independent MUN for parent child hierarchy

Getting ready

How to do it...

How it works...

Aggregation versus pre-aggregated values

Getting ready

How to do it...

How it works...

There's more...

RoleValue() function

Getting ready

How to do it...

How it works...

Swapping dimensions using string manipulation

Getting ready

How to do it...

How it works...

See also

8. Macros

Introduction

Add data level security using CSVIdentityMap macro

Getting ready

How to do it...

How it works...

There's more...

Using prompt macro in native SQL

Getting ready

How to do it...

How it works...

There's more...

Making prompt optional

Getting ready

How to do it...

How it works...

Optional_defaultValue

Pure_Optional

There's more...

Adding token using macro

Getting ready

How to do it...

How it works...

Using prompt and promptmany macros in query subject

Getting ready

How to do it...

How it works...

Showing the prompt values in report based on security

Getting ready

How to do it...

How it works...

String operations to get it right

Getting ready

How to do it...

How it works...

There's more...

Showing user name in footer

Getting ready

How to do it...

How it works...

There's more...

9. Using Report Studio Better

Introduction

Report Studio environmental options

Getting ready

How to do it...

How it works...

View | Visual Aid

Structure | Crosstab Node Creation Option

Tools | Options | View | Reuse Cognos Viewer Window

Tools | Options | Report Options

There's more...

Copy-pasting partial or full report

Getting ready

How to do it...

How it works...

Copy-pasting whole reports—8.2 to 8.4 possible

Getting ready

How to do it...

How it works...

Setting execution time out

Getting ready

How to do it...

How it works...

There's more...

Setting maximum rows limit

Getting ready

How to do it...

How it works...

Handle slow report validation

Getting ready

How to do it...

How it works...

There's more...

Capturing query

Getting ready

How to do it...

How it works...

Why make filters mandatory?

Query formatting

Capture the query for database

Browsing values from data expression window

Getting ready

How to do it...

How it works...

There's more...

Page structure view

Getting ready

How to do it...

How it works...

Pick-up and apply style

Getting ready

How to do it...

How it works...

Grab them all practice

Getting ready

How to do it...

How it works...

There's more…

10. Some More Useful Recipes

Introduction

Timing report execution

Getting ready

How to do it...

How it works...

Report view

Scheduling

There's more...

Missing value versus zero value

Getting ready

How to do it...

How it works...

There's more...

Over-riding data formatting defined in database

Getting ready

How to do it...

How it works...

Conditional drill-through

Getting ready

How to do it...

How it works...

There's more...

Dynamically switching reports using iFrame

Getting ready

How to do it...

How it works...

There's more...

11. Best Practices

Introduction

Reducing number of query items

Getting ready

How to do it...

How it works...

Alias member unique name

Delete unreferenced query objects

Dragging items from data items pane

Highlighting hidden items

Getting ready

How to do it...

How it works...

Relative path for images

Getting ready

How to do it...

How it works...

There's more...

Taming the JavaScripts

Getting ready

How to do it...

How it works...

Customize classes for report-wide effect

Getting ready

How to do it...

How it works...

Creating templates

Getting ready

How to do it...

How it works...

Regression testing

Getting ready

How to do it...

How it works...

There's more...

Code comments

Getting ready

How to do it...

How it works...

A. Recommendations and References

Introduction

Version controlling

Recommendation for prompt types

Cognos Mashup Service

Third-party tools for Cognos

Express Authoring Mode

CAFE

Cognos Go Office

Index

IBM Cognos 8 Report Studio Cookbook

Abhishek Sanghani

---

IBM Cognos 8 Report Studio Cookbook

Copyright © 2010 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

First published: May 2010

Production Reference: 1210510

Published by Packt Publishing Ltd.

32 Lincoln Road

Olton

Birmingham, B27 6PA, UK.

ISBN 978-1-849680-34-9

www.packtpub.com

Cover Image by Tina Negus (<[e-mail]>)

Credits

Author

Abhishek Sanghani

Reviewers

Terry Curran

Marco Hartman

Sascha Mertens

Ramesh Parcha

Acquisition Editor

Rashmi Phadnis

Development Editor

Neha Patwari

Technical Editor

Neha Damle

Copy Editor

Sanchari Mukherjee

Editorial Team Leader

Gagandeep Singh

Project Team Leader

Lata Basantani

Project Coordinator

Joel Goveya

Proofreader

Lesley Harrison

Indexer

Hemangini Bari

Production Coordinator

Alwin Roy

Cover Work

Alwin Roy

About the Author

Abhishek Sanghani was born in India and attended Mumbai University where he majored in Computer Engineering. He began his career in 2004 as a Business Intelligence and Cognos Consultant, and has worked with leading IT and Finance Services companies since then.

He pursued Finance Management degree along with his work in the field of Cognos and BI, successfully progressing and winning awards and certifications year after year. Presently, he is working in the United Kingdom, utilizing his skills of Cognos, SQL, BI and Data Warehousing. In his free time, he writes technical blogs and also provides trainings/seminars on demand. This book is his first attempt in technical authoring.

Mail ID: <[e-mail]>

Blog: http://biandcognos.blogspot.com/

I would like to thank the entire PACKT Publishing team for helping this endeavor. On the personal front, I am thankful to my loving wife Dolly for all the support, and my friend and work mate Amma Manso for all the valuable advice.

About the Reviewers

Terry Curran has worked in the IT industry for over 20 years after graduating from the University of Stirlling with an MSc in Software Engineering. He has considerable software development experience in many industries.

He has extensive knowledge of Cognos Reporting solutions, having worked with Cognos Business Intelligence software for a range of industries from Aviation to Pharmaceuticals over the past 10 years.

He is currently working as a freelance Cognos Business Intelligence consultant and contractor.

Marco Hartman is a Senior Business Intelligence Consultant, currently working at Imtech ICT Performance Solutions in the Netherlands.

He’s working with Cognos products since 2004. Starting with Cognos PowerPlay and Impromptu, he now is an expert in the Cognos 8 platform. Marco is fully certified for Cognos 8 BI (Author, Modeler, Administrator, Scorecarding), Cognos 8 Planning and Cognos TM1. Besides Cognos, he also has experience with Business Objects and Microsoft SQL Server BI.

Marco works on different projects and customers. He is a qualified consultant who understands the needs of the customers. Besides his projects he is a trainer at IBM Cognos, Netherlands. He also likes to anticipate new developments.

Marco studied Business Administration at the Radboud University in Nijmegen and specialized in ‘Knowledge and Information Management’. In his free time, he likes playing tennis and snowboarding.

Sascha Mertens graduated at the German University for Applied Sciences (HS Niederrhein) in 2001 as an engineer of economics. Focusing on the business part of his degree he began to work with Corporate Performance Management (CPM) topics in his thesis of ‘Redesign of a controlling system by means of a management information system (MIS)’.

When starting to work for Deloitte in 2001 he was trained and certified in Analyst and Contributor by Adaytum—the original software producer—before they were acquired by Cognos and subsequently by IBM. With his gained knowledge, Sascha started to work for the first German Planning project with the Volkswagen Financial Services AG which was brought up to a status that is still alive today.

With the modeling of system designs and architectural concepts as well as their implementations into CPM systems, Sascha developed all kinds of planning models such as sales planning, cost planning, personnel planning etc, up to the resulting financial plans. Closely working together with Cognos, Sascha became a beta tester for several upcoming Planning releases and a community leader in one of the leading Cognos Planning forums on the internet.

With a strong focus on the conceptual and business side, he conducted a study for the ‘State of planning within German companies’ (‘Standortbestimmung zur Planung in deutschen Unternehmen’) and offered various public webcasts such as ‘planning scenarios and simulations on board level’, ‘business planning for health insurance companies ‘, ‘decentralized sales planning within the franchise industry’, and ‘driver based planning within a fleet management company’.

During his time with Deloitte Sascha made up his way to a senior managing level and designed and implemented Cognos Planning on a full-time basis in over 15 Cognos Planning and BI projects nationally, European and overseas in the last six years.

Since 2008, Sascha has been working for conunit, a consulting firm specialized on CPM and BI solutions and ‘Cognos Partner of the year 2008 for Germany and Europe’. Within conunit Sascha and his team continue the Cognos Planning story with an offering of all kind of services around the IBM Cognos CPM and BI products including full scope projects (concept, design, and implementation), version upgrades and their migrations, as well as performance enhancements and system improvement checks.

It was a great pleasure reviewing this book. I am sure that it will be very helpful to you in setting up the Reporting system in your company. I would also like to thank people from Deloitte and Cognos that I worked with in collaborative projects who helped