ReportNet CrossTab problem

[JRO061601]

I'm trying to develop a report that has Months along the row headers and Categories along the column headers, with a single measure. I have an outer join in place so that a Month gets pulled onto the report, even if there are no records for that Month in any of the Categories. The report displays zeros across the board. This is what I want so that all 12 months are displayed. The problem is Cognos is creating a null Category column with a zero count all the way down. Is this a known issue with the crosstabber? Is there something I can do to get rid of the null Category column? Thanks.

 

[KingCrab]

filter? Category IS NOT NULL

 

[JRO061601]

Won't that take the rows I want (with the 'filler' Months) out of the result set before it gets crosstabbed? Would defeat the purpose...

 

[KingCrab]

Did you even try?

 

[JRO061601]

Yes, I made sure our developer tried the obvious after you suggested it, and he did. Filter will not work.

 

[almeids]

The problem is that your query must return every possible category, not simply every possible month. One solution is create a same-shaped query that does this, with a measure value of zero, and union it with your existing query using a tabular set. To do this you may have to allow cross join between your fact table (for categories) and your calendar table (for months).

 

[DataVixen]

Have you tried filtering the count (whatever you are measuring) to > 0?

Can you recreate and post a sample report with Go Sales data? It's easier to solve when we can see excatly what's going on. Good luck!