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 also create an optional filter on the Shipment Month Key. Also, we will apply correct filtering to bring up only the top selling product per product line.
Create a new list report based on the GO Data Warehouse (query) package. From the Sales (query) namespace, bring up Products / Product line, Products/Product, and Sales fact / Quantity as columns, the way it is shown in the following screenshot:
Here we want to create a list report that shows product line, product name, and quantity, and we want to create an optional filter on Shipment Month. The report should also bring up only the top selling product per product line. In order to achieve this, perform the following steps:
We will start by adding the optional filter on Shipment Month. To do that, click anywhere on the list report on the Report page. Then, click on Filters from the toolbar.
In the Filters dialog box, add a new detail filter. In the Create Filter screen, select Advanced and then click on OK as shown in the following screenshot:
By selecting Advanced, we will be able to filter the data based on the fields that are not part of our list table like the Month Key in our example as you will see in the next step.
Define the filter as follows:
[Sales (query)].[Time (ship date)].[Month key (ship date)] = ?ShipMonth?
Validate the filter and then click on OK.
Set the usage to Optional as shown in the following screenshot:
Now we will add a filter to bring only the highest sold product per product line. To achieve this, select Product line and Product (press Ctrl and select the columns) and click on the group button from the toolbar. This will create a grouping as shown in the following screenshot:
Now select the list and click on the filter button again and select Edit Filters. This time go to the Summary Filters tab and add a new filter. In the Create Filter screen, select Advanced and then click on OK.
Define the filter as follows:
[Quantity] = maximum([Quantity] for [Product line]).
Set usage to Required and set the scope to Product as shown in the following screenshot:
Now run the report to test the functionality. You can enter
200401as the 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 levels of granularity and hence have different applications.
The detail filter works at the lowest level of granularity in a selected cluster of objects. In our example, this grain is the Sales entries stored in 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. This makes the query engine calculate [Quantity] at product level and then allows only those products 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 code 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 the 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.