Developing Formulas for Block Storage Databases

In This Section:

Using Formulas and Formula Calculations

Process for Creating Formulas

Understanding Formula Syntax

Using Functions in Formulas

Using Substitution and Environment Variables in Formulas

Using Formulas on Partitions

Displaying Formulas

The information in this chapter applies only to block storage databases and is not relevant to aggregate storage databases.

Also see:

All of the examples in this chapter are based on the Sample.Basic database.

For more information about the functions referenced in this chapter, see the Oracle Essbase Technical Reference.

Using Formulas and Formula Calculations

Formulas calculate relationships between members in a database outline. With formulas, you can:

  • Apply formulas to members in the database outline. Use this method if you do not need to control database calculations carefully for accuracy or performance. This method limits formula size to less than 64 KB.

    See Using Functions in Formulas.

  • Place formulas in a calculation script. Use this method if you need to control database calculations carefully.

    See Using Formulas in Calculation Scripts.

Figure 113, Calculation of Margin %, Profit %, and Profit per Ounce shows the Measures dimension from the Sample.Basic database. The Margin %, Profit %, and Profit per Ounce members are calculated using the formulas applied to them.

Figure 113. Calculation of Margin %, Profit %, and Profit per Ounce

This image shows an outline in which formulas are applied to the Margin %, Profit %, and Profit per Ounce members, as described in the text preceding the image.

For formulas applied to members in a database outline, Essbase calculates formulas when you perform the following actions:

For a formula in a calculation script, Essbase calculates the formula when it occurs in the calculation script.

If a formula is associated with a dynamically calculated member, Essbase calculates the formula when the user requests the data values. In a calculation script, you cannot calculate a dynamically calculated member or make a dynamically calculated member the target of a formula calculation. See Dynamically Calculating Data Values.

Using dynamically calculated members in a formula on a database outline or in a calculation script can significantly affect calculation performance. Performance is affected because Essbase interrupts the regular calculation to perform the dynamic calculation.

You cannot use substitution variables in formulas that you apply to the database outline. See Using Substitution Variables in Formulas.

Process for Creating Formulas

You use Formula Editor, a tab in the Member Properties dialog box in Outline Editor, to create formulas. You can type the formulas directly into the formula text area, or you can use the Formula Editor UI features to create the formula.

Formulas are plain text. If required, you can create a formula in the text editor of your choice and paste it into Formula Editor.

To create a formula:

  1. In Outline Editor, select the member to which to apply the formula.

  2. Open Formula Editor.

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

  3. Enter the formula text.

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

  4. Check the formula syntax.

    See Checking Formula Syntax.

  5. Save the formula.

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

  6. Save the outline.

    See “Saving Outlines” in the Oracle Essbase Administration Services Online Help.

Understanding Formula Syntax

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 Restrictions 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

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

Table 47. 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 the “Essbase Functions” section in the Oracle Essbase Technical Reference.

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.

  To enable the Create Blocks on Equations feature for all calculation scripts for a specific database, use a tool:

Tool

Topic

Location

Administration Services

Enabling Create Blocks on Equations

Oracle Essbase Administration Services Online Help

MaxL

alter database

Oracle Essbase Technical Reference

ESSCMD

SETDBSTATE

Oracle Essbase Technical Reference

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.

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

  • 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.

  To check formula syntax, see “Creating and Editing Formulas in Outlines” in Oracle Essbase Administration Services Online Help.

Using Functions in Formulas

Functions are predefined routines that perform specialized calculations and return sets of members or data values. Table 48 lists the types of functions you can use in formulas:

Table 48. List of Function Types

Function Type

Description

Boolean

Provide a conditional test by returning a TRUE (1) or FALSE (0) value.

For example, you can use the @ISMBR function to determine whether the current member matches any members specified.

See Conditional Tests.

Mathematical

Perform specialized mathematical calculations.

For example, you can use the @AVG function to return the average value of a list of members.

See Mathematical Operations.

Relationship

Look up data values within a database during a calculation.

For example, you can use the @ANCESTVAL function to return the ancestor values of a specified member combination.

See Member Relationship Functions.

Range

Declare a range of members as an argument to another function or command.

For example, you can use the @SUMRANGE function to return the sum of all members within a specified range.

See Range Functions.

Financial

Perform specialized financial calculations.

