Crosstab Show all 12 months even if data doesn't exist

[cfrench]

I would like to format my crosstab report that has qty product sales per customers per month. The issue is if there not any sales or a month, then it is omitted. This makes perfect sense but I would still like to display the months 1, 2, 3, 4, etc. instead of 1, 3, 6. Does anybody know how I may do that?

Thank you much.

 

[drlex]

Which product are you using?

soi la, soi carré

 

[cfrench]

Sorry... Impromptu 7.3

 

[griffindm]

The only way to do this that I am aware of is to modify the data source to ensure 12 months are always seen. I do this by using a SQL view with a 12-rows table (months) to simulate zero-value data for the missing months. This ensures Impromptu holds a place for the missing months, and does not interfer with correct data when the months are present.

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
Magic with Data
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[cfrench]

Thanks, I was hoping there was a way in the application but this is a good idea.

Thanks again.

 

[rvasudev]

This is a typical scenario were you need an outer join. But an outer join might cause an performance hit.

Good Luck!
Ramesh

 

[griffindm]

Actually, you can do it without the outer join if you use a UNION view to combine the 12-rows table data with your true data.


The Decision Support Group
Reporting Consulting with Cognos BI Tools
Magic with Data
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com