Cognos 8 Crosstab Returning Wrong Totals

[SCRRT]

I have built a query listing attendance, seats available by educational institute. I would like to display the results in a crosstab. When I use the same query in a list report, I get a different result than if I summarize in a crosstab. I have noticed that any records that have the same values only get counted in the totals once. These are separate classes, just the same size and attendance. If I add the unique identifer for each class to the xtab all classes are added. How do I get Cognos 8 to add all classes without having the unique identifier on the report.

 

[almeids]

If you are by any chance using a UNION in your query, you need to change it to a UNION ALL. UNION eliminates any duplicate rows.

 

[almeids]

Sorry, as I clicked "Submit" I realized you said your query worked in a list...disregard my UNION ALL suggestion.

 

[SCRRT]

There is no union but there is a join between two subqueries. Just to clarify, the list works if the unique identifier is included. If I were to remove it from the list report it would also only display the unique rows. It is behaving something like Impromptu with its Eliminate Duplicate Rows box checked. Here is a sample of the data
List
Classcode Institute SpaceAvailable Enrollment
100 A 5 4
101 A 5 4

Crosstab SpaceAvailable Enrollment
Institute A 5 4

 

[blom0344]

If the query involves no facts (measures with aggregation set to something other than undetermined/none) then Cognos will probably add a distinct clause to the SQL resulting in only unique rows. Including an unique identifier is a good remedy, you can fetch it and not use it in the report (or hide it)

Ties Blom