For example, you can use the @INTEREST function to calculate simple interest or the @PTD function to calculate period-to-date values.

See Financial Functions.

Specifying member lists and ranges

Specify multiple members or a range of members.

For example, the @ISMBR function tests to see if a member that is currently being calculated matches any of a list or range of specified members.

See Specifying Member Lists and Ranges.

Generating member lists

Generate a list of members that is based on a specified member.

For example, you can use the @ICHILDREN function to return a specified member and its children.

See Generating Member Lists.

Character string manipulation

Manipulate character strings for member and dimension names.

For example, you can generate member names by adding a character prefix to a name or removing a suffix from a name, or by passing the name as a string.

See Manipulating Member Names.

Member combinations across dimensions

Point to data values of specific member combinations by using the cross-dimensional operator (->).

See Working with Member Combinations Across Dimensions.

Interdependent values

For formulas that require values from members of the same dimension, but for which the required values have not yet been calculated.

See Using Interdependent Values.

Variances and variance percentages

Calculate a variance or percentage variance between budget and actual values.

See Calculating Variances or Percentage Variances Between Actual and Budget Values.

Allocation

Allocate values that are input at a parent level across child members. You can allocate values within the same dimension or across multiple dimensions.

For example, you can use the @ALLOCATE function to allocate sales values that are input at a parent level to the children of the parent; the allocation of each child is determined by its share of the sales of the previous year.

See Allocating Values.

Forecasting

Manipulate data for the purposes of smoothing or interpolating data, or calculating future values.

For example, you can use the @TREND function to calculate future values that are based on curve-fitting to historical values.

See Forecasting Functions.

Statistical

Calculate advanced statistics. For example, you can use the @RANK function to calculate the rank of a specified member or a specified value in a data set.

See Statistical Functions.

Date and time

Use date and time characteristics in calculation formulas.

For example, you can use the @TODATE function to convert date strings to numbers that can be used in calculation formulas.

See Date and Time Function.

Calculation mode

Specify calculation modes that Essbase is to use to calculate a formula—cell, block, bottom-up, and top-down.

See Calculation Mode Function.

Custom-defined

This type enables you to perform functions that you develop for calculation operations. These custom-developed functions are written in the Java programming language and are called by the Essbase calculator framework as external functions.

See Custom-Defined Functions.

Note:

Abbreviations of functions are not supported. Some commands may work in an abbreviated form, but if another function has a similar name, Essbase may use the wrong function. Use the complete function name to ensure correct results.

Conditional Tests

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:

Table 49. List of Boolean Functions That Test Conditions

Function

Condition

@ISACCTYPE

Current member has a specified accounts tag (for example, an Expense tag)

@ISANCEST

Current member is an ancestor of the specified member

@ISIANCEST

Current member is an ancestor of the specified member, or the specified member itself

@ISCHILD

Current member is a child of the specified member

@ISICHILD

Current member is a child of the specified member, or the specified member itself

@ISDESC

Current member is a descendant of the specified member

@ISIDESC

Current member is a descendant of the specified member, or the specified member itself

@ISGEN

Current member of the specified dimension is in the generation specified

@ISLEV

Current member of the specified dimension is in the level specified

@ISMBR

Current member matches any of the specified members

@ISPARENT

Current member is the parent of the specified member

@ISIPARENT

Current member is the parent of the specified member, or the specified member itself

@ISSAMEGEN

Current member (of the same dimension as the specified member) is in the same generation as the specified member

@ISSAMELEV

Current member (of the same dimension as the specified member) is in the same level as the specified member

@ISSIBLING

Current member is a sibling of the specified member

@ISISIBLING

Current member is a sibling of the specified member, or the specified member itself

@ISUDA

A specified UDA exists for the current member of the specified dimension

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.

Examples of Conditional Tests

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:

  1. The IF statement checks to see if the value of Sales for the current member combination is greater than 500000.

  2. If Sales is greater than 500000, Essbase multiplies the value in Sales by 0.01 and places the result in Commission.

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:

  1. The IF statement uses the @ISIDESC function to check whether the current member on the Market dimension is a descendant of either East or West.

  2. If the current member on the Market dimension is a descendant of East or West, Essbase multiplies the value in Sales by 0.15 and moves on to the next member combination.

  3. If the current member is not a descendant of East or West, the ELSEIF statement uses the @ISIDESC function to check whether the current member is a descendant of Central.

  4. If the current member on the Market dimension is a descendant of Central, Essbase multiplies the value in Sales by 0.11 and moves to the next member combination.

  5. If the current member is not a descendant of East, West, or Central, Essbase multiplies the value in Sales by 0.10 and moves to the next member combination.

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

