Developing Formulas for Block Storage Databases

Table 47 lists the types of operators you can use in formulas:

For information about using operators with #MISSING, zero, and other values, see the “Essbase Functions” section in the Oracle Essbase Technical Reference.

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

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.

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;

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.

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.

Essbase includes Essbase Server-based formula syntax checking that tells you about syntax errors in formulas. For example, Essbase tells you if you have mistyped a function name. Unknown names can be validated against a list of custom-defined macro and function names. If you are not connected to a server or the application associated with the outline, Essbase may connect you to validate unknown names.

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:

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.

You can define formulas that use a conditional test or a series of conditional tests to control the flow of calculation.

The IF and ENDIF commands define a conditional block. The formulas between the IF and the ENDIF commands are executed only if the test returns TRUE (1). If the test returns FALSE (0), you can use the ELSE and ELSEIF commands to specify alternative actions. The formulas following each ELSE command are executed only if the previous test returns FALSE (0). Conditions following each ELSEIF command are tested only if the previous IF command returns FALSE (0). See Understanding Formula Syntax.

When you use a conditional formula in a calculation script, enclose it in parentheses and associate it with a member in the database outline, as shown in the examples in this section.

In conjunction with an IF command, you can use functions that return TRUE or FALSE (1 or 0, respectively) based on the result of a conditional test. These functions are known as Boolean functions.

Use Boolean functions to determine which formula to use. The decision is based on the characteristics of the current member combination. For example, to restrict a certain calculation to the members in the Product dimension that contain input data, preface the calculation with an IF test based on @ISLEV(Product,0).

If one of the function parameters is a cross-dimensional member, such as @ISMBR(Sales -> Budget), all of the parts of the cross-dimensional member must match the properties of the current cell to return a value of TRUE (1).

Table 49 lists Boolean functions that specify conditions:

When you place formulas on the database outline, you can use only the IF, ELSE, ELSEIF, and ENDIF commands and Boolean functions to control the flow of the calculations. You can use additional control commands in a calculation script.

For information about how to develop calculation scripts and how to use them to control how Essbase calculates a database, see Developing Calculation Scripts for Block Storage Databases. For information on individual Essbase functions and calculation commands, see the Oracle Essbase Technical Reference.

You can apply the following formula to a Commission member in the database outline.

In the following example, the formula calculates commission at 1% of sales if the sales are greater than 500000:

IF(Sales > 500000)
   Commission = Sales * .01;
ENDIF;

If you place the formula in a calculation script, you must associate the formula with the Commission member as shown:

Commission (IF(Sales > 500000)
   Commission = Sales * .01;
ENDIF;)

Essbase cycles through the database, performing these calculations:

In the next example, the formula tests the ancestry of the current member and then applies the appropriate Payroll calculation formula:

IF(@ISIDESC(East) OR @ISIDESC(West))
   Payroll = Sales * .15;
ELSEIF(@ISIDESC(Central))
   Payroll = Sales * .11;
ELSE
   Payroll = Sales * .10;
ENDIF;

If you place the formula in a calculation script, you must associate the formula with the Payroll member as shown:

Payroll(IF(@ISIDESC(East) OR @ISIDESC(West))
   Payroll = Sales * .15;
ELSEIF(@ISIDESC(Central))
   Payroll = Sales * .11;
ELSE
   Payroll = Sales * .10;
ENDIF;)

Essbase cycles through the database, performing the following calculations:

See About Multidimensional Calculation Concepts. For information on the @ISIDESC function, see the Oracle Essbase Technical Reference.

Table 50 lists mathematical functions, which allow you to perform many mathematical operations in formulas:

Table 51 lists relationship functions, which allow you to use the member combination that Essbase is currently calculating to look up specific values:

For information about specific Essbase functions, see the Oracle Essbase Technical Reference.

Table 52 lists range functions, which allow you to execute a function for a range of members:

Table 53, List of Financial Functions lists financial functions, which allow you to include financial calculations in formulas:

This section discusses creating formulas that refer to members.

Mar2001

Mar2001, Apr2001, May2001

Jan2000:Dec2000

Q1_2000::Q4_2000

Q1_97::Q4_98, FY99, FY2000

@SIBLINGS(Dept01), Dept65:Dept73, Total_Dept

Working with Member Combinations Across Dimensions

Use the cross-dimensional operator to point to data values of specific member combinations. Create the cross-dimensional operator using a hyphen (-) and a greater-than symbol (>). Do not include a space between the cross-dimensional operator and members.

Figure 114, Specifying a Single Data Value is a simplified illustration of a multidimensional cube, in which Jan is the first column on the X axis, Sales is the fourth and top-most row on the Y axis, and Actual is the first row on the Z axis. In this example, Sales -> Jan -> Actual is the intersection of a single data value.

