Working with Member Formulas

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

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

  • Operator type, function, value, member name, UDA, and so on allowed in formulas.

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

Best Practices:

  • Use member formulas for Level 0 members only.

  • Avoid using member formulas on Custom dimensions, unless required for reporting.

  • Avoid using member formulas on the Movement dimension, due to consolidation performance considerations.

  • Two-pass calculations are recommended to be used on the Account dimension only.

  • Custom member formulas should not be added under the Total Balance Sheet hierarchy.

  • Level 0 members should not be Dynamic Calc without member formulas.

  • Avoid setting leaf Dynamic Calc Account members with formulas or Dynamic Calc Account parents as Two Pass calculation. Instead, use Solve Order. The Two-Pass calculation option will calculate Account as the last dimension, which may sometimes be unnecessary.

  • Avoid using functions mentioned in this topic inside the member formula:https://docs.oracle.com/en/cloud/saas/enterprise-performance-management-common/ecalc/working_with_essbase_hybrid.html. These functions are not supported by Hybrid Essbase.

  • Review any formula that can be calculated after an aggregation such as a ratio. Dynamically calculate with a high solve order.

  • Review any formula which must be computed before aggregation. If performance is slow, consider making it a stored member and use a calculation script.

  • Review any formula that requires data to be retrieved from many data blocks such as a rolling forecast. If performance is slow, consider making it a stored member and use a calculation script.

  • Avoid returning #MISSING in formulas.


    Period Movement Example 3

To define or edit member formulas in the Simplified dimension editor:

  1. View Edit Member Properties.
  2. In the Default Formula column of the grid, select the member for which you want to define or edit a formula. Define or edit the formula for the member using one of the following options:
    • Click the cell once more in the dimension editor grid to enter or edit the formula.

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

    • Click Member Formula icon next to the formula bar, and then enter or edit the formula.

    Tip:

    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.

  3. Optional: To check the validity of a member formula, clickMember Formula icon next to the formula bar, and then click Validate.
  4. Click Save.
  5. To view member formula descriptions, right-click on a column and deselect the Default Mode option to display the Consol Member Formula description column.