Mathematical Operations

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

Table 50. List of Mathematical Functions

Function

Operation

@ABS

Return the absolute value of an expression

@AVG

Return the average value of the values in the specified member list

@EXP

Return the value of e (the base of natural logarithms) raised to power of the specified expression

@FACTORIAL

Return the factorial of an expression

@INT

Return the next-lowest integer value of a member or expression

@LN

Return the natural logarithm of a specified expression

@LOG

Return the logarithm to a specified base of a specified expression

@LOG10

Return the base-10 logarithm of a specified expression

@MAX

Return the maximum value among the expressions in the specified member list

@MAXS

Return the maximum value among the expressions in the specified member list, with the ability to skip zero and #MISSING values

@MIN

Return the minimum value among the expressions in the specified member list

@MINS

Return the minimum value among the expressions in the specified member list, with the ability to skip zero and #MISSING values

@MOD

Return the modulus produced by the division of two specified members

@POWER

Return the value of the specified member raised to the specified power

@REMAINDER

Return the remainder value of an expression

@ROUND

Return the member or expression rounded to the specified number of decimal places

@SUM

Return the summation of values of all specified members

@TRUNCATE

Return the truncated value of an expression

@VAR

Return the variance (difference) between two specified members.

See Calculating Variances or Percentage Variances Between Actual and Budget Values.

@VARPER

Return the percentage variance (difference) between two specified members.

See Calculating Variances or Percentage Variances Between Actual and Budget Values.

Member Relationship Functions

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

Table 51. List of Member Relationship Functions

Function

Look-up Value

@ANCESTVAL

Ancestor values of the specified member combination

@ATTRIBUTEVAL

Numeric value of the attribute from the specified numeric or date attribute dimension associated with the current member

@ATTRIBUTESVAL

Text value of the attribute from the specified text attribute dimension associated with the current member

@ATTRIBUTEBVAL

Value (TRUE or FALSE) of the attribute from the specified Boolean attribute dimension associated with the current member

@CURGEN

Generation number of the current member combination for the specified dimension

@CURLEV

Level number of the current member combination for the specified dimension

@GEN

Generation number of the specified member

@LEV

Level number of the specified member

@MDANCESTVAL

Ancestor values of the specified member combination across multiple dimensions

@SANCESTVAL

Shared ancestor values of the specified member combination

@PARENTVAL

Parent values of the specified member combination

@MDPARENTVAL

Parent values of the specified member combination across multiple dimensions

@SPARENTVAL

Shared parent values of the specified member combination

@XREF

Data value from another database to be used for calculation of a value from the current database

@XWRITE

Used to write values to another Essbase database, or to the same database

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

Range Functions

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

Table 52. List of Range Functions

Function

Calculation

@AVGRANGE

The average value of a member across a range of members

@CURRMBRRANGE

A range of members that is based on the relative position of the member combination Essbase is currently calculating

@MAXRANGE

The maximum value of a member across a range of members

@MAXSRANGE

The maximum value of a member across a range of members, with the ability to skip zero and #MISSING values

@MDSHIFT

The next or nth member in a range of members, retaining all other members identical to the current member across multiple dimensions

@MINRANGE

The minimum value of a member across a range of members

@MINSRANGE

The minimum value of a member across a range of members, with the ability to skip zero and #MISSING values

@NEXT

The next or nth member in a range of members

@NEXT

The next or nth member in a range of members, with the option to skip #MISSING, zero, or both values

@PRIOR

The previous or nth previous member in a range of members

@PRIORS

The previous or nth previous member in a range of members, with the option to skip #MISSING, zero, or both values

@SHIFT

In some cases, @SHIFTPLUS or @SHIFTMINUS

The next or nth member in a range of members, retaining all other members identical to the current member and in the specified dimension

@SUMRANGE

The summation of values of all specified members across a range of members

Financial Functions

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

Table 53. List of Financial Functions

Function

Calculation

@ACCUM

An accumulation of values up to the specified member

@COMPOUND

