How to modify the summary of a crosstab to produce the desired result?

[mickgibson]

Hi,
Can someone please help me with a problem we've encountered when trying to get percentage summaries to operate as desired on a crosstab report in Report Studio?
Currently, our report displays like this (using the default summarisation function);

Female Male Female Male
FACULTY A CAMPUS A 66 33 67% 33%
CAMPUS B 86 74 54% 46%
CAMPUS C 17 19 47% 53%
CAMPUS D 16 13 55% 45%
Summary 185 139 47% 33%

We would like it to display like this:
Female Male Female Male
FACULTY A CAMPUS A 66 33 67% 33%
CAMPUS B 86 74 54% 46%
CAMPUS C 17 19 47% 53%
CAMPUS D 16 13 55% 45%
Summary 185 139 57% 43%

The second table shows how we'd like the percentage summaries to work, ie. that they are a percentage of the Male / Female Summaries before it.

Note that changing the solve order has been tried, and doesn't work.

Note that I've raised this case with Cognos support, and they've indicated that it can't be done, and has been lodged as an ER - which surprised me greatly, so I'm hoping the user community is smarter then Cognos support and can help me with this.

Regards
Mick

 

[almeids]

Mick-
I'm as surprised as you are - seems you ought to be able to do what you're trying. Are you running Reportnet or Cognos 8? I'm not familiar with the term "solve order" (which certainly sounds like what you're looking for) so I'm thinking C8.
Worst case you can pull the summary records separately and union them with your campus details, though you'll need to add a dummy data item to both queries so the summaries sort last, and perhaps some conditional formatting to the crosstab to make them look like subtotals.
Steve

 

[L34621]

Hi Mick,

I am using Cognos 7.3, but in crosstabs, I've always had issues getting the summary percentages to come out correctly. Usually I get an averages in the summary crosstab.
These are the steps I took in 7.3 to get the right summary percentages:
1. total the whole numbers for Male and Female, (but not the percentage columns)
2. Run the query.
3. Click on Insert
4. Choose New Calculation.
5. Drop the New calculation in the summary area of your report.
6. Build the calculation for Male Summary Percentage: (Total Male / (Total Male + Total Female)
7. Right click, choose properties, Align and Align to the correct column.

I have no clue how much different Cognos 8 is from Cognos 7.3, but this method has worked perfectly in 7.3 for my summary percentages.

Good luck!

 

[mickgibson]

I've actually tried similar things in Report Studio within V8, but it doesn't seem to work. It's possible there are differences between the versions that don't allow it, or that I haven't been able to do it correctly.

But I think I'll try it again.

Does anyone have a V8 example?

Regards
Mick

 

[blom0344]

Did you try to add the summary as an aggregate of the type 'calculated'?
It looks like Cognos defaults to establishing the 'min' value if it cannot resolve the proper aggregate..

Ties Blom