MSAS - Implementing Calculations Using MDX Part 2

In this part 2 of  Implementing Calculations Using MDX, we will be learning about Renaming Calculated members, Creating Non Measure Calculated members, Using Functions in Calculated Members, Setting calculations at Member level and Setting a Calculation for a subcube or a selection of cells.

Renaming Calculated members

1. In the Analysis Manager tree pane, under the database that contains the calculated member, expand the Cubes folder.

2. Right-click the cube that contains the calculated member, and then click Edit.

3. In the Cube Editor tree pane, right-click the calculated member, and then click Rename.

4. In the box next to the calculated member icon, type a new name, and then click outside the box.

Creating Non Measure Calculated members

In the Analysis Manager tree pane, right-click the Sales cube, and then click Edit.

In Cube Editor, on the Insert menu, click Calculated Member.

In Calculated Member Builder, in the Parent dimension box, select the dimension that will include the calculated member.

In the Parent member box, specify the member that will include the calculated member. Click Change to select a member other than the displayed member(if any).

In the Member name box, type a name for the non measure calculated member to be created. Let us say we want to display the data for total sales_cost and unit_sales for Canada and Mexico in the sales cube.

In the Value expression box, construct an expression to produce the values of the non measure calculated member.

WITH

MEMBER[STATE].[NORTH AMERICA].[NON-US] AS

‘[Canada] +[Mexico]’

SELECT

[Measures].Members ON COLUMNS,

[State].[Country].Members ON ROWS

FROM Sales

Use any combination of the following methods to add to the expression:

Drag items from the Data and Functions boxes.

Click an item in the Data or Functions box, and then click Insert.

Click the arithmetic operator and number buttons.

Run the query and browse the data. Note that the new members do not appear. The Column axis supplies the member from the Measures dimension(unit_sales) and the row axis supplies the members from the State Dimension(Non-US). Note that using the + sign to add values works well if the user has only two or three values to aggregate. It becomes cumbersome if there are multiple values.

Unlike the measures dimension, the Members function does not retrieve calculated members of the non-measure dimensions. When a calculated member is created on a non measure dimension the member will by definition intersect with all the members of the Measures dimension. Each of the measures already has an aggregated function defined. Using an Aggregate function will help the user take advantage of the previously defined aggregation function.

To save the calculated member, in the cube editor on the File menu, click Save.