Understanding Calculation Script Syntax

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:
- Contains spaces.
  
  For example, in the following formula, Opening Inventory and Ending Inventory are enclosed in double quotation marks:
```
"Opening Inventory" = "Ending Inventory" - Sales + Additions;
```
- Is the same as an operator, function name, or keyword.
  
  See Naming Conventions in Calculation Scripts, Report Scripts, Formulas, Filters, and Substitution and Environment Variable Values.
- Includes any nonalphanumeric character, such as a hyphen ( - ), asterisk ( * ), or slash ( / ).
  
  See Naming Conventions in Calculation Scripts, Report Scripts, Formulas, Filters, and Substitution and Environment Variable Values.
- 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.
- Contains a dot (.).
  
  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;)

Note:

If you use ELSE IF (with a space) rather than ELSEIF (one word) in a formula, you must supply an ENDIF for the IF statement.

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.

Adding Comments to Calculation Scripts

You can include comments to annotate calculation scripts. Comments are ignored when the calculation script runs.

To include a comment, start the comment with /* and end the comment with */. For example:

/* This calculation script comment
   spans two lines. */

Checking Syntax

Essbase includes a syntax checker that flags syntax errors (such as a mistyped function name) in a calculation script.

If syntax errors are not found, Essbase indicates the syntax check succeeded.

If syntax errors are found, Essbase indicates the syntax check failed and displays one error at a time. Typically, an error message includes the line number in which the error occurred and a brief description.

See Create Calculation Scripts

Note:

The syntax checker cannot determine semantic errors, which occur when a calculation script does not work as you expect. To find semantic errors, run the calculation and check the results to ensure they are as you expect. See Checking Calculation Results.