1. Database Administrator's Guide for Oracle Essbase
  2. Developing Formulas for Block Storage Databases
  3. Understanding Formula Syntax

Understanding Formula Syntax

You develop formulas on block storage databases 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:

    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 a database 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:

Operators

You can use the following types of operators in formulas:

Table 18-1 List of Operator Types

Operator Type Description

Mathematical

Perform common arithmetic operations.

For example, you can add, subtract, multiply, or divide values.

See Mathematical Operations.

Conditional

Control the flow of formula executions based on the results of conditional tests.

For example, you can use an IF statement to test for a specified condition.

See Conditional Tests.

Cross-dimensional

Point to the data values of specific member combinations.

For example, you can point to the sales value for a specific product in a specific region.

See Working with Member Combinations Across Dimensions.

For information about using operators with #MISSING, zero, and other values, see Operation Results on #MISSING Values and Zero (0) Values.

Dimension and Member Names

You can include dimension and member names in a formula. For example:

  • Scenario

  • 100-10

  • Feb

Constant Values

You can assign a constant value to a member. For example:

California = 120;

In this formula, California is a member in a sparse dimension and 120 is a constant value. Essbase automatically creates all possible data blocks for California and assigns the value 120 to all data cells. Many thousands of data blocks may be created.

To assign constants in a sparse dimension to only those intersections that require a value, use a FIX statement. See Constant Values Assigned to Members in a Sparse Dimension.

Nonconstant Values

If you assign anything other than a constant to a member in a sparse dimension, and no data block exists for that member, new blocks may not be created unless Essbase is enabled to create blocks on equations. By default, Create Blocks on Equations is disabled.

For example, to create blocks for West that did not exist before running the calculation, you must enable Create Blocks on Equations for this formula:

West = California + 120;

Note:

If Create Blocks on Equations is disabled for a database and data blocks exist for members on either the left- or right-side of the equation, the formula produces results.

You can enable Create Blocks on Equations at the database level, whereby blocks are always created, or you can control block creation within calculation scripts using the SET CREATEBLOCKONEQ ON | OFF calculation command.

Because unnecessary blocks can be created when Create Blocks on Equations is enabled at the application or database level, calculation performance can be affected. To control block creation within a calculation script, use the SET CREATEBLOCKONEQ ON | OFF calculation command. See Nonconstant Values Assigned to Members in a Sparse Dimension.

To enable the Create Blocks on Equations feature for all calculation scripts for a specific database, you can use the alter database MaxL statement.

Basic Equations

You can apply a mathematical operation to a formula to create a basic equation. The equation can be in the database outline or in a calculation script.

The syntax for an equation:

member = mathematical_operation;

member is a member name from the database outline and mathematical_operation is any valid mathematical operation.

In the following example, Essbase cycles through the database, subtracting the values in COGS from the values in Sales, and placing the results in Margin: 

Margin = Sales - COGS;

The following example shows how to use an equation in the database outline and in a calculation script. In the outline, apply the following formula to a Markup member:

(Retail - Cost) % Retail;

Then, in a calculation script, use this formula:

Markup = (Retail - Cost) % Retail;

Essbase cycles through the database, subtracting the values in Cost from the values in Retail, calculating the resulting values as a percentage of the values in Retail, and placing the result in Markup.

Checking Formula Syntax

Essbase includes formula syntax checking that tells you about syntax errors in formulas. For example, Essbase tells you if you have mistyped a function name.

A syntax checker cannot tell you about semantic errors in a formula. Semantic errors occur when a formula does not work as you expect. To find semantic errors, run the calculation and check the results to ensure that they are as you expect.

Essbase displays the syntax checker results at the bottom of the Formula Editor. If Essbase finds no syntax errors, it displays the “No errors” message.

If Essbase finds one or more syntax errors, it displays the number of the line that includes the error and a brief description of the error. For example, if you do not include a semicolon end-of-line character at the end of a formula, Essbase displays a message similar to the following message: 

Error: line 1: invalid statement; expected semicolon

If a formula passes validation in Formula Editor or Outline Editor, but Essbase Server detects semantic errors when the outline is saved, check the following:

  • The incorrect formula is saved as part of the outline, even though it contains errors.

  • Essbase Server writes a message in the application log that indicates what the error is and displays the incorrect formula.

  • Essbase Server writes an error message to the comment field of the member associated with the incorrect formula. The message indicates that the incorrect formula was not loaded. You can view this comment in Outline Editor by closing and reopening the outline.

  • If you do not correct the member formula, and a calculation that includes that member is run, the formula is ignored during the calculation.

After you have corrected the formula and saved the outline, the message in the member comment is deleted. You can view the updated comment when you reopen the outline.