Crosstab Column Headers

Previously I described a technique to add column headers to crosstabs and make them look like lists.

I found that this method sometimes leads to empty cells on the right hand side of the crosstab - I believe due to a bug in Cognos.

Here I describe another way to do it that works fine.

• Add an empty first row to the crosstab by bringing in Crosstab Spaces.
  
• Set the text of these... add one for each column on the left hand side of the crosstab.
  
• Set "Define Contents = Yes" for all the facts cells in this first row
  
• Unlock the editing (lock icon)
• Drag a Text Item into each member cell and set the text to be the column heading of each column in the top (horizontal) side of the crosstab
  
• Hide the topmost row of the corsstab by setting Box Type = None

a crosstab that looks like this:

                                        | measure1 | measure2
----------------------------------------+----------+---------
dimension member 1 | dimension member 2 | <1234>   | <1234>
                   +--------------------+----------+---------
                   | dimension member 2 | <1234>   | <1234>
                   +--------------------+----------+---------

will look like

                                           | measure1 | measure2
                                           |          |
-------------------------------------------+----------+---------
       crosstab space | crosstab space     | textitem | textitem
             member1" |      "member2"     |"measure1"|"measure2"
----------------------+--------------------+----------+---------
   dimension member 1 | dimension member 2 | <1234>   | <1234>
                      +--------------------+----------+---------
                      | dimension member 2 | <1234>   | <1234>
                      +--------------------+----------+---------

Crosstabs with Column Headers

aka How to make Crosstabs that look like lists

                     | sales
------+--------------+---------
branch| sales person | <1234>
      +--------------+---------
      | sales person | <1234>

will look like

                     | sales 
-----------+---------+--------
1st Avenue | Aaron A | 1,000
           +---------+--------
           | Betty B | 2,000
-----------+---------+--------
2nd Street | Clare C | 1,500 
...

If it was a list it would have column headers and would look like this:

branch     | sales person | sales 
-----------+--------------+--------
1st Avenue | Aaron A      | 1,000
           +--------------+--------
           | Betty B      | 2,000
-----------+--------------+--------
2nd Street | Clare C      | 1,500 
...

To achieve this using a crosstab you need to add extra Text Items and hide some cells by setting their box type to ``none''

    HIDE             | "branch" | "sales person" | sales
---------------------+----------+----------------+------
branch| sales person |  HIDE    |   HIDE         | <1234>
      +--------------+----------+----------------+-------
      | sales person |  HIDE    |   HIDE         | <1234>

Hiding the top left corner will pull the extra Text Items to the left so that the "headers" will nicely align with their columns.
But adding these new text item still creates extra empty columns in the crosstab... these have to be hidden as well.

The resulting crosstab will look just like a list, displaying column headers.

You may need to play around with styling it to get everything in the right color....

List vs Crosstab

It's easy. But I haven't found it mentioned clearly in Cognos documentation or training:
Use List for relational model, Crosstab for dimensional model.

This means that if you are using Cognos in a real data warehouse scenario you can most certainly forget about lists.

The good news:

• Everything you can do with list can be done with crosstab (so you won't miss it)
  including having only one dimension
  
• if you can get it working in crosstab then you can plot it on a graph
  graphs work exactly like crosstabs