Integration Software

How to remove the default grouping from an IBM Cognos crosstab

In case you want to export a crosstab to Excel and you do not want the grouped cells to be merged once exported to Excel, you can override the dimension info for the query and simulate a list-like output.

Step 1: Create your crosstab by adding the row-level attributes from your hierarchy along with the column attributes and measure(s).

How to remove the default grouping from an IBM Cognos crosstab remove dflt grouping cog crosstab rep stud design

This would provide the default grouping of the hierarchy attributes as we know it:

How to remove the default grouping from an IBM Cognos crosstab remove dflt grouping cog crosstab dflt run

Step 2: Set the “Override Dimension Info” property of the query to “Yes”:

How to remove the default grouping from an IBM Cognos crosstab remove dflt grouping cog crosstab override dim info

Step 3: Click the Dimension Info tab (bottom query pane) and reverse the hierarchy (start with the detailed level):

How to remove the default grouping from an IBM Cognos crosstab remove dflt grouping cog crosstab dim info tab

How to remove the default grouping from an IBM Cognos crosstab remove dflt grouping cog crosstab dim info design

The result is now a list-like output, without any grouping applied:

How to remove the default grouping from an IBM Cognos crosstab remove dflt grouping cog crosstab result

Notes:

In some cases (when you want additional row attributes from different hierarchies), you can create a Data Item in the query concatenating the different attributes and using this Data Item as the highest level in the Dimension Info tab, this way you control the required detailed info yourself.

Performance will go down when ungrouping hierarchy attributes (depending on the number of row-level attributes).

To fully simulate a list you can add crosstab spaces above each row attribute.

Leave a Reply

News

Blog

Twitter Feed

Contact us


2 − two =