Analysis Services MDX - SCOPE Statement

A question was put to me recently on how to setup a measure that performs differently based on different member selections in the cube.

One option is to use lots of nested IF statments. This may well work, but is quite untidy and is known to be a little slow.

Wherever you are considering using IF type logic in your calculation script, it would be a very good idea to consider using the much faster SCOPE statement instead.  The SCOPE statment is clearer and significantly more performant.

As an example of how to use the scope statment i'll use a geometry based illustration:  My cube contains a dimension called Shape that contains 2 members: Square and Circle.  I then have a number of measures:  Length and Radius.  I create a calculated measure called Area.  The calculation for area depends on what shape i have selected.  If i have selected square then Area = (Length)^2, while for Circle it would be Area=Pi*(Radius)^2.

Circle and Square can be thought of as subcubes.  I can therefore define the scope statement as follows:

SCOPE([Measures].[Area],[Shapes].[Circle]); THIS=(Pi*[Measures].[radius]*[Measures].[radius]);
END SCOPE;

SCOPE([Measures].[Area],[Shapes].[Square]); THIS=([Measures].[Length]*[Measures].[Length]);
END SCOPE;

 Note - I would still need to have defined the Area measure in some manner elsewhere in my script.  The above statments only refer to the measure, they dont actually create it.

That is essentially an introduction to how to use the SCOPE statment.  Next post will be on issues to watch out for when using it!