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.

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

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.

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.