Cognos Crosstab query

[Swathi37]

Hi All,

I have a question on Cognos Impromptu.

I have the following raw data

Date Time Company User
20030420 1:00 AAA User1
20030420 1:15 BBB User10
20030420 1:45 AAA User9
20030420 1:55 AAA User30
20030420 13:14 ccc User19
20030420 15:00 DDD User20
20030420 16:15 AAA User2
20030420 16:25 AAA User3
20030420 21:25 EEE User7


Final Report

Time of Day Company User
0:00 0 0
1:00 2 4
2:00 0 0
3:00 0 0
4:00 0 0
5:00 0 0
6:00 0 0
7:00 0 0
8:00 0 0
9:00 0 0
10:00 0 0
11:00 0 0
12:00 0 0
13:00 1 1
14:00 0 0
15:00 1 1
16:00 1 2
17:00 0 0
18:00 0 0
19:00 0 0
20:00 0 0
21:00 1 1
22:00 0 0
23:00 0 0
0:00 0 0

How do I get the final output? I get the output for only the time with values. But I need to display 0 values for the above time interval.
Your help is greatly appreaciated.
Thanks
Swathi

 

[doodvat]

Swathi

One option is to have a table with all the time intervals and then use a Left outer join to join it to the raw data table shown above.
That should solve it, although you sometimes get problems when applying a filter on the crosstab.

 

[Swathi37]

It helps!!! Thanks a lot for your response.