My cross tab shows distinct count of case id numbers for each of a number of categories.  The problem is, that the column totals don't equal the sum of the records within the cross tab.  I know why this is, its because case ids can appear under more than one category - therefore they are counted twice ( or more) within the body of the crosstab, but only once in the summary - like this:

 

                              ofiice1     office2   office3

category a                  5           4          2

category b                  6            5         4

 

How can I get my totals to display the actual total of the cell values?  IE 11, 9 & 6 in the example above.

 

Thanks

 

 

The only way round it for a crosstab that I can think of is to filter the data before pulling it into the report to get only one record per ID per category then you can just use the COUNT. This would mimic the distinct count at the category level but a total sum of all records at the top level to incluse your "duplicates" at each category level.

If you are using SQL you could create a view using the category, office and id# and group on them all.