Essbase Formula Syntax

You develop formulas on block storage cubes using Essbase calculation syntax, including member names, equations, values, operators, statement terminators, and quotation marks.

When you create member formulas, follow these rules:

End each statement in the formula with a semicolon (;). For example:
```
Margin % Sales;
```
Use only saved outline member names. If a substitution variable is used for a member name, the substitution variable value must be a saved outline member name.
Enclose a member name in double quotation marks ("") if the member name meets any of the following conditions:
- Contains spaces. For example:
```
"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. For example, hyphens (-), asterisks (*), and slashes (/).
- Is all numeric or starts with one or more numerals. For example, "100" or "10Prod"
For a full list of member names that must be enclosed in quotation marks, see Naming Conventions in Calculation Scripts, Report Scripts, Formulas, Filters, and Substitution and Environment Variable Values.
End each IF statement in a formula with an ENDIF statement.

For example, the following formula contains a simple IF...ENDIF statement. You can apply this formula to the Commission member in an outline:
```
IF(Sales < 100)
   Commission = 0;
ENDIF;
```
If you are using an IF statement nested within another IF statement, end each IF with an ENDIF. 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 ENDIFs. For example:
```
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.
Ending ENDIF statements with a semicolon (;) is not required, but it is a good practice.

When writing formulas, you can check the syntax using the Formula Editor syntax checker. See Checking Formula Syntax.

See: