Formulas in Calculation Scripts

Essbase formulas calculate mathematical and positional relationships between members in a database outline. You can apply them to members in the outline, and/or use them in calculation scripts for strict control of your calculations.

When you define Essbase formulas in calculation scripts, they take precedence over any formulas applied to members in the cube outline. Using formulas in this way gives your batch calculations additional flexibility beyond the relationships encoded into the cube outline.

You can write formulas to calculate using basic equations, conditional equations, and interdependent formulas.

In an Essbase calculation script, you can perform both of these operations:

Calculate an existing member formula on the cube outline
Define a new formula that lasts for the duration of the calculation script's execution

To calculate an existing formula that is applied to a member in the outline, use the member name followed by a semicolon (;). For example, the following command in a calc script calculates the formula that is applied to the Variance member in the outline:

Variance;

To override values that result from outline calculation, you can manually apply a formula in the calculation script. For example, the following formula in a calculation script cycles through the cube, adding the values in the members Payroll, Marketing, and Misc, and placing the result in the Expenses member. This formula overrides any formula placed on the Expenses member in the outline:

Expenses = Payroll + Marketing + Misc;

Note:

You cannot apply formulas to shared members or label only members.

See:

Also see Developing Formulas for Block Storage Databases.

Basic Equations in Essbase Calc Scripts

In Essbase calculation scripts, you use equations to assign values to cube outline members. The assignment lasts for the duration of the calculation script's execution.

The syntax for an equation in an Essbase calculation script is:

member = mathematical_expression;

member is a member name from the outline, and mathematical_expression is any valid mathematical expression.

Essbase evaluates the expression and assigns the value to the specified member.

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

Margin = Sales - COGS;

In this example, 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 results in Markup:

Markup = (Retail - Cost) % Retail;

You can also use the > (greater than) and < (less than) logical operators in equations.

In the following example, if February sales are greater than January sales, Sales Increase Flag results in a value of 1; if false, the result is a value of 0:

Sales Increase Flag = Sales -> Feb > Sales -> Jan;

Conditional Equations in Essbase Calc Scripts

You can use conditional equations in Essbase calculation script formulas to define if/else logic and control the flow of events during calculation.

When you use an IF statement as part of a member formula in a calculation script, you must:

Associate the IF statement with a single member
Enclose the IF statement in parentheses

In the following example, the entire IF…ENDIF statement is enclosed in parentheses and associated with the Profit member, Profit (IF(...)...):

Profit
(IF (Sales > 100)
   Profit = (Sales - COGS) * 2;
ELSE
   Profit = (Sales - COGS) * 1.5;
ENDIF;)

Essbase cycles through the database and performs the following calculations:

The IF statement checks whether the value of Sales for the current member combination is greater than 100.
If Sales is greater than 100, Essbase subtracts the value in COGS from the value in Sales, multiplies the difference by 2, and places the result in Profit.
If Sales is less than or equal to 100, Essbase subtracts the value in COGS from the value in Sales, multiplies the difference by 1.5, and places the result in Profit.

For more information about conditional commands and controlling the flow of calc scripts, see Calculation Command Groups

Interdependent Formulas in Essbase Calc Scripts

As with formulas in the outline, a formula in a calculation script calculates mathematical relationships between members in the Essbase cube outline.

Formulas are considered interdependent if their evaluations depend on the value of other members of the same dimension.

A good example of an interdependent formula in Essbase is a cash flow evaluation, in which the opening inventory is dependent on the closing inventory from the previous month.

When you use an interdependent formula in a calculation script, keep in mind that the same rules apply as for the IF statement. You must:

Associate the formula with a single member
Enclose the formula in parentheses

In the following example, the entire interdependent formula is enclosed in parentheses and associated with the Opening Inventory member:

"Opening Inventory"
(IF(NOT @ISMBR (Jan))
   "Opening Inventory" = @PRIOR("Ending Inventory");
ENDIF;)
"Ending Inventory" = "Opening Inventory" - Sales + Additions;