IBM Cognos 8 Report Studio Cookbook

IBM Cognos 8 Report Studio Cookbook

By : Abhishek Sanghani

Buy this Book

IBM Cognos 8 Report Studio Cookbook

By: Abhishek Sanghani

Buy this Book

Overview of this book

Cognos Report Studio is widely used for creating and managing business reports in medium to large companies. It is simple enough for any business analyst, power user, or developer to pick up and start developing basic reports. However, when it comes to developing more sophisticated, fully functional business reports for wider audiences, report authors will need guidance. This book helps you understand and use all the features provided by Report Studio to generate impressive deliverables. It will take you from being a beginner to a professional report author. It bridges the gap between basic training provided by manuals or trainers and the practical techniques learned over years of practice. This book covers all the basic and advanced features of Report Authoring. It begins by bringing readers on the same platform and introducing the fundamental features useful across any level of reporting. Then it ascends to advanced techniques and tricks to overcome Studio limitations.Develop excellent reports using dimensional data sources by following best practices that development work requires in Report Studio. You will also learn about editing the report outside the Studio by directly editing the XML specifications. Provide richness to the user interface by adding JavaScript and HTML tags. The main focus is on the practical use of various powerful features that Report Studio has to offer to suit your business requirements.

IBM Cognos 8 Report Studio Cookbook

Credits

About the Author

About the Reviewers

Preface

Free Chapter

Report Authoring Basic Concepts

Introduction

Summary filter and detail filter

Sorting grouped values

Aggregation and rollup aggregation

Implementing IF THEN ELSE in filters

Data formatting options: Dates, numbers, and percentages

Creating sections

Hiding column in crosstab

Prompts: Display value versus use value

Advanced Report Authoring

Introduction

Adding a cascaded prompt

Creating nested report: Defining master detail relationship

Writing back to the database

Adding conditional formatting

Running difference: Negative values in brackets and red

New conditional styling with v8.3 onwards

Conditional block: Many reports in one

Drill-through from crosstab intersection

Overriding crosstab intersection drill-through definition

Tips and Tricks: JavaScripts

Introduction

Dynamic default value for prompt

Changing title of the dropdown box

Listbox: Applying Deselect All on rerun

Validating textbox prompts

Show/Hide prompt controls at runtime

Automatic selection and submission of values

Manipulating Date Time control

Variable width bar chart using JavaScript

Tips and Tricks: Report Page

Introduction

Showing images dynamically (Traffic Light report)

Handling missing image issue

Dynamic links to external website (Google Map example)

Alternating drill link

Showing tool tip on reports

Achieving minimum column width

Merged cells in Excel output

Worksheet name in Excel output

Conditional column titles

XML Editing

Introduction

Quickly changing drill targets

Copy-pasting drill link

Removing references to old package and namespaces

A hidden gem in XML—row level formatting

Writing Printable Reports

Introduction

Container size and rows per page

Eliminating orphan rows

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

Avoiding page title repetition

Horizontal pagination

Choosing output format from prompt

Choosing right colors

Defining page set

Caution about HTML items and JavaScript

Working with Dimensional Models

Introduction

List report or crosstab report

Filtering rows or columns

Filtering whole report

Adding prompt into slicer and its limitation

New feature for zero suppression

Level independent MUN for parent child hierarchy

Aggregation versus pre-aggregated values

RoleValue() function

Swapping dimensions using string manipulation

Macros

Introduction

Add data level security using CSVIdentityMap macro

Using prompt macro in native SQL

Making prompt optional

Adding token using macro

Using prompt and promptmany macros in query subject

Showing the prompt values in report based on security

String operations to get it right

Showing user name in footer

Using Report Studio Better

Introduction

Report Studio environmental options

Copy-pasting partial or full report

Copy-pasting whole reports—8.2 to 8.4 possible

Setting execution time out

Setting maximum rows limit

Handle slow report validation

Capturing query

Browsing values from data expression window

Page structure view

Pick-up and apply style

Grab them all practice

Some More Useful Recipes

Introduction

Timing report execution

Missing value versus zero value

Over-riding data formatting defined in database

Conditional drill-through

Dynamically switching reports using iFrame

Best Practices

Introduction

Reducing number of query items

Highlighting hidden items

Relative path for images

Taming the JavaScripts

Customize classes for report-wide effect

Creating templates

Regression testing

Code comments

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

Customer Reviews

5 star

4 star

3 star

2 star

1 star

Aggregation versus pre-aggregated values

The biggest advantage of using a cube as data source is its capability to pre-aggregate values. This recipe will show you how to exploit this feature of cube in your Cognos reports.

Getting ready

Create a simple crosstab report using GO Sales SSAS Cube package. Pull Organization Code2 and Code3 on rows, and Current Year on columns. Drag Stmt Year in the intersection.

How to do it...

First, we will create aggregation using standard method. For that, select the Organization Code3 row.
From the toolbar, click on the Aggregate button and choose Aggregate.
Run the report to test it.
Now go back to the Report Studio. Delete the new row created for Aggregation.
Go to second tab (Data Item) of Insertable objects. Drag Organization Code2 again on report and drop it under Organization Code3 and format this row to be bold; shown as follows:
Now run the report to test it.
Notice that the aggregated values are the same.