How to use Cognos report studio to change default Total() calculation


I have a crosstab report that calculates for failure rates for my products ; it has two measures (PASSCOUNT, FAILCOUNT) and a calculation FAILRATE (FAILCOUNT/PASSCOUNT+FAILCOUNT). The report layout is as follows:

When I select the Total icon, it logically adds up each of the columns like so

[code]2012 OCT 7547 697 0.08
NOV 9570 373 0.04
DEC 1879 107 0.05

Total 18996 1177 0.17[/code]

My user however wants TOTAL FAILRATE to be

which translates to
1177 / (18996+1177) = 0.058

How can I create this custom total in the report? I am reading about creating a Query calculation but I am not clear this is the right approach.

Cognos Report Studio 8.4 IBM DB2 UDB

Hi Alan,

I’ve had similar issues with reports I’ve created. When you add the totals to a report, it adds new fields to the report’s underlying query. E.g. your query likely has fields named something like PASSCOUNT, FAILCOUNT, and FAILRATE, but adding the totals would created three more fields in the query, whose names default to TOTAL(PASSCOUNT), TOTAL(FAILCOUNT), and TOTAL(FAILRATE). If you look at the expression definition for these, you’d likely see them listed as something like TOTAL(FAILRATE). Using that, it would do exactly as you describe - add up the fail rates from the chart, giving you (in your example) a value of 0.17. What you’d want to do is change the expression definition for TOTAL(FAILRATE) to be:

You may also have to play with the field’s “total” settings, i.e. the setting where you can choose min, max, count, count distinct, etc. I’ve found that I sometimes have to set that setting to “calculated”.

Hope this helps!