Custom Rollup Formulas and Custom Member Formulas

Custom Rollup Formulas and Custom Member Formulas

Custom rollup formulas and custom member formulas are expressions, written in Multidimensional Expressions (MDX), that determine the cube cell values associated with members. A custom rollup formula applies to all the members (except calculated members) in a level. A custom member formula applies to a single member.

Custom rollup formulas and custom member formulas are different from calculated members. Custom rollup formulas and custom member formulas apply to members that exist in dimension tables. In contrast, calculated members are not stored in dimension tables. Calculated members provide members in addition to those in the tables.

Custom rollup formulas and custom member formulas use similar kinds of MDX expressions.

Both custom rollup formulas and custom member formulas override the aggregate functions associated with measures. For example, before a custom rollup formula is specified, a measure using the Sum aggregate function has the following values for the following members of the Time dimension:

• 1997: 2100
  • Quarter 1: 700
    
    
  • Quarter 2: 500
    
    
  • Quarter 3: 100
    
    
  • Quarter 4: 800
• 1998: 1500
  • Quarter 1: 600
    
    
  • Quarter 2: 200
    
    
  • Quarter 3: 300
    
    
  • Quarter 4: 400

For the Year level, the following custom rollup formula is specified:

Time.CurrentMember.LastChild

This custom rollup formula overrides the Sum aggregate function and produces the following new values for the Year members:

• 1997: 800
  
  
• 1998: 400

The values for the Quarter members are unchanged.

Custom member formulas operate in a similar manner, but each affects only a single member. The value of the custom member is supplied by the custom member formula. For example, the following custom member formula can be used to supply the value for the Quarter 4 child member of the 1998 member in the Time dimension.

Time.[Quarter 3] * 1.5

Custom member formulas are stored in a column of the dimension table. When you enable custom member formulas, a dialog box appears in which you select or create this column. This procedure is summarized later in this topic.

To apply a custom rollup formula to only some members of a level, use the IIf and  RollupChildren functions. The RollupChildren function can roll up the children of a specified member, using unary operators specified in the function. For example, to apply the custom rollup formula Sales * 0.10 to only the children of SalesPersons, type the following custom rollup formula:

IIf(Employees.CurrentMember.Parent.Name = "SalesPersons", Sales * 0.10,
    RollupChildren(Employees.CurrentMember, 
    Employees.CurrentMember.Properties("UNARY_OPERATOR"))

For more information about these functions, see IIf and RollupChildren.

In terms of the order of evaluation, if a level has both a custom rollup formula and custom member formulas, the custom member formulas override the custom rollup formula. Calculated members are resolved before custom rollup formulas and custom member formulas are resolved. If a cube contains multiple dimensions with custom rollup formulas or custom member formulas, the formulas are resolved in the order that the dimensions were added to the cube. You can view and change this order in the Cube Editor tree pane. For more information about the order of evaluation for various formulas, see Understanding Pass Order and Solve Order.

To specify a custom rollup formula in any level except an (All) level, use the Custom Rollup Formula property of the level. To specify a custom rollup formula in an (All) level, use the All Member Formula property of the dimension. You can access these properties in the properties pane of Cube Editor. Clicking the edit (...) button beside the values of these properties displays MDX Builder, in which you can construct the custom rollup formula.

A custom rollup formula can be specified in either a shared or private dimension. If it is specified in both, the custom rollup formula in a cube's private dimension takes precedence.

To enable custom member formulas in a level, use its Custom Members property in the properties pane of Dimension Editor (if the dimension is shared) or Cube Editor (if the dimension is private). Clicking the edit (...) button beside this property's value displays the Define Custom Member Column dialog box in which you select or create a column to store the formulas. After you close this dialog box, if the dimension is write-enabled, you can create the formulas in the custom member formula pane of Dimension Editor (if the dimension is shared) or Dimension Browser. (To perform this action, make sure that the write-enabled dimension is 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 the formulas into the column.

Note  If a cube has a measure with its Aggregate Function property set to Distinct Count, adding a custom rollup operator or expression to a level will cause the cube's structure to become invalid.

See Also

Creating Custom Member Formulas

Properties Pane (Cube Editor Data View)

Using Custom Rollup Operators