Home > MDX > MDX – Item() for dynamic calculations

MDX – Item() for dynamic calculations

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

Advertisements
Categories: MDX Tags:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: