MDAC 2.5 SDK - OLE DB Programmer's Reference
Chapter 24: Multidimensional Expressions

Assigning a Solve Order Example

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

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

The preceding statement produces the following dataset:

The cells shaded in gray could be either Annual Growth of Performance or Performance of Annual Growth. If it is Annual Growth of Performance, 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 is as follows. (The text in bold indicates the changes.)

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

And to get Performance of Annual Growth, the MDX statement is as follows. (The text in bold indicates the changes.)

WITH
MEMBER [Year].[Annual Growth] AS 
   ([1995] - [1994]), SOLVE_ORDER = 2
MEMBER [Status].[Performance] AS 
   (([Actual] - [Planned]) / [Planned] * 100), SOLVE_ORDER = 1
SELECT
{[1994], [1995], [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 orders. 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.