Blog Post

MDX ‘Is’ Operator

,

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 TypeItem CodeItem DescriptionShipped QuantityForecast QuantityABS Variance to Forecast
BagB001Backpack572
 L005Laptop Case1091
 M123Messenger Bag201010
 W187Wheel Bag594
 D137Duffle Bag (Wheeled)13152
 G173Grocery Bag10100
 C183Cargo Transport532
 T183Trunk451
Total  72684

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 TypeItem CodeItem DescriptionShipped QuantityForecast QuantityABS Variance to ForecastNew ABS Variance to Forecast
BagB001Backpack5722
 B001 Total 57222
 L005Laptop Case10911
 L005 Total 109122
 M123Messenger Bag20101010
 M123 Total 20101022
 W187Wheel Bag5944
 W187 Total 59422
 D137Duffle Bag (Wheeled)131522
 D137 Total 1315222
 G173Grocery Bag101000
 G173 Total 1010022
 C183Cargo Transport5322
 C183 Total 53222
 T183Trunk4511
 T183 Total 45122
Total  7268422

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

Rate

Share

Share

Rate