In the text box on the Member Formula tab, you can include predefined formula expressions in member formulas, and test them with the Validate button. You can also load them.
You can update the dimension outline without updating the business rules and calc scripts that depend on the outline. Calculations become more independent of specifics in the outline. You can use Smart Lists as objects in calculations. Performance is not decreased when you use formula expressions because they are run only when you refresh the database.
To use a formula expression in a member formula:
Select the dimension for whose member to add or change a formula.
Select options for the following fields:
Plan Type
Note:
A formula entered for the default plan type will be applied to all plan types unless it is overridden by a different formula entered for a specific plan type.
Data Storage—Select a data storage option. The default is Store.
Note:
The plan type specific data storage field will not display the Shared or Label Only options. This is because a member cannot be set to Shared or Label Only in one plan type and not another.
Solve Order—For aggregate storage plan types only, solve order specifies the order in which formulas are evaluated. Enter a whole number between 0 and 100000 (or use arrows to increase or decrease the number). The formulas for members that have a specified solve order are calculated in order from the lowest solve order to the highest. The default is 0.
In the text box, define formulas for the member.
You can include Planning formula expressions and Essbase native formulas in the member formula. See the on-premises Oracle Essbase Database Administrator's Guide for syntax, rules, and usage on Essbase native formulas.
Planning provides predefined formula expressions that you can use in member formulas. You cannot edit or create your own formula expressions.
Optional: To check the validity of the member formula, click Validate.
Optional: If there are errors in the member formula, click Show Details to view a description.
Optional: Click Reset to restore the previous member formula if you do not want to save the changes you made to the member formula.
Member formula expressions support functions and variables. Follow these syntax rules for functions and variables when you create formula expressions:
Enclose variables or properties with square brackets, [ ]. If you omit square brackets, the variable is treated as a native variable.
Enclose member names with quotation marks.
Characters in variables are case-insensitive, but cannot have extra spaces or characters such underscore ( _ ).
You can include subcalls to other functions within an expression.
Do not enter text where a number is required.
The order of the outline is important in a member formula. For example, do not reference a value that has not been calculated yet.
You can include a Smart List as a variable in a formula expression, such as the formula expression, “Status”= [Status.Departed]
“Status” is the member name, Status is the Smart List name, and Departed is a Smart List entry. If the Smart List ID for Departed is 2, Status.Departed is replaced with a 2 in the member formula (Planning treats Smart Lists as numbers). If the Smart List ID for Departed is 2, 2 is put in the calculation and 2 is stored in the database.
Write Smart Lists in this format: [SmartListName.SmartListEntry]
Planning formula expressions can include these predefined variables and functions.
Table 45. Variables in Formula Expressions
Variable | Description |
|---|---|
Generates an IF statement if the Planning application is a multicurrency application, or an empty string if it is one currency application. Used with ClosedInputValueBlock. | |
Generates an End IF statement if the Planning application is a multicurrency application, or an empty string if it is one currency application. Used with OpenInputValueBlock. | |
Returns the number of time periods in the year | |
Returns the number of years in the application |
Table 46. Functions in Formula Expressions
Function | Description |
|---|---|
Returns the name of a predefined dimension. The dimtags are:
| |
Returns the specified period. The periodName options are:
| |
Generates a cross reference for the account | |
Generates a cross-reference for the account. The account name contains a prefix that you define. The default prefix is No, followed by a blank space and the account name, for example, No Salary. | |
Generates a member formula that returns an index for the time period; the index is based on the calendar year. | |
Generates a member formula that returns an index for the time period; the index is based on the fiscal year. | |
Generates a calendar year-to-date formula for the member | |
Generates a calendar year-to-date formula for the member, and the time period index based on the calendar year and fiscal year. Use when members are renamed. The default member names are “Cal TP-Index” and “Fiscal TP-Index.” |
Follow the rules of syntax carefully. If formula expression syntax contains errors, error messages are returned after you validate the member formula. To get information about error messages, click Show Details on the Member Formula tab. The most common error message is “Failed to execute.” This occurs when you use parameters in the expression incorrectly. These actions cause “Failed to execute” error messages:
Entering the wrong number of parameters in the formula expression
Misspelling member names, functions, or variable names
Not surrounding member names with quotation marks
Including numbers where strings are required