Custom member formulas determine the cell values associated with members and override the aggregate functions of measures. They are written in Multidimensional Expressions (MDX). Each custom member formula applies to a single member. Custom member formulas are stored in the dimension table.
Custom member formulas can include MDX functions in Microsoft® SQL Server™ 2000 Analysis Services and some Microsoft Visual Basic® for Applications functions. Custom member formulas can also include some functions from the Microsoft Excel worksheet function library if it is installed. If installed, this library is registered automatically. Other functions are also supported if their libraries are installed and registered. For more information about installing and registering custom function libraries, see Register Function Libraries Dialog Box.
Before you can create custom member formulas for a level, you must first create or select a column in the dimension table to store the custom member formulas. To create or select a column for an existing dimension, use the Define Custom Member Column dialog box.
To create or select a column to store custom member formulas
After you create or select a column to store the custom member formulas, you can begin creating the custom member formulas. If the dimension is write-enabled, you can use the custom member formula pane of Dimension Editor or Dimension Browser to create a custom member formula. (This procedure requires that the write-enabled dimension be included in a cube that was processed since the dimension last changed.) If the dimension is not write-enabled, you must use a tool other than Analysis Manager to insert custom member formulas into the column. The Decision Support Objects (DSO) library supports this functionality through the CustomRollupExpression property of the Level interface. For more information about the CustomRollupExpression property, see CustomRollupExpression (Level Interface).
To create a custom member formula in a write-enabled dimension
After custom member formulas are created or updated in a shared dimension, it must be processed with the Incremental update option. After custom member formulas are created or updated in a private dimension, its cube must be processed with the Refresh data option. Exceptions are write-enabled dimensions whose custom member formulas are created or updated in Analysis Manager or through client applications; these dimensions and the cubes that contain them do not require subsequent processing. For more information, see Updating and Refreshing Cube Data and Updating and Rebuilding Shared Dimensions.
Note If a cube has multiple custom member formulas, they are evaluated in the order that the dimensions are included in the cube. Therefore, if you want to change the order in which custom member formulas are evaluated in the cube, change the order of the dimensions in Cube Editor. If you have dimensions with custom member formulas that need to be evaluated first and you want to move them to the top of the tree, use the tree pane to delete and then add them again to the cube. You can use the same technique to move other dimensions with custom member formulas within the tree pane so that they correspond to the order of their evaluation.
Custom Rollup Formulas and Custom Member Formulas
Define Custom Member Column Dialog Box