Working with Member Formulas

You can define or edit member formulas directly in the grid, in the formula bar, or in the Member Formula dialog box, where you can validate member formulas.

You can define member formulas that combine operators, calculation functions, dimension and member names, and numeric constants to perform calculations on members.

Member formulas can also include:

  • Operator types, functions, values, member names, and UDAs

  • Predefined formula expressions, including Smart List values, that expand into a formula or value upon database refresh

To define or edit member formulas:

  1. Right-click a column heading and clear the Default Mode option on the drop-down menu.

    Drop-down-menu with the Default Mode option cleared

    For more information on the Default Mode setting, see Customizing the Column Layout.

  2. In the grid, select the member for which you want to add a formula.

  3. With the member selected, scroll to the right in the grid until you see the Default Formula column.

  4. In the Default Formula column, perform one of the following tasks;

    • Double-click a cell in the grid, and then enter or edit a formula in the dialog box that appears.

    • Click within the formula bar above the dimension editor grid, and enter or edit a formula.

    • Click Formula icon next to the formula bar, and then enter or edit a formula in the Member Formula dialog box. When you enter a formula in the Member Formula dialog box, you can take advantage of the editing icons at the top of the dialog box, and you can validate the formula.

    Tip:

    • If you don't see the Default Formula column, you'll need to add it to the grid. Click Select Columns icon to open the Select Columns dialog, expand Member Formula, select Default Formula, and then click OK. After selecting it, the Default Formula column will be included in the grid for all subsequent sessions.

    • To include member names in formulas, keep the focus on the formula cell in the grid. Press Ctrl while clicking the member name you want to include in the formula. The member name will display in the formula bar.

  5. Optional: To check the validity of a member formula, click Formula icon next to the formula bar, and then click Validate.

  6. Click Save.