I have a crosstab report like this
Type 1 Type 2
Agency Name Reason1a Reason2a Reason2a Reason2b Total Pct
Agency1 10 12 21 22 65 69.89
Agency2 5 6 8 9 28 30.11
TOTAL 15 18 29 31 93
The expression for dataitem Total = total(currentMeasure within detail [Cancel Reinstatement Reason Type])
The expression for dataitem TOTAL = total(currentMeasure within detail [Financial Master Agent Name])
I have 2 challenges that i've been struggling with for quite a while.
#1 sorting on Total desc
so even tho' i have the pre-sort parameter set to sort descending on the data item in the query, the sorting is totally accurate.
#2 adding the percent column to the crosstab
seemingly this would be Total / TOTAL * 100. However, none of the solutions I have tried work either error free or with correct percentage being calculated.
While both of these seem like they should be very straight forward, I haven't uncovered the secret. Any help will be appreciated. Thanks.
Anybody have any ideas on these problems?
1. You can add the field to the report, sort on it, then using box=none to hide it.
2. Rather than referencing the query fields, it sometimes helps to recalculate them in the calculated field. Try this calculation instead: total((currentMeasure within detail [Cancel Reinstatement Reason Type])/
total(currentMeasure within detail [Financial Master Agent Name]))*100
If there is any aggregation going on, make sure the field is set to Calculate.
Thanks for the reply.
#1 We actually need the total column on the report so hiding it is not an issue. However, I have tried sorting on the column in the report as well as sorting the data item in the query and a combination of both but the sorting isn't as expected. It appears that the detail measure of Cancel Reinstatement Reason Type is affecting the sorting results somehow
total(currentMeasure within detail [Cancel Reinstatement Reason Type])
#2 Also on the Pct column, I tried the solution specified with many variations of 'calculated' parameter and always received the following error.
Unable to build the aggregate dataItem="Pct of Policies". The referenced dataItem="Financial Master Agent Name" was not found at the same nesting level.
Any other ideas? So far, after days of trial and error, scouring message boards and user guides, still no progress.