MDX Scope Statement

Recently someone asked me what is the use of SCOPE statement in SSAS Calcultions tab, I shared one example with that guy to understand the basics of Scope statement. I thought, I should share the same so if anyone wants to understand Scope statement then they can refer this post.

Scope statement is used to limit the scope of specified MDX to a specified subcube i.e. you can specifiy the behaviour for subcube.

Consider Date dimension and Calendar hierarchy from AdvantureWorks sample, Consider, you want to multiply your measure value if user select Month level members. We are taking [Measures].[Internet Tax Amount] and Calendar hierarchy in MDX.

Open SSMS and connect to Anlysis services and execute following MDX query.

SELECT
NON EMPTY [Measures].[Internet Tax Amount] ON COLUMNS,
NON EMPTY
DESCENDANTS([Date].[Calendar].[Calendar Year].&[2008],[Date].[Calendar].[Month],SELF)
ON ROWS
FROM [Adventure Works]

When you execute above query, you will get following results;

Values displayed in above snap are actual values coming from fact. Since Facts are at Date granularity, it is getting aggregated at higher level and displayed at Month level but consider client wants to show double the value if they see at Month level. So in such cases you can use Scope statement so SSAS engine can show expected values at Month level.

Put following Scope statement in Calculations Tab and save changes. We are passing Month level from Calendar hierarchy and [Internet Tax Amount] because we want to show double to same measure at Month level. This returns the current subcube.

Scope 
  ( 
     [Date].[Calendar].[Month],     
     [Measures].[Internet Tax Amount]
  );    

    This = [Measures].[Internet Tax Amount] * 2;    

  End Scope;

Once saved, execute same MDX query and you will find results are doubled up.