Thursday, February 2, 2017

Get Period N data in MDX...

In some scenarios, we may need to get Period N data including the current period.
As an example if we select the Year as 2017, we may need to get the values for 2017, 2016, 2015, .., (2017-(n-1)).

I was able to achieve it using the below MDX Query

WITH
SET [Set_PeriodRange] AS
{STRTOMEMBER(@Param_Period+".lag(N-1)" : STRTOMEMBER(@Param_Period)}

SELECT NON EMPTY{[Measures].[Sales]} ON COLUMNS,
NON EMPTY([Set_PeriodRange]) ON ROWS 
FROM [SalesCube]

Here what I did was I created a set for the what ever the required period range, using the lag function.
Then use that Set for the Row axis in the MDX.
@Param_Period is the parameter with the Year value.

The use of the created Set is that if you pass the parameter value based on a hierarchy, it will return the members for the current level of the hierarchy.
If the current level is Quarter, it will return the N number of Quarters.

Hope this will be helpful...


No comments:

Post a Comment