Saturday, October 4, 2014

How to export crosstab into user friendly Excel

Business users want to change from format 1 to format 2.  With format 2, User can simply process data further.  This document shows you an idea to make crosstab format 1 to format 2. Please see report specification (version 10.2) for detail. 
Based on the feedback from CognosPaul, there is a better option, described at the end of this post.
(Format 1)
(Format 2)

Step 1: Create normal cross tab as below, sorted by year
Step 2: Drag a cross space under revenue

Step 3: Drag region and product line to the right of first two columns
Step 4: Drag two cross space to title area
Step 5: unlock cell and make text item for titles and Highlight cells and make define contents as Yes
Step 6: Drag query item Year into cell
Step 7:  Add text to Region and Product line

Step 8: Select the first row, or original header and define box Type as None
Step 8: Remove style for region and product line

Better option:
Step 1: Add two cross space with fact cells to the left of the Year node Step 2: Define contents to yes on their fact cells Step 3: Drop the region and product lines into those intersections Step 4: Add text to the title with region and product line


  1. I tend to avoid this method as it makes the SQL/MDX significantly more complex. You might want to consider adding two spaces to the left of the Year node, defining contents to yes on their fact cells, and dropping the region and product lines into those intersections.

  2. Thanks for your comments; this is definitely a better option. I have adjusted the post to add this better option.