Crosstab Percent Summaries

[tdhoffer]

I have a crosstab report that I created in Report Studio that looks like the example below. F and M are text so the results for the number of females and male are the results of crosstab counts of the gender field.

F M %F %M
CAMPUS A 66 33 ?? ??
CAMPUS B 86 74 ?? ??
CAMPUS C 17 19 ?? ??
CAMPUS D 16 13 ?? ??
Summary 185 139 ?? ??

I would like to know how to calculate the %F and %M students but I have not been able to figure it out. I realize is it is F/(F+M), for example. But what I cannot figure out is how to set up a Cognos calculation that refers to the crosstab values of F and M. Can anyone help?

Terry H
RiverStone Health
Billings, MT

 

[blom0344]

Create new dataitem:

total(CASE WHEN [GENDER] = 'F'
THEN [SOMEMEASURE] ELSE 0 END)/
(total([SOMEMEASURE]))

Set aggregate of the dataitem to calculated and add it to the crosstab

Do the same for the 'M' Gender..

Ties Blom

 

[tdhoffer]

Thanks for the quick reply blom. The use of the case statement makes sense now that I see it. But I have one question. What would be used for SomeMeasure?

 

[blom0344]

Each crosstab uses at least one aggregate. No idea how it is called in your case, but it must be a dataitem storing the number of .... or count of ....

Ties Blom

 

[tdhoffer]

Thanks. Got it!