Formula Solve Order

When a dataset has multiple formulas, there can be a problem with the order of formula evaluation. To understand the problem, consider the MDX statement

WITH
   MEMBER [Year].[Annual Growth] AS ([1996] - [1997])
   MEMBER [Status].[Performance] AS (([Actual] - [Planned]) / 
      [Planned] * 100)
SELECT
   {[1994], [1995], [Annual Growth]} ON COLUMNS,
   {[Actual], [Planned], [Performance]} ON ROWS
FROM SalesBitmap

This produces a dataset that looks like this:

The cell shaded in gray could be either Annual Growth of Performance or Performance of Annual Growth. If it is Annual Growth of Performance, then the value in the cell is 75 – 50 = 25. If it is Performance of Annual Growth, the value in the cell is (80 – 20)/20 * 100 = 300.

This shows the clear need to establish an order for applying the formulas. To do this, you assign a “solve order” to each formula. When two formulas are in conflict, the one with the lower solve order gets resolved first.

Solve orders are assigned for calculated members by using the solve_order_specification clause. This clause is part of member_specification. For the example above, to get Annual Growth of Performance, the MDX statement looks like this (the text in bold indicates the changes):

WITH
MEMBER [Year].[Annual Growth] AS 
  ([1997] - [1996]), SOLVE_ORDER = 1
MEMBER [Status].[Performance] AS 
  (([Actual] - [Planned]) / [Planned] * 100), SOLVE_ORDER = 2
SELECT
{[1996], [1997], [Annual Growth]} ON COLUMNS,
{[Actual], [Planned], [Performance]} ON ROWS
FROM SalesBitmap

To get Performance of Annual Growth, the MDX statement looks like this (the text in bold indicates the changes):

WITH
MEMBER [Year].[Annual Growth] AS 
  ([1997] - [1996]), SOLVE_ORDER = 2
MEMBER [Status].[Performance] AS 
  (([Actual] - [Planned]) / [Planned] * 100), SOLVE_ORDER = 1
SELECT
{[1996], [1997], [Annual Growth]} ON COLUMNS,
{[Actual], [Planned], [Performance]} ON ROWS
FROM SalesBitmap

The following rules define the semantics of SOLVE_ORDER:

• When multiple expressions are in conflict, the statements with the lower solve orders get resolved first, followed by those with the higher solve order. What happens when conflicting expressions have the same solve order is provider-specific.
  
  
• Solve orders must always be greater than or equal to 0.
  
  
• The default solve order of a formula is 0.