MSAS - Implementing Calculations Using MDX Part 1 Page - 3

The list of functions for building expressions is available in the right pane of the Calculated member builder. To add functions to the Value expression box the user has to double click the function or select the function and click Insert button. The user can also drag the function to the Value expression box.

The Insert button helps the user add data to the Value expression box from the Data pane or the Functions pane. To the extreme right of the Calculated member builder is the Number or arithmetic operator buttons. These help the user add numbers and operators to Value expressions.

The Register button at the bottom of the numeric pad displays the Register Function Libraries dialog box on being clicked. The user can register external function type libraries by clicking on this button. The library types supported are *.olb, *.tlb, *.dll. The new library then appears in the function box once the user has identified the library and selected it. Libraries can be added or removed using this dialog box.

Now that we have had a look at the interface of the Calculated member interface let us proceed with building a calculated member. As stated earlier we had named our member name as Average Price. In the MeasuresLevel expand the measures to display all the measures existing in the cube.

In the Value expression box, construct an expression to produce the values of the calculated member. Use any combination of the following methods to add to the expression:

Select Store Sales, and then drag it into the Value expression box.

In the number and operator pad, click the / operator. The operator appears at the end of the expression in the Value expression box.

Under Data, select the Unit sales measure and drag it to the end of the expression in the Value expression box.

To close Calculated Member Builder, click OK.

To save the cube with the new calculated member, in Cube Editor, on the File menu, click Save.

The user is brought back to the Cube Editor. Average Price is displayed as a calculated member.

Now right click the cube and browse the data in the cube.

Once the calculated member is added to the cube, all dependent virtual cubes must be processed to ensure that users have access to the calculated members. During the processing of the virtual cubes, end users will remain unaffected so long as they remain connected. However, unless the virtual cubes are processed end user will not be able to see the calculated members. Note that Calculated members in regular cubes are not visible in the Analysis manager tree pane. To create and access them, the user will have to navigate to the Cube Editor.