How to assemble crosstab based on good prepared data

Check list of all posts

Context

Sometimes, we can pre-aggregate data in data warehouse. There are manly two reasons of doing pre-aggregation: 1) the logic is complicated, and 2) report performance is very critical.  However, when bring such data into report; we can experience the problem, as we cannot use the default aggregation in Cognos. This document shares an idea to resolve this problem.

Data in data warehouse

Data is pre-calculated in database for current month, Previous month, YTD current Month and TYD previous month, which is reflected in framework manager as below

Product	CM-1 % budget	CM % budget	CM-1 Sales	CM Sales	CM-1 budget	CM budget	YTD CM-1 % budget	YTD CM % budget	YTD CM-1 Sales	YTD CM Sales	YTD CM-1 budget	YTD CM budget
Product A	91%	90%	91	90	101	100	101%	100%	191	190	201	200

Report

However, the requested report is supposed to display as below.

	CM-1	CM	YTD CM-1	YTD CM
Product A	%budget	91%	90%	101%	100%
	Sales	91	90	191	190
	budget	101	100	201	200

Solution

There is no way that we can use the traditional crosstab aggregate to get report from framework manger. The solution is to list these data multiple times with cross space, and then hide all data that not needed to display.

Product	CM-1CM-1 % budget	CM CM % budget	CM-1 Sales	CM Sales	CM-1 budget	CM budget	YTD CM-1YTD CM-1 % budget	YTD CM YTD CM % budget	YTD CM-1 Sales	YTD CM Sales	YTD CM-1 budget	YTD CM budget
Product A	%budget	91%	90%	91	90	101	100	101%	100%	191	190	201	200
	Sales	91%	90%	91	90	101	100	101%	100%	191	190	201	200
	budget	91%	90%	91	90	101	100	101%	100%	191	190	201	200

Sample

A screenshot from a practical report is listed below