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:
OEM
MODEL
TESTYEAR TESTMONTH PASSCOUNT FAILCOUNT FAILRATE
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
TOTAL FAILCOUNT/(TOTAL PASSCOUNT+TOTAL FAILCOUNT)
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.
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”.