crosstab with Totals and Percent

[c84user]

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.

[c84user]

Anybody have any ideas on these problems?

[JackCognos]

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.

[c84user]

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.