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

Create a new list report based on GO Data Warehouse (query) package. From the Sales (query) namespace, bring Product | Product Line, Product | Product Name, and Sales fact | Quantity as columns.

Report Studio allows you to define two types of filters. Both work at different grain and hence have different applications.

When you define multiple levels of grouping, you can easily change the scope of summary filters to decide the grain of filtering.

For example, if you need to show only those products whose sales are more than 1000 and only those product lines whose sales are more than 25000, you can quickly put two summary filters for [Quantity] with the correct Scope setting.

Please read the "Implementing IF THEN ELSE in filtering" recipe in this chapter.

Open the report created in the previous recipe in Cognos Report Studio for further amendments.

The Sort option by default works at the detailed level. This means the non-grouped items are sorted by the specified criteria within their own groups.

Here we want to sort the product lines which are grouped (not detailed items). In order to sort the Groups, we need to define the advanced sorting option as shown in this recipe.

You can define it so for the whole list report from the Advanced Sorting dialog box. You can use different items and ordering for different groups and details.

You can also choose to sort certain groups by the data items that are not shown in the report. You need only bring those items from Source (model) to the Query, and you will be able to pick it in the sorting dialog.

Create a simple list report with Product | Product line, Product | Product name and Sales Fact | Unit Cost as columns.

In this recipe, we have seen two properties of the data items related to aggregation of the values.

The rollup aggregation of any item is important only when you create the aggregation of 'Aggregate' type. When it is set to automatic, Cognos will decide the function based on data type which is not preferred.

It is good practice to always set the aggregation and rollup aggregation to meaning function than leaving as 'automatic'.

Create a simple list report with Order Method | Order Method and Sales Fact | Sales Quantity as columns. Group by order method to get one row per method and set the aggregation for quantity to TOTAL.

The IF ELSE construct works fine when it is used in data expression. However, when we use it in a filter, Cognos often doesn't like it. It is strange because the filter is parsed and validated fine in the expression window and IF ELSE is a valid construct.

The workaround for this problem is to use the pair of AND..OR as shown in this recipe. The IF condition and corresponding action item are joined with AND clause. The ELSE part is taken care by OR operations with the reverse condition (in our example, Order Method <> 'Sales Visit').

You need not use both AND and OR clauses all the time. The filtering in this example can also be achieved by this expression:

([Sales (query)].[Retailer site].[Retailer name] = ?SalesVisitRetailer?)

or

([Order method]<>'Sales visit')

Depending on the requirement, you need to use only OR, only AND, or the combination of AND..OR.

Make sure that you cover all the possibilities.

Create a simple list report with Product | Product line, Product | Product type and Time (Ship date) | Day key (Ship date) as columns from the Sales (query) namespace.

Also add Quantity, Unit Price, and Unit Cost from the Sales Fact Query Subject.

Create grouping on Product line and Product type.

In this recipe, we are trying multiple techniques. We are using the CAST function to convert a number to date. Also, we are checking how dates can be formatted to hide certain details (for example, days) and how to change the separator. Finally, we have tested formatting options for numbers and percentage.

Please note that ideally the warehouse stores a calendar table with a 'Date' type of field , or this is made available through Framework Model. So, we need not cast the key to date every time. However, this recipe is meant to show you the technique and introduce you to the casting function.

Also, we are assuming here that business needs to see the shipment month. So, they want to see MONTH YEAR format only and we are hiding the days.

Another way of achieving this is explained as follows:

Let us change the expression for Shipment Day Key column to this: [Sales (query)].[Time (ship date)].[Day key (ship date)]/10000

Now set the Data Format to Number, with the following options:

Run the report to examine the output. You will see that we have gotten rid of the last two digits from the day key and the year part is separated from month part by a hyphen. This is not truly converted to MONTH YEAR but conveys the information.

The advantage here is that the numerical operation is faster than CAST to DATE. We can use similar techniques to cosmetically achieve the required result.

Create a simple list report with Sales order | Order number, Product | Product name, Sales fact | Quantity, and Sales fact | Unit sale price as columns.

The information we are trying to show in this report can also be achieved by normally grouping on order number. That will bring all the related records together. We can also set appropriate group/level span and sorting for better appearance.

However, in this recipe, I want to introduce another feature of Report Studio called Section.

When you create a section on a column, Report Studio automatically does the following:

Some of the advantages of creating sections are as follows:

If you create a section for a crosstab or chart report, you need to explicitly define the master-detail relationship. Chapter 2 defines the master-detail relationship.

Create a crosstab report with Sales fact | Quantity as a measure. Drag Time dimension | Current year and Month on rows, Order method | Order method on column as shown in the following screenshot. Create aggregation on measure.

Define appropriate sorting.

As you can see the year column on the left is now successfully hidden.

When we want to hide an object in Report Studio, we often set its Box Type property to None. However, in this case, that was not possible.

Try setting the box type of year column to None and run the report. It will look like the following screenshot:

As you can see, the cells have shifted to the left leaving the titles out of sync. This is most often the problem when Report Studio creates some merged cells (in our case, for the aggregations).

The solution to this is to format the column in such a way that it is hidden in the report.

This solution works best in HTML output. The excel output still has a column on the left with no data in it.

You might need to define the background colour and bordering as well, so as to blend the empty column with either the page background on left or the month column on right.

Create a simple list report with Product | Product name and Sales fact | Quantity as columns.

When you drag a prompt object from insertable objects, Report Studio launches the prompt wizard.

In the first step, you choose the parameter to be connected to the prompt. It might be an existing parameter (defined in query filter or framework model) or a new one. In this recipe, we chose to create a new one.

Then, you are asked whether you want to create a filter. If there is already a filter defined, you can uncheck this option. In our example, we are choosing this option and creating a filter on Product line code. Please note that we have chosen the numerical key column here. Filtering on a numerical key column is standard practice in data warehousing as it improves the performance of the query and uses the index.

In next step, Report Studio asks where you want to create a new query for prompt. This is the query that will be fired on database to retrieve prompt values. Here we have the option to choose a different column for display value.

In our recipe, we chose Product line as display value. Product line is the textual or descriptive column that is user friendly. It has one to one mapping with the Product line code. For example, Camping Equipment has Product Line Code of 2101.

Hence, when we run the report, we see that the prompt is populated by Product line names, which makes sense to the users. Whereas if you examine the actual query fired on the database, you will see that filtering happens on the key column, that is, Product line code.

You can also check the 'Generated SQL' from Report Studio.

For that, select the Tools | Show Generated SQL/MDX option from the menu.

It will prompt you to enter a value for the product line code (which is proof that it will be filtering on the code).

Enter any dummy number and examine the query generated for the report. You will see that the product line code (key column) is being filtered for the value you entered.

So, now you know how the 'Prompt display values' and 'Use values' work.

If you ever need to capture the prompt value selected by the user in expressions (which you will often need for conditional styling or drill-throughs), you can use the following two functions.