The proceeds of a compound interest calculation

@COMPOUNDGROWTH

A series of values that represent the compound growth of the specified member across a range of members

@DECLINE

Depreciation for a specific period, calculated using the declining balance method

@DISCOUNT

A value discounted by the specified rate, from the first period of the range to the period in which the amount to discount is found

@GROWTH

A series of values that represents the linear growth of the specified value

@INTEREST

The simple interest for a specified member at a specified rate

@IRR

The internal rate of return on a cash flow

@NPV

The Net Present Value of an investment (based on a series of payments and incomes)

@PTD

The period-to-date values of members in the dimension tagged as time

@SLN

The amount per period that an asset in the current period may be depreciated (calculated across a range of periods).

The depreciation method used is straight-line depreciation.

@SYD

The amount per period that an asset in the current period may be depreciated (calculated across a range of periods).

The depreciation method used is sum of the year's digits.

Note:

One member formula cannot contain multiple financial functions (for example, @NPV and @SLN, or multiple instances of @NPV). A member formula that requires multiple financial functions must be broken into separate formulas so that each formula contains only one financial function (for example, MemberName(@NPV(...));Membername(@NPV(...))).

Member-Related Functions

This section discusses creating formulas that refer to members.

Specifying Member Lists and Ranges

In some functions, you may need to specify multiple members, or you may need to specify a range of members. For example, the @ISMBR function tests to see if a member that is currently being calculated matches any of a list or range of specified members.

Table 54 lists the syntax for specifying members:

Table 54. Syntax for Specifying Member Lists and Ranges

Member List or Range

Syntax

One member

The member name.

For example:

Mar2001

A list of members

A comma-delimited (,) list of member names.

For example:

Mar2001, Apr2001, May2001

A range of all members at the same level, between and including the two defining members

The two defining member names separated by a colon (:). For example:

Jan2000:Dec2000

A range of all members in the same generation, between and including the two defining members

The two defining member names separated by two colons (::).

For example:

Q1_2000::Q4_2000

A function-generated list of members or a range of members

For a list of member list contents and corresponding functions, see Generating Member Lists.

A combination of ranges and list

Separate each range, list, and function with a comma (,).

For example:

Q1_97::Q4_98, FY99, FY2000

or

@SIBLINGS(Dept01), Dept65:Dept73, Total_Dept

If you do not specify a list of members or a range of members in a function that requires either, Essbase uses the level 0 members of the dimension tagged as time. If no dimension is tagged as time, Essbase displays an error message.

Generating Member Lists

Member set functions allow you to generate member lists that are based on a specified member or member list. Table 55 lists member set functions:

Table 55. List of Member Set Functions

Function

Contents of Member List

@ALLANCESTORS

All ancestors of the specified member, including ancestors of the specified member as a shared member. This function does not include the specified member.

@IALLANCESTORS

All ancestors of the specified member, including ancestors of the specified member as a shared member. This function includes the specified member.

@ANCEST

The ancestor of the specified member at the specified generation or level

@ANCESTORS

All ancestors of the specified member (optionally, up to the specified generation or level), but not the specified member

@IANCESTORS

All ancestors of the specified member (optionally, up to the specified generation or level), including the specified member

@LANCESTORS

All ancestors of the specified list of members (optionally, up to the specified generation or level), but not including the specified members

@ILANCESTORS

All ancestors of the specified list of members (optionally, up to the specified generation or level), including the specified members

@ATTRIBUTE

All base-dimension members that are associated with the specified attribute-dimension member

@WITHATTR

All base members that are associated with attributes that satisfy the specified conditions

@BETWEEN

All members whose name string value fall between, and are inclusive of, two specified string tokens

@CHILDREN

All children of the specified member, but not including the specified member

@ICHILDREN

All children of the specified member, including the specified member

@CURRMBR

The current member being calculated for the specified dimension

@DESCENDANTS

All descendants of the specified member (optionally, up to the specified generation or level), but not the specified member nor descendants of shared members

@IDESCENDANTS

All descendants of the specified member (optionally, up to the specified generation or level), including the specified member, but not descendants of shared members

@LDESCENDANTS

All descendants of the specified list of members (optionally, down to the specified generation or level), but not including the specified members

@ILDESCENDANTS

All descendants of the specified list of members (optionally, down to the specified generation or level), including the specified members

