- IBM Cognos 8 Report Studio Cookbook
- Abhishek Sanghani
- 747字
- 2021-08-06 17:15:46
Creating nested report: Defining 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 last year.
We need to produce a list report with 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 Product | Product line, Product | Product name and Sales fact | Unit cost in the list.
How to do it...
- 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 Chartobject from the Insertable Objects pane onto the report as a column.
- Choose an appropriate chart type. In this recipe, we will choose "Line with Markers".
- From Source pane, drag Quantity from Sales fact onto the chart as the Default measure. Drag Month key from time dimension under Category and Product name from product dimension as the Series.
- Now click anywhere on the chart and choose Data | Master Detail Relationship from the menu bar.
- Create a new link and connect Product name items from both the queries.
- Click the OK button to come back on the Report Page. Now select the Y1 Axis of chart by clicking on it.
- Change its Use Same Range for All Instances property to No.
- Now click on the Chart and click the Filter
button from toolbar. - Define a detailed filter on Current Year from time dimension as required. In this recipe, I will hard code it to 2004. So, the filter is defined as:
[Sales (query)].[Time dimension].[Current year]=2004.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.
How it works...
Cognos Report Studio allows one report object to be nested within another list report. In the previous recipe of creating sections, we saw that the Report Studio automatically creates nesting for us. In this recipe, we manually created nesting for finer controls.
We need to define this relationship in the following cases:
- When outer and inner report objects use different queries.
- For any nesting other than 'List within 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 Master-Detail relationship.
Hence, the Detail query is executed multiple times, each time with different filtering.
As it has to retrieve very small amount of information at a time, a page of output is returned very quickly. This is particularly useful when the report is accessed interactively.
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 (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 name and Unit cost) to the users.
See also
With version 8.4 of Cognos Report Studio, a new feature called 'Microchart' is introduced. This type of chart is particularly useful for such in-line spark chart kind of representation.
This version also allows you to quickly insert a chart within a crosstab by right-clicking on row titles (not one-click action for a list report though).
I would highly recommend exploring and experimenting around these features.
- PS是這樣玩的：輕松掌握 Photoshop 通關秘籍
- RAW攝影后期從入門到精通：Photoshop+Lightroom雙修精解
- Cacti 0.8 Beginner's Guide
- BPEL Cookbook: Best Practices for SOA/based integration and composite applications development
- 中文版Illustrator CC基礎培訓教程（移動學習版）
- 24小時全速學會Photoshop 2021
- After Effects CC 2018影視特效與合成案例教程
- 中文版3ds Max 2021完全自學教程
- 計算機圖形圖像處理Photoshop CS6項目教程
- Instant Testing with QUnit
- 中文版After Effects CC 2018 動漫、影視特效后期合成秘技
- Visio圖形設計從新手到高手（兼容版）
- 玩轉電子設計：基于Altium Designer的PCB設計實例（移動視頻版）
- Instant Apache Sqoop
- 中文版Flash CC實例教程