- IBM Cognos 8 Report Studio Cookbook
- Abhishek Sanghani
- 647字
- 2021-08-06 17:15:43
Summary filter and detail filter
Business owners need to see the sales quantity of their product lines to plan their strategy. They want to concentrate only on the highest selling product for each product line. They would also like the facility to select only those orders that are shipped in a particular month, for this analysis.
In this recipe, we will create a list report with product line, product name, and quantity as columns. We will create optional filter on Shipment Month Key. Also, we will apply correct filtering to bring only the highest sold product per product line.
Getting ready
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.
How to do it...
- We will start with adding the optional filter on shipment month. To do that, click anywhere on the list report on Report Page. Select Filters from the toolbox.
- In the Filters dialog box, add a new detail filter. Define filter as:
[Sales (query)].[Time (ship date)].[Month key (ship date)] = ?ShipMonth?
- Set the usage to Optional.
- Now we will add a filter to bring only highest sold Product per Product line. To achieve this, select Product line and Product name (Ctrl+select) and click on the Group button from toolbox.
This will create grouping shown as follows:
- Now select the list and click on Filter button again. This time go to the Summary filters tab and add a new filter.
- Define the filter as:
[Quantity] = maximum([Quantity] for [Product line]). - Set usage to Required and set the scope to Product name.
- Now run the report to test the functionality. You can enter 200401 as Month Key as that has data in the Cognos supplied sample.
How it works...
Report Studio allows you to define two types of filters. Both work at different grain and hence have different applications.
The detail filter works at the lowest level of granularity in selected cluster of objects. In our example, this grain is the 'Sales entries' stored in the 'Sales fact'. By putting a detail filter on shipment month, we are making sure that only those sales entries which fall within the selected month are pulled out.
In order to achieve the highest sold product per product line, we need to consider the aggregated sales quantity for the products.
If we put a detail filter on quantity, it will work at sales entry level. You can try putting a detail filter of [Quantity] = maximum([Quantity] for [Product line]) and you will see that it gives incorrect results.
So, we need to put a summary filter here. In order to let the query engine know that we are interested in filtering sales aggregated at product level, we need to set the SCOPE to Product name. This makes the query engine calculate [Quantity] at product name level and then allows only those product names where the value matches maximum([Quantity] for [Product line]).
There's more...
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.
The detail filter can also be set to apply after aggregation (by changing the application property). However, I think this kills the logic of detail filter. Also, there is no control on the grain at which the filter will apply. Hence, Cognos sets it to before aggregation by default, which is the most natural usage of the detail filter.
See also
Please read the "Implementing IF THEN ELSE in filtering" recipe in this chapter.
- 性能測試從零開始
- Protel DXP 2004 SP2原理圖與PCB設計（第4版）
- 中文版Premiere Pro CC入門教程
- 移動App測試的22條軍規(guī)
- Flash CC動畫制作案例教程
- 數(shù)碼攝影后期處理秘笈：Photoshop CC專業(yè)調(diào)色（第2版）
- DSP開發(fā)寶典
- Blender 3D Architecture, Buildings, and Scenery
- 圖像處理中的數(shù)學修煉（第2版）
- Premiere CC視頻編輯入門與應用
- PHP 5 Social Networking
- 網(wǎng)店美工設計：Photoshop CC案例教程（視頻指導版）
- 零基礎攝影后期修圖 Photoshop照片處理輕松入門
- Audition CC音頻處理完全自學一本通
- Creo 4.0中文版基礎教程