Archive

Posts Tagged ‘Item()’

MDX – Item() for dynamic calculations

February 11, 2012 Leave a comment

It is very common scenarios to have dynamic calculations while developing reports using MDX i.e. SSAS Cube as source.
Here I came across a scenario as
Need to calculate the variance and percentage among all the years i.e. multi years with respect to first year of the measures
It is fairly simple to implement in MDX than SSRS expression, here is the MDX query on AdventureWorks
Logic: Here we need to consider the first year member as the source for all the calculations so
1. All year values as one set and measure as one set
2. Apply the logical AND to get a result set for all the years along with measures
3. Use the Item(0) to get the first year measure
4. Use the first year measure calculated member for all the calculations

WITH MEMBER [measures].[First Year Internet Sales Amount] 
AS    
     {{[Date].[Calendar Year].&[2006],[Date].[Calendar Year].&[2007],[Date].[Calendar Year].&[2008]}     
       *{ [Measures].[Internet Sales Amount]}
      }.Item (0) // Item(0) is used to get the first year measure
   , FORMAT_STRING = "Currency"
   , SOLVE_ORDER = 1
MEMBER [measures].[Variance]  AS
(
[Measures].[Internet Sales Amount] - [measures].[First Year Internet Sales Amount]
)
, SOLVE_ORDER = 2
MEMBER [Measures].[PercentageVariance] AS
(
CASE WHEN [measures].[First Year Internet Sales Amount] IS NULL 
OR ISEMPTY([measures].[First Year Internet Sales Amount]) THEN 1
     WHEN [measures].[Internet Sales Amount] IS NULL THEN -1
ELSE [measures].[Variance] 
 /
[measures].[First Year Internet Sales Amount] 
END )
,FORMAT_STRING = "Percent"
, SOLVE_ORDER = 3  
SELECT 
   {  [measures].[First Year Internet Sales Amount],[Measures].[Internet Sales Amount] 
     , [measures].[Variance],[Measures].[PercentageVariance]
    }
  ON 0
, [Date].[Calendar Year].children ON ROWS
FROM 
(select 
{[Date].[Calendar Year].&[2006],[Date].[Calendar Year].&[2007],[Date].[Calendar Year].&[2008]} on 0 
from 
[Adventure Works]
)

Points to consider:
1. Here we used all the year values so we need to use the STRTOSET function to pass the dynamic year values.
2. I have come across one of the msdn forum question with my answer could get from here
3.I have discussed one more dynamic calculations using previous year measures query in my previous post could get from here

Categories: MDX Tags: