You can create customized measures or dimension members, called calculated members, by combining cube data, arithmetic operators, numbers, and/or functions. For example, you can create a calculated member called Marks that converts dollars to marks by multiplying an existing dollar measure by a conversion rate. Marks can then be displayed to end users in a separate row or column.
Calculated member definitions are stored, but their values exist only in memory. In the preceding example, values in marks are displayed to end users but are not stored as cube data.
You can create calculated members in regular or virtual cubes. For more information, see Creating Calculated Members in Regular Cubes and Creating and Maintaining Calculated Members in Virtual Cubes.
To create a calculated member, use Calculated Member Builder. It enables you to specify the following options for the calculated member:
Select the parent dimension to include the calculated member. Dimensions are descriptive categories by which the numeric data (that is, measures) in a cube can be separated for analysis. In tabular browsers, dimensions provide the column and row headings displayed to end users when they browse a cube's data. (In graphical browsers, they provide other types of descriptive labels but with the same function as in tabular browsers.) A calculated member provides a new heading (or label) in the parent dimension you select.
Alternatively, you can include the calculated member in the measures instead of a dimension. This option also provides a new column or row heading, but it is attached to measures in the browser.
Select a parent member to include the calculated member. This option is unavailable if you select a one-level dimension or Measures as the parent dimension.
Dimensions are divided into levels that contain members. Each member produces a heading. While browsing the cube's data, end users can drill down from a selected heading to previously undisplayed subordinate headings. The heading for the calculated member is added at the level directly below the parent member you select.
Select the name of the calculated member. This name appears as the column or row heading for the calculated member values when end users browse the cube.
Specify the expression that produces the values of the calculated member. This expression can be written in Multidimensional Expressions (MDX). The expression may contain any of the following:
Important Any calculated member that will be used in the value expression of another calculated member must be created before the calculated member that will use it.
After you create a calculated member, you can rename it. Renaming changes the associated column or row heading displayed to end users.
To rename a calculated member
Microsoft® SQL Server™ 2000 Analysis Services provides its own extensive function library for creating calculated members. Analysis Services also supports other function libraries:
A function in a library other than the Analysis Services function library is supported by Analysis Services only if it meets the following conditions:
You should test each function separately from libraries other than the Analysis Services function library before you expose the resulting data to end users. For more information about supported functions, see Visual Basic for Applications Functions and Excel Functions.
Note When you use a function in a library other than the Analysis Services function library, you can omit an optional argument only if you also omit all arguments that follow it.
If multiple libraries include the same function name, Analysis Services functions take precedence. After that, precedence is resolved in order of registration.
In order for end users to see the correct values returned by a function, the library containing the function must be installed and registered on their computers.
You can use the following techniques when you create calculated members:
You can use the keyword Null to create a calculated member whose value is null.
When you create a calculated member, you can specify an integer value for its Solve Order property in the Advanced tab of the properties pane. This property specifies a priority for solving calculated members, zero being the highest priority. The order of solving calculated members becomes an issue for cells at intersections where two or more calculated members are involved and the result depends on the order in which the calculated members are solved. For example, if a calculated member for Annual Growth is presented in columns and Performance is presented in rows, the cell at the intersections can mean Annual Growth of Performance or Performance of Annual Growth, depending on which calculated member is solved first.
A set in a function can be given a temporary alias for use within the function by using the keyword As. In this example, the set {State} is given the temporary alias S1 so it can be referred to later in the function in a nested iteration.
Sum({State} As S1, Sum(Geography.CurrentMember.Children,
Population * Val((Geography.CurrentMember.Properties("CityTax")) +
Val(S1.Current.Properties("StateTax")))))