Learn Basic Calculation Script Syntax

When designing calculation scripts for Essbase block storage cubes, there are a few syntax rules to learn. Commands are generally terminated by a semicolon. Quotation marks must be used around certain member names. Essbase can help you validate the syntax.

Overall Syntax Rules

Essbase block storage cubes include a flexible set of commands that you can use to control how the cube 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 conditional or control flow 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 matches 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 ( / ).
- 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 using the Essbase web interface, you can use the syntax checker to validate it.

Use Comments in 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. */

Check the 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.

Refere to 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. Refer to Check Calculation Results.