Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

MDX for Report Time Periods

Posted by thomasivarssonmalmo on November 11, 2010

Time period aggregates combined with measure types like actual, budget and forecast are normal day to day reporting. This MDX snippet is one way how you can build these aggregates that are not symmetric. End users would like to see actual values for a single month together with the accumulated values for the pervious months.

I do not handle this in a single step but in three like in this way:

  • Build the set for the time periods, like months
  • Build the set that combines the first set with the measure type
  • Build a calculated member that use the Aggregate()-function, the second set and the appropriate measure

I use the Adventure Works demo cube and the scenario dimension in that cube that consist of Actual, Budget and Forecast. There is only one measure that can be used for that dimension, amount in the Financial Reporting measure group.

Here is the first example that constructs two calculated members on the scenario dimension.

WITH
Set MonthJanMarch2002 As [Date].[Calendar].[Month].&[2002]&[1]:[Date].[Calendar].[Month].&[2002]&[3]
Set ActMonthJanMarch2002 As  CrossJoin(MonthJanMarch2002,{[Scenario].[Scenario].&[1]})
Set BudMonthJanMarch2002 As  CrossJoin(MonthJanMarch2002,{[Scenario].[Scenario].&[2]})
Member Scenario.Scenario.ActAmountJanMarch2002 As Aggregate(ActMonthJanMarch2002,[Measures].Currentmember)
Member Scenario.Scenario.BudAmountJanMarch2002 As Aggregate(BudMonthJanMarch2002,[Measures].CurrentMember)

Select {Scenario.Scenario.ActAmountJanMarch2002, Scenario.Scenario.BudAmountJanMarch2002} On 0,
[Measures].[Amount] on 1
From [Adventure Works];

If I have more than one measure you can add them on rows in the select part and the calculated members will work on all measures being put there. The result look like this:

First Query

In the second example I have a little more complex scenario. Lets add two scenarios in one calculated measure, actuals for January 2002 to February 2002 and budget for March 2002. This is a common scenario when you are working with forecasts.

WITH
Set MonthJanMarch2002 As [Date].[Calendar].[Month].&[2002]&[1]:[Date].[Calendar].[Month].&[2002]&[3]
Set MonthJanFeb2002 As [Date].[Calendar].[Month].&[2002]&[1]:[Date].[Calendar].[Month].&[2002]&[2]
Set ActMonthJanMarch2002 As  CrossJoin(MonthJanMarch2002,{[Scenario].[Scenario].&[1]})
Set ActMonthJanFeb2002 As  CrossJoin(MonthJanFeb2002,{[Scenario].[Scenario].&[1]})
Set BudMonthJanMarch2002 As  CrossJoin(MonthJanMarch2002,{[Scenario].[Scenario].&[2]})
Member Scenario.Scenario.ActAmountJanMarch2002 As Aggregate(ActMonthJanMarch2002,[Measures].Currentmember)
Member Scenario.Scenario.BudAmountJanMarch2002 As Aggregate(BudMonthJanMarch2002,[Measures].CurrentMember)

Member Scenario.Scenario.ActAmountJanFebBudMarch2002
As Aggregate({(MonthJanFeb2002,[Scenario].[Scenario].&[1]),([Date].[Calendar].[Month].&[2002]&[3],[Scenario].[Scenario].&[2])})

Select {Scenario.Scenario.ActAmountJanMarch2002, Scenario.Scenario.BudAmountJanMarch2002, Scenario.Scenario.ActAmountJanFebBudMarch2002} On 0,
[Measures].[Amount] on 1
From [Adventure Works];

The result looks like this:

Second Query

The definition of the calculate member and the aggregate() function in this last example came with a little help from the SSAS team at Microsoft.  These calculated members and named sets can be built in the MDX editor for Reporting Services in the 2008 version and later.

Advertisements

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: