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.
To define or edit member formulas in the Simplified dimension editor: