Troubleshooting
Problem
This document will outline how to adjust a default Report Studio crosstab to perform the selected aggregation on the crosstab column first and then force the row aggregation to be applied to the column aggregation.
Resolving The Problem
Sample Crosstab
|
Revenue | 2004 |
2005 | 2006 | Average(Order year) |
| Camping Equipment |
$20,471,328.88) | $31,373,606.46) |
$37,869,055.58) | $29,904,663.64) |
| Golf Equipment |
$5,597,980.86) | $9,598,268.88) |
$10,709,215.84) | $8,635,155.19) |
| Mountaineering Equipment |
$9,642,674.54) | $11,248,676.06) |
$10,445,675.30) | |
| Outdoor Protection | $1,536,456.24) |
$988,230.64) | $646,428.04) |
$1,057,038.31) |
| Personal Accessories | $7,144,797.52) |
$10,955,708.04) | $13,793,960.30) |
$10,631,488.62) |
| Total(Product line) | $34,750,563.50) |
$62,558,488.56) | $74,267,335.82) |
$171,576,387.88 |
In a regular crosstab report any calculations applied to the rows (Total in the above example) will be done before those applied to columns (Average in the above example). In the above example, we want to see the Total Revenue for all Product Lines by Order Year. We also want to see the Average Revenue across the 3 order years for which there are records. As you can see, eveything is correct except the corner of the crosstab (bottom right-hand cell). By default, it displays the Total Revenue for all years (171,576,387.88) when it should be displaying the average revenue for all products across all years.
To achieve this we change the Solve Order property of the column. By default, both the row aggregation and the column aggregation have the same solve order. We will make the column solve order higher than the row solve order so that the column aggregation will be done first. Using the above crosstab as an example:
Open the report specificiation in Report Studio
Click on the <#Average(Order Year)#> column header.
Locate the Sort Order property in the properties pane
Change it to be higher than the number applied to the sort order property of the <#Total(Product Line)#> row header. By default they each have a solve order property of 1 so change the solve order property of the column to 2.
If you run the report again you will now see the result as:
| Revenue |
2004 | 2005 |
2006 | Average(Order year) |
| Camping Equipment |
$20,471,328.88) |
$31,373,606.46) | $37,869,055.58) |
$29,904,663.64) |
| Golf Equipment | $5,597,980.86) |
$9,598,268.88) | $10,709,215.84) |
$8,635,155.19) |
| Mountaineering Equipment | $9,642,674.54) |
$11,248,676.06) | $10,445,675.30) |
|
| Outdoor Protection |
$1,536,456.24) | $988,230.64) |
$646,428.04) | $1,057,038.31) |
| Personal Accessories |
$7,144,797.52) | $10,955,708.04) |
$13,793,960.30) | $10,631,488.62) |
| Total(Product line) |
$34,750,563.50) | $62,558,488.56) |
$74,267,335.82) |
$12,255,456.28 |
The result is still incorrect (12,225,456.28). That result is actually all of the revenue amounts added together (the details) and then divided by 14 (the number of revenue cells). The total is now providing an average but it is calculating the average of the details and not of the average of the Total(Product line) columns.
In order to force this to occur, we have to change the expression definition for the <#Average(Order Year)#> column header.
By default it will read:
average(currentMeasure within detail [Order year])
Change it to:
average(currentMeasure within set [Order year])
(Note the change from detail to set in the expression) With this change, the average column provided an average of all of the totals at the summary level (for the set) instead of for the details. The end result looks like this:
|
Revenue | 2004 |
2005 | 2006 | Average(Order year) |
| Camping Equipment | $20,471,328.88) |
$31,373,606.46) | $37,869,055.58) |
$29,904,663.64) |
| Golf Equipment | $5,597,980.86) |
$9,598,268.88) | $10,709,215.84) |
$8,635,155.19) |
| Mountaineering Equipment | $9,642,674.54) |
$11,248,676.06) | $10,445,675.30) |
|
|
Outdoor Protection |
$1,536,456.24) | $988,230.64) |
$646,428.04) | $1,057,038.31) |
|
Personal Accessories |
$7,144,797.52) | $10,955,708.04) |
$13,793,960.30) | $10,631,488.62) |
| Total(Product line) |
$34,750,563.50) | $62,558,488.56) |
$74,267,335.82) | $57,192,129.29) |
See the before and after sample report specifications attached to this document.
- 1019192_1.zipWas this topic helpful?
Document Information
Modified date:
15 June 2018
UID
swg21339552