@RDESCENDANTS

All descendants of the specified member (optionally, up to the specified generation or level), including descendants of shared members, but not the specified member

@IRDESCENDANTS

All descendants of the specified member (optionally, up to the specified generation or level), including the specified member and descendants of shared members

@EQUAL

Member names that match the specified token name

@NOTEQUAL

Member names that do not match the specified token name

@EXPAND

Expands a member search by calling a member set function for each member in a member list

@GENMBRS

All members of the specified generation in the specified dimension

@LEVMBRS

All members of the specified level in the specified dimension

@LIKE

Member names that match the specified pattern.

@LIST

Separate lists of members to be processed by functions that require multiple list arguments

@MATCH

All members that match the specified wildcard selection

@MBRCOMPARE

Member names that match the comparison criteria

@MBRPARENT

Parent of the specified member

@MEMBER

The member with the name that is provided as a character string

@MERGE

A merged list of two member lists to be processed by another function

@PARENT

The parent of the current member being calculated in the specified dimension

@RANGE

A member list that crosses the specified member from one dimension with the specified member range from another dimension

@REMOVE

A list of members from which some members have been removed

@RELATIVE

All members of the specified generation or level that are above or below the specified member

@SHARE

A member list that identifies all shared members among the specified members

@SIBLINGS

All siblings of the specified member, but not the specified member

@ISIBLINGS

All siblings of the specified member, including the specified member

@LSIBLINGS

All siblings that precede the specified member in the database outline, but not the specified member

@RSIBLINGS

All siblings that follow the specified member in the database outline, but not the specified member

@ILSIBLINGS

All siblings that precede the specified member in the database outline, including the specified member

@IRSIBLINGS

All siblings that follow the specified member in the database outline, including the specified member

@SHIFTSIBLING

The sibling at the specified distance from the member

@NEXTSIBLING

The next, or right-most, sibling of the member

@PREVSIBLING

The previous, or left-most, sibling of the member

@UDA

All members that have a common UDA defined on Essbase Server

@XRANGE

A member list that identifies the range of members between (and inclusive of) two specified single or cross-dimensional members at the same level

Manipulating Member Names

You can work with member names as character strings. Table 56, List of Character String Manipulation Functions lists character string manipulation functions:

Table 56. List of Character String Manipulation Functions

Function

Character String Manipulation

@CONCATENATE

Create a character string that is the result of appending a member name or specified character string to another member name or character string

@NAME

Return a member name as a string

@SUBSTRING

Return a substring of characters from another character string or from a member name

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

This image shows a cube, in which the intersection of a single data value is shown, as described in the text preceding the table.

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.

Value-Related Functions

This section discusses formulas related to values.

Using Interdependent Values

Essbase optimizes calculation performance by calculating formulas for a range of members in the same dimension simultaneously. Some formulas, however, require values from members of the same dimension, and Essbase may not yet have calculated the required values.

A good example is that of cash flow, in which the opening inventory is dependent on the ending inventory from the previous month.

The values for Opening Inventory and Ending Inventory must be calculated on a month-by-month basis. Assume you want to achieve the results shown in Table 57:

Table 57. Data Values for Cash Flow Example

 

Jan

Feb

Mar

Opening Inventory

100

120

110

Sales

50

70

100

Addition

70

60

150

Ending Inventory

120

110

160

Assuming that the Opening Inventory value for January is loaded into the database, the following calculations are required to get the results in Table 57:

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

You can calculate the required results by applying interdependent, multiple equations to one member in the database outline.

The following formula, applied to the Opening Inventory member in the database outline, calculates the correct values:

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

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

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

Essbase cycles through the months, performing the following calculations:

  1. The IF statement and @ISMBR function check that the current member on the Year dimension is not Jan. This step is necessary because the Opening Inventory value for Jan is an input value.

  2. If the current month is not Jan, the @PRIOR function obtains the value for the Ending Inventory for the previous month. This value is then allocated to the Opening Inventory of the current month.

  3. The Ending Inventory is calculated for the current month.

Note:

To calculate the correct results, you must place the above formula on one member, Opening Inventory. If you place the formulas for Opening Inventory and Ending Inventory on their separate members, Essbase calculates Opening Inventory for all months and then Ending Inventory for all months. This organization means that the value of the Ending Inventory of the previous month is not available when Opening Inventory is calculated.

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

