Planning member formulas support Essbase native formulas and Planning formula expressions that are evaluated and expanded into Essbase code blocks when the database is refreshed. In these expressions, you can address Smart Lists by name, which Planning replaces with their numerical values in calculations.
In the text box on the Member Formula tab, you can include predefined formula expressions in member formulas, and test them with the Validate Member Formula 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.
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 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 Member Formula and OK.
Optional: If there are errors in the member formula, click Show Details to view a description.
Optional: Click Refresh to restore the previous member formula if you do not want to save the changes you made to the member formula.
Before using formula expressions in member formulas, you should understand Essbase formulas and calculation and the application outline. See the Oracle Essbase Database Administrator's Guide.
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, Essbase replaces Status.Departed with 2 in the member formula (Planning treats Smart Lists as numbers). If the Smart List ID for Departed is 2, Essbase puts 2 in the calculation and stores 2 in the database.
Write Smart Lists in this format: [SmartListName.SmartListEntry]
Planning formula expressions can include these predefined variables and functions.
Table 78. 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 79. 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