Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

ReportNet CrossTab problem

Status
Not open for further replies.

JRO061601

MIS
May 30, 2002
92
US
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.
 
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...
 
Yes, I made sure our developer tried the obvious after you suggested it, and he did. Filter will not work.
 
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).
 
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!

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top