Intersecting 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 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 of 0 is used. In crosstabs, if more than one data item has the same solve order, column items are calculated first and row items are calculated second.

For example, a crosstab contains the columns Revenue, Sales target, and a column that calculates the percentage by which actual revenue exceeds target revenue. The crosstab contains a row for each 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, IBM® Cognos® Business Intelligence calculates the column percentage first and the summary row 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:

Figure 1. Crosstab showing a calculation that has a solve order specified
a crosstab showing each year's actual revenue, sales target, and percentage by which the actual exceeds the target

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.

Nested Calculations in Crosstabs

If a crosstab contains nested calculations, the calculations are performed in the following order by default:

  1. outermost calculation on the columns
  2. innermost calculation on the columns
  3. outermost calculation on the rows
  4. innermost calculation on the rows

If there are solve order ties, column items are calculated before row items and outer nesting items are calculated before inner nesting items.

Parent topic: Considerations when Creating Calculations