MDX Scripting with SCOPE Statement

While working at a client this past month, I encountered a set of special requirements that specified the need for some sets of custom calculations to be applied to some very specific subcubes. I may have been able to accomplish this goal with some very complex and painful-to-look-at MDX, but a better alternative for solving these requirements was to use a SCOPE statement. A SCOPE statement is ideal for limiting an MDX expression to a specific subcube, and in this article I am going to show you how, using a simple example in the Adventure Works cube, to make this happen.

So imagine that you need to apply some special calculations to a measure group that holds some forecasted sales numbers. As you might expect, sales in the fourth quarter will probably be higher than sales during the rest of the year. Also, sales within the Thanksgiving to Christmas time frame might also be higher than other weeks within the fourth quarter. To reflect the increase in the amount of sales during that time of the year, we'll need to use an MDX script that utilizes the SCOPE statement to apply a calculation to adjust those forecasts sales measures accordingly.

Browsing the Adventure Works cube, I've sliced the Sales Order Count measure (which will be our imaginary Forecasted Sales Order Count measure group) by Month of the Year and Calendar Quarter of the Year, just so we'll have some base line numbers to compare to our calculations after they've been applied.

But because we want to reflect the increase in sales during the fourth quarter, let's create an MDX calculation script that uses the SCOPE statement that makes this adjustment for us. Go to your Calculations tab after opening your Analysis Services project, and click the New Script Command icon .

Use this MDX script to apply our custom calculation to the subcube including the fourth quarter of our calendar year and Sales Orders Count measure:

SCOPE ([Date].[Calendar Quarter of Year].&[CY Q4],[Measures].[Order Count]); 

THIS = ([Measures].[Order Count]*2); 

END SCOPE  

The SCOPE statement specifies the subcube to which our custom calculation should be applied. The THIS statement specific the custom calculation to be applied to the subcube.

After adding this script and deploying and processing the cube, you can see the results of our calculation here.

Now the Sales Order Count during the fourth quarter has been multiplied by two while the rest of the Sales Order Counts during the rest of the year remain untouched.

To apply a custom calculation to the month of December to reflect the forecasted increase in sales during the month, you would do something similar to what we just did: 

SCOPE ([Date].[Calendar Quarter of Year].&[CY Q4],[Measures].[Order Count]); 

THIS = ([Measures].[Order Count]*2);  

END SCOPE

SCOPE ([Date].[Month of Year].&[12],[Measures].[Order Count]); 

THIS = ([Measures].[Order Count]*2.5); 

END SCOPE 

Here are the results:

Be aware of the fact that our custom calculations have stacked on top of one another. Because the initial calculation applied to the subcube of the fourth quarter has already been applied by the time the calculation is being applied to the month of December, we are actually multiplying the Sales Order Count by 2, and then by 2.5 for the month of December. If we wanted to only multiply the Sales Order Count by 2 for the months of October and November, and then multiply the Sales Order Count for the month of December by 2.5 without stacking our calculations, we'd need to do something like this:

SCOPE (({[Date].[Month of Year].&[10],[Date].[Month of Year].&[11]}),[Measures].[Order Count]);

THIS = ([Measures].[Order Count]*2);

END SCOPE; 

SCOPE ([Date].[Month of Year].&[12],[Measures].[Order Count]);

THIS = ([Measures].[Order Count]*2.5);

END SCOPE

And the results: 

I hope this has been a good example to help you understand how to use the SCOPE statement in your calculations and to give you a good starting point for applying calculations to certain subcubes within your cube. Another useful aspect of SCOPE statements to keep in mind is the fact that SCOPE statements can be embedded within one another, allowing you to apply one calculation to a specific subcube and another calculation to a certain subcube within the first specific subcube, so feel free to try SCOPE out and get as creative as you can to meet those obscure and confusing requirements handed to you by your end users! And feel free to post any comments, questions, or feedback, BIDN buddies!