The Cognos Report Toolkit - Stitch multiple crosstabs together

Check list of all posts

General note about The Cognos Report Toolkit

Report specification:  Relational, and Cube

It is normal for business users to see a crosstab, where data is from difficult areas. The report is actually from multiple crosstabs and then stitched either at row or at column level. In this case, we can use the similar concept of stitched query to provide a very good performance. This solution below is generic for relation, and cube, where data can be from different domains. It is very powerful, as you can stich any data together. In addition, it will results a very good performance, as data only needs to stitch at summary level. The only drawback is that you need to make sure all individual queries have the same number of query items, with the same sequence and data type.

Please see screenshot below,

All data stitched at product dimension, each fact has their own measure. In this sample, revenue is further broken down to year. Since you need to use union, we have to keep number of columns and type of columns consistent.  

In case when the dimension or measure is NOT applied, we use cast null to make empty data, or cast(null,integer). When present at layout, you need to simple suppress column.  This is solution for relational related data.  For the cube data source, we use rootMember ([great_outdoors_sales_en].[Years].[Years]) for not neded dimension, and we still can use cast(null,integer).for not available measure.

Query

In order to union three queries, all three query need to have same items with the same type. Use cast(null as integer) for not applied data items

Product line: [Sales (query)].[Products].[Product line]

Year: [Sales (query)].[Time].[Year]

Revenue: [Sales (query)].[Sales].[Revenue]

Sales target:cast(null as integer)

Return quantity:cast(null as integer)

Product line: [Sales target (query)].[Products].[Product line]

Year: cast(null,integer)

Revenue: cast(null,integer)

Sales target: [Sales target (query)].[Sales target].[Sales target]

Return quantity:cast(null as integer)

Product line: [Returned items (query)].[Products].[Product line]

Year: cast(null,integer)

Revenue: cast(null,integer)

Sales target: cast(null,integer)

Return quantity: [Returned items (query)].[Returned items].[Return quantity]

The combined data looks like follows

Layout

Suppress columns is needed, as there will be an empty column for target and return with empty year.

Cube based source

All queries and layout for cube based source have the same structure. The only different is query item definition. Detail is listed below

Revenue query

Product line: [great_outdoors_sales_en].[Products].[Products].[Product line]

Year:  [great_outdoors_sales_en].[Years].[Years].[Year]

Revenue:  [great_outdoors_sales_en].[Measures].[Revenue]

Sales target:cast(null as integer)

Return quantity:cast(null as integer)

Sales target query

Product line: [great_outdoors_sales_en].[Products].[Products].[Product line]

Year: rootMember ([great_outdoors_sales_en].[Years].[Years])

Revenue: cast(null,integer)

Sales target: [great_outdoors_sales_en].[Measures].[Sales target]

Return quantity:cast(null as integer)

Return quantity

Product line: [great_outdoors_sales_en].[Products].[Products].[Product line]

Year: rootMember ([great_outdoors_sales_en].[Years].[Years])

Revenue: cast(null,integer)

Sales target: cast(null,integer)

Return quantity:  [great_outdoors_sales_en].[Measures].[Unit cost] (no return quantity available)