IBM Cognos 10 Report Studio Cookbook, Second Edition: Getting the most out of IBM Cognos Report Studio is a breeze with this recipe-packed cookbook. Cherry-pick the ones you want or go through the tutorial step by step – either way you'll end up with some highly impressive reports. , Second Edition

Now as you have implemented the recipes in Chapter 1, Report Authoring Basic Concepts or read through them, I am confident that we are on the same page about the fundamental techniques of report authoring.

Now you know how filtering, sorting, and aggregation work. You also know how to apply data formatting, create sections, and hide columns. You are also now aware of how to add new prompts and select appropriate options in the prompt wizard.

Based on this understanding, we will now move on to some advanced topics; including cascaded prompts, nested reports, and conditional blocks. We will also examine some techniques around drill-through links. These will enable you to create professional reports as required in the current industrial environment.

Business owners want to see sales made by employees. They also want the facility to limit the report to a certain region, country, or employee.

When they select a region, they would like the country pick-list to automatically reduce to the countries falling in that region. Similarly, the employee pick-list should also reduce when they pick a country.

Users want to see product lines, products, and corresponding unit costs. For every product, they also want to see the trend of sales over the last year.

We need to produce a list report with the required information and nest a line chart within it to show the sales trend.

Writing back to the database is perhaps the most frequently requested functionality by business users—writing some notes or comments back to database, for a particular entry on the report. Though there is no direct functionality provided in Cognos Report Studio for this, it is still possible to achieve it by putting together multiple tools. This recipe will show you how to do that.

The business wants to see sales figures by products. They then want to write some comments for the products from the same interface. The comments need to be stored in the database for future retrieval and updating.

You will need access on the backend database and Framework Manager for this recipe.

The business wants to see company sales figure by years and quarters. They want to highlight the entries where sales are below 5,000,000.

We will assume that database provides us the Quarter number and we need to convert that to words. We will use conditional formatting for that. Also, where sales is below 5 million, the cell will be shown in red using another conditional variable.

Business owners need to see the sales figures by month and their month-on-month difference.

If the difference is negative (fall in sales) then it needs to be shown in red and values need to be in brackets.

Assume that the following report needs to be formatted such that quantities below 1.7 million will be highlighted with red background and those above 2 million should be green. Also, we need the negative values for Running Difference (month-on-month) to be shown in red and in brackets as shown in the following screenshot:

As shown in the previous recipe, this would have needed us to define two conditional variables. Then attach each to the corresponding column as Style variable to define the styles. With one more such numeric column, the author had to define one more variable and repeat the exercise. Let's see how the new conditional styling feature solves this problem.

The purpose of this recipe is to introduce you to a very useful and powerful control of Report Studio called conditional blocks.

Users want a report on sales figures. They want the facility to split the numbers by product lines, periods, or retailer region, any one at a time. For convenience purposes, they don't want three different reports, instead they are looking for one report with the facility to choose between the report types.

Getting ready

Create a report with three list objects. Define the list columns as follows:

• List 1: Products / Product line and Sales fact / Quantity

• List 2: Time / Year, Time / Month, and Sales fact / Quantity

• List 3: Retailers / Region and Sales fact / Quantity

Define appropriate grouping, sorting, and aggregation for all the list objects. Make sure that all objects use different queries as shown in the following screenshot:

We have a crosstab report that shows sales quantity by month and order method. We need to create drill-through links from months and sales values.

Let us consider an extension of the last recipe. Let us say the users want to see a discontinuous crosstab as main report. Instead of just Order method type as a column, we need to display Order method type and Product line as columns.

The rows display Month. The measure is sales quantity.

The drill-through from the intersection has to go to the appropriate report depending on whether the column is Product line or Order method type.