This image shows an outline that illustrates calculating variances, as described in the text preceding the table.

Allocating Values

Allocation functions allow you to allocate values that are input at the parent level across child members in the same dimension or in different dimensions. The allocation is based on a variety of specified criteria.

Table 58. List of Allocation Functions

Function

Allocated Values

@ALLOCATE

Values from a member, cross-dimensional member, or value across a member list within the same dimension.

@MDALLOCATE

Values from a member, cross-dimensional member, or value across multiple dimensions.

For examples of calculation scripts using @ALLOCATE, see Allocating Costs Across Products; using @MDALLOCATE, see Allocating Values Across Multiple Dimensions.

Forecasting Functions

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:

Table 59. List of Forecasting Functions

Function

Data Manipulation

@MOVAVG

Apply a moving average to a data set and replace each term in the list with a trailing average.

This function modifies the data set for smoothing purposes.

@MOVMAX

Apply a moving maximum to a data set and replace each term in the list with a trailing maximum.

This function modifies the data set for smoothing purposes.

@MOVMED

Apply a moving median to a data set and replace each term in the list with a trailing median.

This function modifies the data set for smoothing purposes.

@MOVMIN

Apply a moving minimum to a data set and replace each term in the list with a trailing minimum.

This function modifies the data set for smoothing purposes.

@MOVSUM

Apply a moving sum to a data set and replace each term with a trailing sum.

This function modifies the data set for smoothing purposes.

@MOVSUMX

Apply a moving sum to a data set and replace each term with a trailing sum. Specify how to assign values to members before you reach the number to sum.

This function modifies the data set for smoothing purposes.

@SPLINE

Apply a smoothing spline to a set of data points.

A spline is a mathematical curve that is used to smooth or interpolate data.

@TREND

Calculate future values and base the calculation on curve-fitting to historical values.

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

Statistical Functions

Statistical functions allow you to calculate advanced statistics in Essbase.

Table 60. List of Statistical Functions

Function

Calculated Value

@CORRELATION

The correlation coefficient between two parallel data sets

@COUNT

The number of values in the specified data set

@MEDIAN

The median, or middle number, in the specified data set

@MODE

The mode, or the most frequently occurring value, in the specified data set

@RANK

The rank of the specified member or value in the specified data set

@STDEV

The standard deviation, based upon a sample, of the specified members

@STDEVP

The standard deviation, based upon the entire population, of the specified members

@STDEVRANGE

The standard deviation, crossed with a range of members, of the specified members

@VARIANCE

The variance, based upon a sample, of the specified data set

@VARIANCEP

The variance, based upon the entire population, of the specified data set

Date and Time Function

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

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

Calculation Mode Function

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

Note:

You can also use the configuration setting CALCMODE to set calculation modes to BLOCK or BOTTOMUP at the database, application, or server level. See the Oracle Essbase Technical Reference.

Custom-Defined Functions

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.

Using Substitution and Environment Variables in Formulas

Substitution variables are used to reference information that changes frequently; environment variables are used as placeholders for user-specific system settings. See:

Using Substitution Variables in Formulas

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)

Note:

Substitution variables used in formulas for new outline members do not pass verification unless the outline is saved.

Using Environment Variables in Formulas

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.

Note:

Environment variables cannot be used in MDX queries or in member formulas that are within aggregate storage outlines.

Using Formulas on Partitions

A partitioned application can span multiple Essbase Servers, processors, or computers.

You can use formulas in partitioning, just as you use formulas on your local database. If, however, a formula you use in one database references a value from another database, Essbase must retrieve the data from the other database when calculating the formula; therefore, ensure that the referenced values are up-to-date and carefully consider the performance impact on the overall database calculation. See Writing Calculation Scripts for Partitions.

With transparent partitions, carefully consider how you use formulas on the data target. See Transparent Partitions and Member Formulas and Performance Considerations for Transparent Partitions.

See Designing Partitioned Applications and Creating and Maintaining Partitions.

Displaying Formulas

  To display a formula, use a tool:

Tool

Topic

Location

Administration Services

Creating and Editing Formulas in Outlines

Oracle Essbase Administration Services Online Help

ESSCMD

GETMBRCALC

Oracle Essbase Technical Reference

MaxL

query database

Oracle Essbase Technical Reference