Aggregation and rollup aggregation
Business owners want to see the unit cost of every product. They also want the entries to be grouped by product line and see the highest unit cost for each product line. At the end of the report, they want to see the average unit cost for the whole range.
Getting ready
Create a simple list report with Products / Product line, Products/Product, and Sales fact / Unit cost as columns.
How to do it...
In this recipe, we want to examine how to aggregate the data and what is meant by rollup aggregation. Using the new report that you have created, this is how we are going to start this recipe:
We will start by examining the Unit cost column. Click on this column and check the Aggregate Function property.
Set this property to Average.
Add grouping for Product line and Product by selecting those columns and then clicking on the GROUP button from the toolbar.
Click on the Unit cost column and then click on the Summarize button from the toolbar. Select the Total option from the list.
Now, again click on the Summarize button and choose the Average option as shown in the following screenshot:
The previous step will create footers as shown in the following screenshot:
Now delete the line with the <Average (Unit cost)> measure from Product line. Similarly, delete the line with the <Unit cost> measure from Summary. The report should look like the following screenshot:
Click on the Unit cost column and change its Rollup Aggregate Function property to Maximum.
Run the report to test it.
How it works...
In this recipe, we have seen two properties of the data items related to aggregation of the values.
The aggregation property
We first examined the aggregation property of unit cost and ensured that it was set to average. Remember that the unit cost here comes from the sales table. The grain of this table is sales entries or orders. This means there will be many entries for each product and their unit cost will repeat.
We want to show only one entry for each product and the unit cost needs to be rolled up correctly. The aggregation property determines what value is shown for unit cost when calculated at product level. If it is set to Total, it will wrongly add up the unit costs for each sales entry. Hence, we are setting it to Average. It can be set to Minimum or Maximum depending on business requirements.
The rollup aggregation property
In order to show the maximum unit cost for product type, we create an aggregate type of footer in step 4 and set the Rollup Aggregation to Maximum in step 8.
Note
Here we could have directly selected Maximum from the Summarize drop-down toolbox. But that creates a new data item called Maximum (Unit Cost). Instead, we ask Cognos to aggregate the number in the footer and drive the type by rollup aggregation property. This will reduce one data item in query subject and native SQL.
Multiple aggregation
We also need to show the overall average at the bottom. For this we have to create a new data item. Hence, we select unit cost and create an Average type of aggregation in step 5. This calculates the Average (Unit Cost) and places it on the product line and in the overall footer.
We then deleted the aggregations that are not required in step 7.
There's more...
The rollup aggregation of any item is important only when you create the aggregation of Aggregate type. When it is set to automatic, Cognos will decide the function based on the data type, which is not preferred.
It is good practice to always set the aggregation and rollup aggregation to a meaningful function rather than leaving them as automatic.
