MSAS - Implementing Calculations Using MDX Part 2 Page - 4

Setting a Calculation for a subcube or a selection of cells

Note that calculated cells are available only with Microsoft Analysis Services Enterprise edition. To set a calculation for a group of cells or a subcube follow the steps below.

In the Analysis Manager tree pane expand the cubes folder and right click the Expense Budget cube. Click Edit.

In the Cube Editor click on Data tab to display the data.

Drag the Scenario dimension in the columns to replace the measures dimension

Expand the Scenario dimension so that its four member columns are displayed.

Now right click on the Calculated cells node in the Cube Editor tree pane and then click New Calculated Cells.

The Calculated cells Wizard appears.

Click Next to go on to Define the Calculation Subcube screen.

Select the Account Dimension.

Click Next in the Members Set box and select A Single Member option.

A dimension tree appears. Expand the Dimension and click on Gross Sales.

Now select the Scenario dimension and in the Members Set box select A Single Member.
In the Dimension tree select the Current Year’s Actuals.

Click Next and the Define the Calculation Condition(optional) dialog box appears.

In the MDX expression box enter “Lookup Cube(“[Sales]”, “(Measures.[Store Sales]”, + time.currentmember.Uniquename+ “.Store.[“+Store.currentmember.name+”])”)”.

Click Next to navigate to the final screen. Enter “Gross Sales actual from the Sales cube” in the name box. This name will be used for the object in the Analysis Manager.

Click Finish.

The value for the Current year’s actual, Gross Sales cells have changed. The value shown is derived from Stores Sales measure in the sales cube. The Current year’s Budget cells are also updated automatically based on the member calculation previously defined.

Save the changes and close the cube editor.