Figure 114. Specifying a Single Data Value

The following example, which allocates miscellaneous expenses to each product in each market, illustrates how to use the cross-dimensional operator. The value of Misc_Expenses for all products in all markets is known. The formula allocates a percentage of the total Misc_Expenses value to each Product -> Market combination. The allocation is based on the value of Sales for each product in each market.

Misc_Expenses = Misc_Expenses -> Market -> Product * 
   (Sales / ( Sales -> Market -> Product));

Essbase cycles through the database, performing these calculations:

1. Divides the Sales value for the current member combination by the total Sales value for all markets and all products (Sales -> Market -> Product).

2. Multiplies the value calculated in step 1 by the Misc_Expenses value for all markets and all products (Misc_Expenses -> Market -> Product).

3. Allocates the result to Misc_Expenses for the current member combination.

Using the cross-dimensional operator can have significant performance implications. For optimization guidelines, see Using Cross-Dimensional Operators.

This section discusses formulas related to values.

1. January Ending   = January Opening – Sales + Additions
2. February Opening = January Ending
3. February Ending  = February Opening – Sales + Additions
4. March Opening    = February Ending
5. March Ending     = March Opening – Sales + Additions

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

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

Calculating Variances or Percentage Variances Between Actual and Budget Values

You can use the @VAR and @VARPER functions to calculate a variance or percentage variance between budget and actual values.

You may want the variance to be positive or negative, depending on whether you are calculating variance for members on the accounts dimension that are expense or nonexpense items:

• Expense items. You want Essbase to show a positive variance if the actual values are less than the budget values (for example, if actual costs are less than budgeted costs).

• Nonexpense items. You want Essbase to show a negative variance if the actual values are less than the budget values (for example, if actual sales are less than budgeted sales).

By default, Essbase assumes that members are nonexpense items and calculates the variance accordingly.

  To tell Essbase that a member is an expense item:

1. In Outline Editor, select the member.

   The member must be on the dimension tagged as accounts.

2. Open Formula Editor.

   See “Creating and Editing Formulas in Outlines” in the Oracle Essbase Administration Services Online Help.

3. Tag the member as an expense item.

   See Setting Variance Reporting Properties.

When you use the @VAR or @VARPER functions, Essbase shows a positive variance if the actual values are less than the budget values. For example, in Sample.Basic, the children of Total Expenses are expense items. The Variance and Variance % members of the Scenario dimension calculate the variance between the Actual and Budget values. See Figure 115, Variance Example.

Figure 115. Variance Example

Forecasting functions allow you to manipulate data for the purposes of interpolating data or calculating future values. Table 59 lists the functions that forecast values:

For information about specific Essbase functions, see the Oracle Essbase Technical Reference.

Statistical functions allow you to calculate advanced statistics in Essbase.

The date function allows you to use dates with other functions.

@TODATE: Convert date strings to numbers that can be used in calculation formulas

The calculation mode function allows you to specify which calculation mode that Essbase uses to calculate a formula.

@CALCMODE: Specify the calculation mode (cell, block, bottom-up, or top-down) that Essbase uses to calculate a formula

You can create custom-defined functions, to be used in formulas and calculation scripts, to perform calculations not otherwise supported by the Essbase calculation scripting language. Custom-developed functions must be written in the Java programming language and registered on the Essbase Server. The Essbase calculator framework calls them as external functions.

Custom-defined functions are displayed in the functions tree in Calculation Script Editor, where you can select them to insert into a formula.

See Developing Custom-Defined Calculation Functions.

Substitution variables act as placeholders for information that changes regularly; for example, time-period information. You can use substitution variables in formulas that you apply to the database outline.

When the outline is calculated, Essbase replaces the substitution variable with the value that you have assigned to it. You can create and assign values to substitution variables using Administration Services, MaxL, or ESSCMD.

You can set substitution variables at the server, application, and database levels. Essbase must be able to access the substitution variable from the application and database on which you are running the calculation scripts. See Using Substitution Variables.

To use a substitution variable in a formula, enter an ampersand (&), followed by the substitution variable name.

Essbase treats any text string preceded by & as a substitution variable.

For example, assume that the substitution variable UpToCurr is defined as Jan:Jun. You can use the following @ISMBR function as part of a conditional test:

@ISMBR(&UpToCurr)

At the time Essbase calculates the outline, it replaces the substitution variable, as shown:

@ISMBR(Jan:Jun)

In outline member formulas, you can use system environment variables as placeholders for user-specific system settings. Because environment variables are defined at the operating system level, they are available to all formulas on Essbase Server.

Using environment variables in formulas is the same as using them in calculation scripts. See Using Environment Variables in Calculation Scripts and Formulas.