Evaluation Order

If you want to place two or more consolidations on a report, the order the engine evaluates them is significant and can change your 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 Dollar Sales metric and two consolidations. One consolidation is Seasons, as discussed in the previous examples. The other is called Years and is composed of three elements: 2022, 2023, and 2022/2023. The row for Spring 2022/2023 can be calculated either as (March 2022 + April 2022 + May 2022) / (March 2023 + April 2023+ May 2023) or as (March 2022 / March 2023) + (April 2022 / April 2023) + (May 2022 / May 2023). When the first calculation is used, so that the Seasons consolidation is evaluated first, the following report results.

When the second calculation is used, so that the Years consolidation is evaluated first, the following report results. Notice the difference in the 2022/2023 rows.

The evaluation order is set in the Report Data Options dialog box of the Report Editor when you create the report. To access this dialog box, choose Data > Report Data Options.