IBM Cognos 8 Report Studio Cookbook: Over 80 great recipes for taking control of Cognos 8 Report Studio

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

You now know how filtering, sorting, and aggregations 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 current industrial environments.

Business owners want to see sales made by employees. They also want the facility to limit the report to 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 last year.

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

This 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 want to then write some comments for the products from the same interface. The comments need to be stored in database for future retrieval and updating.

You would 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 months 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.

In this recipe, you will learn about the new conditional styles property introduced in Report Studio which, in my humble opinion, is the best feature added to make a report author's life less tedious.

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

As shown in 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 block.

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: Product | Product lines and Sales fact | Quantity.

• List 2: Time dimension | Current year, Time dimension | Current month and Sales fact | Quantity.

• List 3: Retailer site | Region and Sales fact | Quantity.

Define appropriate grouping, sorting, and aggregation for all the list objects. Make sure that all objects use different queries.

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 methods on column, we need to display Order methods and Product lines as columns.

The rows display Month. 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.