MDX Scope with an If Then statement

The scope statement in MDX is great for applying calculations to a certain area of a cube, also called a subcube. Dustin Ryan wrote a great article on the scope statement here. The issue I am going to cover will be using the scope statement when you only want to cover all but one member of a dimension.

The normal scope statement would be:

Scope([Referral Type].[Referral Type Desc].members,[Measures].[ER to IP Admits]);

This = ([Measures].[ER to IP Admits] * 0);

End Scope

And this works great and causes the measure to become zero for all members in this dimension. The problem is there is one member in the dimension where I do not want it to be zeroed out.

Here is the member:

[Referral Type].[Referral Type Desc].&[ER TO IP]

This member needs to keep the measure at its original value. To accomplish this I place an “If Then” statement around the “This” part of the scope statement. If you are doing this in Analysis Services in the calculation tab, you will click the new script command button and enter the scope statement from above.

image

Below you can see the calculation screen before you save and close the cube.

image

After you create the Scope statement you can save and close the cube and the script will be broken into three different sections in the script organizer as seen below.

image

Then click on the second section of the scope statement (the one that contain “This =”). Then add the “if then” statement around the “This=” section. Makes sure you add the “End If” at the end. Below you can see the middle section of the MDX scope statement. I do not want this to be applied to the member “ER TO IP”. So I placed a “Not” in front of the Boolean expression.

If not([Referral Type].[Referral Type Desc].currentmember is [Referral Type].[Referral Type Desc].&[ER TO IP]) Then

This = ([Measures].[ER to IP Admits] * 0)

End if

Notice how I used the “is” comparison. This is faster than using equal to because SSAS does not have to convert the values to strings. Now the Scope will be applied to all members of the dimension except then one checked for in the “If then” statement. If you want to do the opposite, remove the “Not” from in front of the “if then” statement.

Here are the final results in the cube browser.

image

About these ads
This entry was posted in mdx, SSAS, Syndication and tagged , . Bookmark the permalink.

2 Responses to MDX Scope with an If Then statement

  1. Patrick says:

    Nice article. I would only mention that it would be far better for your initial calculation to be “This = null” to keep the cube sparse, and use a format to make the nulls show up as 0.

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