Multiple Calculations in Crosstabs and Charts |
An intersection point in a crosstab or chart can contain a value that is derived from multiple calculations.
If the query expressions for the row and for the column that intersect in a crosstab both include calculations, the intersecting value is the result of performing both calculations. The second calculation is performed on the result of the first calculation. If you change the order in which the calculations are performed, the resulting value at the intersection point changes.
The solve order is a positive numeric value that you assign to data items. Negative values are not supported. The data item with the lowest value is calculated first, followed by the next higher value, and so on. The data item with the highest solve order is computed last. If you do not specify a solve order, the default value is 0. In crosstabs, if more than one data item has the same solve order, column items are calculated first and then row items.
For example, a crosstab contains the columns Actual revenue, Sales target, and a third column that calculates the percentage by which actual revenue exceeds target revenue. The crosstab contains a row for each Sales year and a summary row that calculates the overall totals for each of the three columns. The cell where the percentage calculation and the overall total calculation intersect contains only one value. By default, Report Studio calculates the column percentage first, and the summary row is calculated last. The value that appears at the intersection is therefore a sum of the percentages, which is not a meaningful result. The value is more meaningful if the overall percentage is calculated after the actual and sales target revenues are summed. Specifying a solve order for the percentage calculation that is higher than the overall total calculation gives the following result:
It is not necessary to specify the solve order for the overall total calculation. Because the default solve order is 0, setting the solve order for the percentage calculation to a value higher than 0 is sufficient.
Related Topics: