http://www.sqlservercentral.com/blogs/salvoz-sql/2012/07/04/mdx-is-operator/

Printed 2014/12/19 03:54AM

MDX ‘Is’ Operator

By Jennifer Salvo, 2012/07/04

I recently encountered a business scenario involving calculated members in a cube that I thought is worth sharing.

We have a cube with the following measures:  Shipped Quantity, Forecast Quantity, and ABS Variance to Forecast.

ABS Variance to Forecast is a calculated member in the cube, the MDX is pretty simple:

IIF ([Measures].[Forecast Quantity]<>0 OR [Measures].[Shipped Quantity]<>0
        ,ABS([Measures].[Forecast Quantity]-[Measures].[Shipped Quantity])
        ,Null)

As you can see in the example below, when the ABS Variance to Forecast is calculated at the part type level, it is using the aggregated values of the Shipped Quantity and Forecast Quantity and subtracting them.

Part Type Item Code Item Description Shipped Quantity Forecast Quantity ABS Variance to Forecast
Bag B001 Backpack 5 7 2
  L005 Laptop Case 10 9 1
  M123 Messenger Bag 20 10 10
  W187 Wheel Bag 5 9 4
  D137 Duffle Bag (Wheeled) 13 15 2
  G173 Grocery Bag 10 10 0
  C183 Cargo Transport 5 3 2
  T183 Trunk 4 5 1
Total     72 68 4

This was not the behavior the user was looking for, instead he wanted the ABS Variance to Forecast at the part type level to be the Sum of the ABS Variance to Forecast at the Item level.  In the example above, the result would be 22.

At first, it appeared that creating a new calculated member with the following MDX would do the trick.

SUM([Item].[Item Code].[Item Code].MEMBERS,[Measures].[ABS Variance to Forecast])

This did yield the correct values calculated at the part type level, but caused issues for the totals at the item code level.

Part Type Item Code Item Description Shipped Quantity Forecast Quantity ABS Variance to Forecast New ABS Variance to Forecast
Bag B001 Backpack 5 7 2 2
  B001 Total   5 7 2 22
  L005 Laptop Case 10 9 1 1
  L005 Total   10 9 1 22
  M123 Messenger Bag 20 10 10 10
  M123 Total   20 10 10 22
  W187 Wheel Bag 5 9 4 4
  W187 Total   5 9 4 22
  D137 Duffle Bag (Wheeled) 13 15 2 2
  D137 Total   13 15 2 22
  G173 Grocery Bag 10 10 0 0
  G173 Total   10 10 0 22
  C183 Cargo Transport 5 3 2 2
  C183 Total   5 3 2 22
  T183 Trunk 4 5 1 1
  T183 Total   4 5 1 22
Total     72 68 4 22

The totals at the item level were the same as the total at the part type level, definitely not correct.

To resolve this issue, my co-worked introduced me to a new operator in MDX: ‘Is’.  The ‘Is’ operator is used to check whether two objects are equivalent. In this case, we needed to check if the CurrentMember was equal to the DefaultMember (‘All’).  If the CurrentMember is equal to the DefaultMember, then we are at the ‘All’ level of the hierarchy and need to use the SUM function, otherwise we use the value of the ABS Variance to Forecast measure.

IIF([Item].[Item Code].CurrentMember Is [Item].[Item Code].DefaultMember ,SUM([Item].[Item Code].[Item Code].MEMBERS,[Measures].[ABS Variance to Forecast]),[Measures].[ABS Variance to Forecast])

This produced the correct results and I learnt something new about MDX Smile


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.