IBM Cognos 10 Report Studio Cookbook（Second Edition）

上QQ阅读APP看书，第一时间看更新

Creating a nested report – defining the master-detail relationship

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.

Getting ready

Create a simple list report based on the Sales (query) namespace. Pull Products / Product line, Products/Product and Sales fact / Unit cost in the list.

How to do it...

To complete this recipe, we need to create a relationship between the list report created and a chart report that will be embedded into it. We can do this by defining a master-detail link between them.

We already have a list report that shows the product lines, products, and corresponding unit costs. Please make sure that appropriate sorting and aggregations are applied to the columns.
Now we will add a nested Chart object to show the sales trend for each product.
Drag a new Chart object from the Toolbox pane into the report as a column as shown in the following screenshot:
Choose an appropriate chart type. In this recipe, we will choose Line with Markers.
From the source pane, drag Quantity from Sales fact into the chart as the Default measure (y-axis). Drag Month key from the Time dimension under Categories (x-axis) and Product from the Products dimension under Series as shown in the following screenshot:

Tip

Please note that we are using the month key here in order to show the monthly figures in the correct order. Later on you can use a category label to show month names. Directly pulling the month name results in alphabetic sorting is an incorrect trend.
Now click anywhere on the chart and choose Data / Master Detail Relationships from the menu bar.
Create a new link and connect Product items from both the queries as shown in the following screenshot:
Click on the OK button to come back on the report page. Now select the Y1 Axis of the chart by clicking on it.
Change its Use Same Range for All Instances property to No.
Now click on the chart and click on the Filter button from the toolbar and then click on Edit Filters.
Define a detailed filter on Year from the Time dimension as required. In this recipe, we will hard code it to 2012. So, the filter is defined as [Sales (query)].[Time].[Year] = 2012.
Though in practical cases, you would have to filter for year, rather than hard-coding.
Run the report to test it.
Update the chart properties (size, marker, color, and so on) for better presentation as shown in the following screenshot:

How it works...

Cognos Report Studio allows one report object to be nested within another list report. In the previous recipe, we saw that the Report Studio automatically creates nesting for us. In this recipe, we manually created nesting for finer control.

The master-detail relationship

We need to define this relationship in the following cases:

When outer and inner report objects use different queries
For any nesting other than a list within a list

In order to generate the report, Cognos first fires the master query on the database to retrieve the records. Then for each record, it fires the detail query with the filtering as defined in the master-detail relationship.

Hence, the detail query is executed multiple times, each time with different filtering.

As it has to retrieve very small amounts of information at a time, a page of output is returned very quickly. This is particularly useful when the report is accessed interactively.

Tip

In Report Studio, you can turn on an option from VIEW Menu | Visual Aid | Show Master Detail Relationships.

This will highlight the data containers that have master-detail relationships with an icon like this: The master-detail relationship . By double-clicking on the icon, you can quickly open the relationship for viewing and editing.

There's more...

By using separate queries for the outer and inner report object in nesting, we can have more control on what information is retrieved. In this example, we want to show a sales trend (chart) only for one year—we hard coded it to 2004. Hence, the chart query needs to be filtered on year.

However, the outer query (the list of product lines and products) does not need this filtering.

As you can see in the report output, there are some rows with no corresponding graph. For example, Personal Accessories / Auto Pilot. This means there was no selling of this product in the year 2004. If we had used the same query for the list and the chart, this row would have been filtered out resulting in loss of information (product and unit cost) to the users.