Regarding Crosstab layout

[dipstm]

Hi,

I have a crosstab report which has Years on column edge and Months on row edge and amount as measure.
The sample output of crosstab is as below:-

      2005 2007 2008  Total
Jan   100   50   75    225
July   10   50   50    200
Nov   50    10  100    160
Total 160  110  225   

Now I want to show all the  months starting from Jan to Dec  and for the months were there are no values will show a zero...like this

      2005   2007  2008
Jan   100     50    75
Feb    0       0     0
Mar    0       0     0
April  0       0     0
May    0       0     0
June   0       0    0
July  10       50   50
Aug    0       0    0
Sept   0       0    0
Oct    0       0    0
Nov   50      10    100
Dec    0       0    0


Plz let me know hw can we achieve this in Cognos?
Thanx in Advance.

[Suraj]

Union or replace measure by 0 when it is null.

[dipstm]

This will give me 0's for my measures but hw do I handle the Months.
I need to show all the months frm Jan to Dec irrespective of whether data is there or not.
Thanx.

[Suraj]

When measures are replaced with 0's, crosstabs usually display the corresponding rows automatically, unless the months are not coming from data base query.

[dipstm]

Hi,
yes thts the case my month field is coming from the database field.
So displaying months frm Jan to Dec wont be possible ?
Thanx

[Suraj]

You can do this:
create a separate query that just has months, nothing else. This way it'll have all 12 months as it is not filtered by anything.
Then join that with your current query and use the months from first query.