When performing calculations in a cross-tab, sometimes Cognos Report Studio will not calculate correctly when an intersection in a cross-tab contains a value that is derived from multiple calculations.
A classic example of this is an aggregation of Gross Margin %, which is calculated as Gross Margin/Revenue. The aggregation of this calculation in a cross-tab simply adds each of the individual Gross Margin %’s for each row to get a total Gross Margin %. Clearly this is the incorrect result.
We want report studio to calculate the GM% after totalling the Revenue and GM columns. Defining the Solve Order on the Cross Tab Node Member allows us to calculate the GM% correctly. The Solve Order is defined via the properties pane after selecting the Cross Tab Node Member (see below)
By default, the Solve Order is set to NULL for both column and row items. In the case of NULL, or when both have the same value, the column items are calculated first, and row items second. This is the current behaviour of our report and the current settings are returning the wrong value for total GM%.
The data item with the highest solver order is calculated last. Therefore, in order to get GM% to calculate correctly, we need to edit the solve order for the ‘GM%’ Cross Tab Node Member (Column) to be higher than the ‘Total’ Cross Tab Node Member (Row). In this case, we change the ‘GM%’ solve order to 1, and leave the ‘Total’ as NULL.
This will provide the desired result when running the report