About the evaluation order of consolidations

If a report contains two or more consolidations, the order that the engine evaluates them is significant and can change the result set. If one of the consolidations involves multiplication or division and the other involves addition or subtraction, which consolidation is calculated first matters. When performing a mathematical calculation, the product of a sum is not always equal to the sum of the product.

For example, a report contains the Revenue metric and two consolidations. The Seasons consolidation has four elements, Winter, Spring, and so on. Each element uses addition. (For a more detailed description of this consolidation example, see Consolidations: Virtual attributes.) The other consolidation, Years, is composed of three elements: 2022, 2023, and 2022/2023. The row for Spring 2022/2023 can be calculated in either of the following ways:

• (March 2022 + April 2022 + May 2022) / (March 2023 + April 2023+ May 2023)

• (March 2022 / March 2023) + (April 2022 / April 2023) + (May 2022 / May 2023).

When the first calculation is used, the Seasons consolidation is evaluated first. When the second calculation is used, the Years consolidation is evaluated first, and a different result is calculated. For report samples, see the Evaluation order section of the Custom Groups and Consolidations chapter of the Advanced Reporting Help.

The evaluation order is set in the Evaluation Order subcategory of the Report Data Options dialog box of the Report Editor when you create the report.