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.
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.
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.
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]).
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.