Essbase provides a flexible set of commands that you can use to control how a database is calculated. You can construct calculation scripts from commands and formulas.
When you create a calculation script, you must apply the following rules:
End each formula or calculation script command with a semicolon (;). For example:
Example 1
CALC DIM(Product, Measures);
Example 2
DATACOPY Plan TO Revised_Plan;
Example 3
"Market Share" = Sales % Sales -> Market;
Example 4
IF (Sales <> #MISSING) Commission = Sales * .9; ELSE Commission = #MISSING; ENDIF;
You do not need to end the following commands with semicolons:
IF ENDIF ELSE ELSIF FIX ENDFIX EXCLUDE ENDEXCLUDE LOOP ENDLOOP
Note: | Although not required, Oracle recommends ending each ENDIF statement in a formula with a semicolon. |
Enclose a member name in double quotation marks (" "), if that member name meets any of the following conditions:
For example, in the following formula, Opening Inventory and Ending Inventory are enclosed in double quotation marks:
"Opening Inventory" = "Ending Inventory" - Sales + Additions;
Includes any nonalphanumeric character, such as a hyphen ( - ), asterisk ( * ), or slash ( / ).
Contains only numerals or starts with a numeral.
For example: “100” or “10Prod”
Begins with an ampersand (&). The leading ampersand (&) is reserved for substitution variables. If a member name begins with &, enclose the name in quotation marks.
Note: | Do not enclose substitution variables in quotation marks in a calculation script. |
For example: 1999.Jan or .100
If you are using an IF statement or an interdependent formula, enclose the formula in parentheses to associate it with the specified member.
For example, the following formula is associated with the Commission member in the database outline:
Commission (IF(Sales < 100) Commission = 0; ENDIF;)
End each IF statement in a formula with an ENDIF statement.
For example, the previous formula contains a simple IF...ENDIF statement.
If you are using an IF statement that is nested within another IF statement, end each IF with an ENDIF statement.
For example:
"Opening Inventory" (IF (@ISMBR(Budget)) IF (@ISMBR(Jan)) "Opening Inventory" = Jan; ELSE "Opening Inventory" = @PRIOR("Ending Inventory"); ENDIF; ENDIF;)
You do not need to end ELSE or ELSEIF statements with ENDIF statements.
For example:
Marketing (IF (@ISMBR(@DESCENDANTS(West)) OR @ISMBR(@DESCENDANTS(East))) Marketing = Marketing * 1.5; ELSEIF(@ISMBR(@DESCENDANTS(South))) Marketing = Marketing * .9; ELSE Marketing = Marketing * 1.1; ENDIF;)
End each FIX statement with an ENDFIX statement.
For example:
FIX(Budget,@DESCENDANTS(East)) CALC DIM(Year, Measures, Product); ENDFIX
End each EXCLUDE statement with an ENDEXCLUDE statement.
When you write a calculation script, use the Calculation Script Editor syntax checker to validate the syntax. See Checking Syntax.