Cross Tab Table - or some alternative way of doing it - with suppresse

[benshsms]

I have a database table with the following fields

Problem ID
Major Problem Type
Minor Problem Type

I need to output a report that shows something similar to...

Major Problem Type Minor Problem Type NumberofOccurences
-------------------------------------------------------------------------------
Application Softreset required 6
Server Software Issue 5
PDA Charging issue 4
Server Hardware issue 2
Application Hard reset required 1



I have tried using the cross tab expert but that wants to always group the table by major type (ie it would show the table as this....

Major Problem Type Minor Problem Type NumberofOccurences
-------------------------------------------------------------------------------
Server Software Issue 5
PDA Charging issue 4

Application Softreset required 6
Hard reset required 1

Server Hardware issue 2

I NEED it to do the job that the upper table does, displaying a major problem type for each minor type and sorting the whole table based on the number of occurences (regardless of the major type)

Thanks in advance!!

 

[Katy44]

Have you tried just doing a standard report (not cross tab) no grouping, and sort by number of occurences descending?

 

[benshsms]

Hi...not sure how I would do that, the number of occurences isn't a field in the database the table I'm querying just has a list of problem IDs which each have a major and a minor problem type. I'm sure there is an easy function I'm missing to count up the number of occurences of each type without using grouping????

 

[Katy44]

OK, sorry.
Given the minor problem type, can you derive the major problem?
If that's the case, you could put the minor prob into the details, group by minor prob, insert a count summary into the group header, and then suppress the details section. You would then just need to put the major prob in the group header too. Would this work?

 

[benshsms]

Thanks! Works superbly!

 

[lbass]

And if you want to order the groups in descending order by occurrences, then go to report->topN/group sort and leave the setting at "All", choose Count of {table.ID} (the field you are summarizing), descending.

-LB