Developing Formulas

This chapter explains how to develop and use formulas to calculate a database. It provides detailed examples of formulas, which you may want to adapt for your own use. For more examples, see Examples of Formulas.

This chapter includes the following sections:

Your use of formulas can have significant implications for calculation performance. After reading this section, use the information in Optimizing Calculations to design and create formulas optimized for performance.

For information on using formulas with Hybrid Analysis, see Using Formulas with Hybrid Analysis in Accessing Relational Data with Hybrid Analysis.

Understanding Formulas

Formulas calculate relationships between members in a database outline. You can use formulas in two ways:

The following figure 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 370: Calculation of Margin %, Profit %, and Profit per Ounce

Essbase provides a comprehensive set of operators and functions, which you can use to construct formula calculations on a database. The rest of this section provides a description of the elements you can place in a formula, and provides basic information about formula calculation and syntax:

Operators

The following table shows the types of operators you can use in formulas:

Table 34: Descriptions of Operator Types

Operator Type
Description

Mathematical

Perform common arithmetic operations. For example, you can add, subtract, multiply, or divide values. For a complete list of the mathematical operators, see the Technical Reference in the docs directory.

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. For a list of the conditional operators, see the Technical Reference in the docs directory. For more information on writing conditional formulas, see Specifying Conditions.

Cross-dimensional

Point to the data values of specific member combinations. For example, point to the sales value for a specific product in a specific region. For more information, see Using the Cross-Dimensional Operator ( -> ).



See Inserting Text and Operators in a Formula for information on how to add operators to formulas.

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

Functions

Functions are predefined routines that perform specialized calculations and return sets of members or data values. The following table shows the types of functions you can use in formulas:

Table 35: Descriptions of Function Types  

Function Type
Description

Boolean

Provide a conditional test by returning either a TRUE (1) or FALSE (0) value. For example, you can use the @ISMBR function to determine whether the current member is one that you specify. For more information, see Examples of Formulas.

Mathematical

Perform specialized mathematical calculations. For example, you can use the @AVG function to return the average value of a list of members. For more information, see Examples of Formulas.

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. For more information, see Examples of Formulas.

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 that lie within a specified range. For more information, see Examples of Formulas.

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. For more information, see Examples of Formulas.

Member Set

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. For more information, see the Technical Reference in the docs directory.

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 parent's children; each child's allocation is determined by its share of the previous year's sales.

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.

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.

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.

Miscellaneous

This type provides two different kinds of functionality:

  • You can specify calculation modes that Essbase is to use to calculate a formula: cell, block, bottom-up, and top-down
  • You can manipulate character strings for member and dimension names; for example, to 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.

Custom-Defined Functions

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



For a complete list of operators, functions, and syntax, see the Technical Reference in the docs directory.

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

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:

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 FIX as described in Improving Performance for Constants in a Sparse Dimension.

Non-Constant Values

When you assign a constant to a member in a sparse dimension, you do not need to enable Create Blocks on Equations. However, if you assign anything other than a constant to a member in a sparse dimension, and no data block exists for that member, you still need to enable Create Blocks on Equations.

For example, you need to enable Create Blocks on Equations for this formula:

West = California + 120; 

To enable Create Blocks on Equations, use this procedure:

  1. In Application Manager, select Database > Settings.
  2. Essbase displays the Database Settings dialog box.

  3. Check Create Blocks on Equations.
  4. Click OK.

Tip: You can enable Create Blocks on Equations without Application Manager:

Tool
Instructions
For More Information

Administration Services

Database Properties window > General tab

Essbase Administration Services Online Help

MaxL

alter database

Technical Reference in the docs directory

ESSCMD

SETDBSTATE



Understanding Formula Calculation

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

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. For more information, 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 has to interrupt the regular calculation to perform the dynamic calculation.

You cannot use substitution variables in formulas that you apply to the database outline. For more information, see Using Substitution Variables.

Understanding Formula Syntax

When you create member formulas, make sure the formulas follow these rules:

When writing formulas, you can check the syntax using the Formula Editor syntax checker. For more information, see Writing Formulas.

For detailed information on syntax for Essbase functions and commands, see the Technical Reference in the docs directory.

Example: Creating a Simple Formula

This section provides a step-by-step example of creating and saving a simple formula in an outline using the Formula Editor. For an example of creating a formula in a calculation script, see Developing Calculation Scripts. For detailed information on creating formulas, and obtaining the required calculation results, consider all the information in Calculating Data.

This example is based on the Sample Basic database, which is supplied with the Essbase installation. If you do not have Sample Basic installed, contact your Essbase administrator.

This example shows you how to create a formula on the Variance member of the Scenario dimension. This formula calculates the variance between Budget values and Actual values.

To create the example formula:

  1. Start Application Manager, and connect to the OLAP Server.
  2. Select the Sample application and the Basic database, and click Open to open the Sample Basic outline.
  3. Figure 371: Application Desktop Window

    If another user has Sample Basic open and locked, you can clear "Lock file" in the bottom right-hand corner of the application desktop window. However, if you clear "Lock file," you cannot save your work.

  4. Double-click the Scenario dimension to display its members.
  5. Figure 372: Scenario Dimension in Sample Basic Outline

  6. Select the Variance member in the outline, and click the button.
  7. Essbase displays the formula in Formula Editor.

    Figure 373: Formula Editor Showing Variance Formula

  8. To re-create the formula, select the existing formula and select Edit > Delete in Formula Editor.
  9. Figure 374: Formula Editor With Variance Formula Deleted

  10. In the Dimensions list, select Scenario.
  11. Essbase displays Scenario in the Members list.

    Figure 375: Formula Editor Dimensions and Members Lists With Scenario Selected

  12. In the Members list, double-click the button next to Scenario to display the members under Scenario.
  13. Select Formula > Paste Function or click the button.
  14. Essbase displays the Function Templates dialog box.

  15. In the Categories list, select Math.
  16. In the Templates list, select @VAR.
  17. Essbase displays the function and the default arguments below the Categories list.

    Figure 376: Function Templates Dialog Box

  18. Check Insert Arguments to insert default, temporary arguments in Formula Editor.
  19. Click OK.
  20. Essbase inserts @VAR (mbrName1, mbrName2) at the cursor position.

    Figure 377: Formula Editor With Variance Formula Added

  21. Click the button, or type a ; (semicolon) to insert the semicolon formula end-of-line character.
  22. Click the button to save the formula.
  23. Close Formula Editor.
  24. Click the button to save the changes to the outline.

You have recreated the formula on Variance in Sample Basic.

Building Formulas in Formula Editor

You use Formula Editor in Application Manager to create formulas. You can type the formulas directly into the formula text area, or you can use the Formula Editor user interface features to create the formula.

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

This section explains how to perform basic formula creation tasks using the Formula Editor:

Note: You can also check syntax in some situations with the Formula Editor. For instructions, see Checking Syntax on the Client.

Opening Formula Editor

Open Formula Editor to create new formulas or open existing ones.

To open Formula Editor from Application Manager:

  1. Open the Application Manager and connect to the OLAP Server.
  2. Select the application and database.
  3. Click the Outline button to display outlines available for the application and database.
  4. Double-click the outline to open it in the Outline Editor.
  5. In Outline Editor, highlight the member whose formula you want to create or edit.
  6. Select Edit > Formula or click the Formula Editor button, .
  7. Essbase opens Formula Editor for the selected member. If the member already has a formula, the formula is displayed in Formula Editor. The following figure shows Formula Editor for the Variance member in the Sample Basic database.

    Figure 378: Formula Editor Window

Displaying a Formula

Open the database outline to display members and their associated formulas in Outline Editor. You can also highlight the member for which you want to see a formula and click the button to open Formula Editor.

You can also use the GETMBRCALC command in ESSCMD to display member formulas. See the Technical Reference in the docs directory for information about this command. See Automating the Production Environment for information about ESSCMD.

Adding a Formula

You can use Application Manager to add a formula to a database outline.

To add a formula:

  1. Open the Application Manager and connect to the OLAP Server.
  2. Select the application and database.
  3. Click the Outline button to display outlines available for the application and database.
  4. Double-click the outline to open it in the Outline Editor.
  5. Select the member for which you want to add a formula.
  6. Click the button to open Formula Editor.
  7. Type or insert the formula in the Formula Editor window. See Inserting Text and Operators in a Formula.

Changing a Formula

To change an existing formula, open it in Formula Editor.

To change a formula:

  1. Open the Application Manager and connect to the OLAP Server.
  2. Select the application and database.
  3. Click the Outline button to display outlines available for the application and database.
  4. Double-click the outline to open it in the Outline Editor.
  5. Select the member that has the formula you want to edit.
  6. Click the button to open Formula Editor.
  7. Make the required changes to the formula.

Saving a Formula

You can save formulas to the database outline.

To save a formula after you have created or opened it:

  1. Open the Application Manager and connect to the OLAP Server.
  2. Select the application and database.
  3. Click the Outline button to display outlines available for the application and database.
  4. Double-click the outline to open it in the Outline Editor.
  5. Select a member and click the Formula Editor button to open or create a formula for that member.
  6. In Formula Editor, select File > Save or click the button to save the changes in Formula Editor.
  7. Close Formula Editor.
  8. Click the button in Outline Editor to save the changes in the database outline.
  9. Essbase displays the formula beside the member in the database outline.

Printing a Formula

You can print the contents of a formula from Formula Editor.

To print a formula, in Formula Editor, select File > Print, or click the button.

Deleting a Formula

You can delete a formula that has been saved to the database outline.

To delete a formula:

  1. In Outline Editor, select the member with the formula that you want to delete.
  2. To open Formula Editor, click the button.
  3. Essbase displays the formula in the Formula Editor window.

  4. Select a member and click the Formula Editor button to open or create a formula for that member.
  5. Select the text of the formula.
  6. Select Edit > Delete to delete the text of the formula.
  7. Click the button to save the changes in Formula Editor.
  8. Close Formula Editor and click the button to save the changes in the database outline.
  9. Essbase no longer displays the formula beside the member in the database outline.

To undo the last action, in Formula Editor, select Edit > Undo, or click the button.

Inserting Text and Operators in a Formula

You can type text and operators directly into the Formula Editor text area, or you can use the toolbar buttons to add the text and operators. You can also copy, cut, and search for text in Formula Editor.

To type text in Formula Editor:

  1. Open the Application Manager and connect to the OLAP Server.
  2. Select the application and database.
  3. Click the Outline button to display outlines available for the application and database.
  4. Double-click the outline to open it in the Outline Editor.
  5. In Formula Editor, click in the formula text area below the toolbar.
  6. Type the appropriate text. You are limited to less than 64 Kb.
  7. Text is displayed at the cursor position as you type.

    Figure 379: Adding a Formula in Formula Editor

To insert an equal sign (=) in a formula:

  1. In Formula Editor, place the cursor where you want to insert the equal sign (=).
  2. Type = or click the button.

To insert a mathematical operator (+, -, X, /, %) in a formula:

  1. In Formula Editor, place the cursor where you want to insert the mathematical operator.
  2. Type the operator or click one of the following toolbar buttons:

To insert the cross-dimensional operator ( -> ) in a formula:

  1. In Formula Editor, place the cursor where you want to insert the cross-dimensional operator.
  2. Type a - (hyphen) followed by a > (greater than symbol), or click the button.

For more information on the cross-dimensional operator, see Using the Cross-Dimensional Operator ( -> ).

To insert the semicolon formula end-of-line character (;) in a formula:

  1. In Formula Editor, place the cursor at the end of the formula.
  2. Type a ; (semicolon) or click the button.

To insert a function or operator in a formula:

  1. In Formula Editor, place the cursor where you want to insert the function.
  2. Select Formula > Paste Function, or click the button.
  3. Essbase displays the Function Templates dialog box.

  4. In the Categories list, select a function category. For example, to insert the @VAR function, select Math.
  5. In the Templates list, select the required function or operator. For example, scroll down the list and select @VAR.
  6. Essbase displays the function or operator and the default arguments below the Categories list.

    Figure 380: Function Templates Dialog Box With Math Category Selected

  7. If required, select Insert Arguments to insert default, temporary arguments in the function.
  8. Click OK.
  9. Essbase inserts @VAR ( ) at the cursor position.

    Figure 381: Formula Editor Showing @VAR Formula

    If you checked Insert Arguments, Essbase inserts @VAR and default, temporary arguments. You can then type over the default arguments with the correct arguments.

    Figure 382: Formula Editor Showing @VAR with Arguments

To cut text in Formula Editor:

Select the text that you want to cut and do one of the following:

To copy text in Formula Editor:

Select the text that you want to copy and do one of the following:

To paste text in Formula Editor:

Select the text that you want to paste and do one of the following:

To find and replace text in Formula Editor:

  1. In Formula Editor, select Edit > Find.
  2. Essbase displays the Find dialog box.
  3. Figure 383: Formula Editor Find Dialog Box

  4. In the Find what text box, type the characters that you want to search for.
  5. Click the Find Next button.

To do a case-sensitive search in Formula Editor:

  1. In the Find dialog box, select Match case.
  2. For example, to search for Margin but not "margin," type Margin in the Find what text box, and select Match case.

  3. Click Find Next.

Inserting Members in a Formula

You can insert dimension and member names in Formula Editor instead of typing them.

To insert a dimension name in a formula:

  1. In Formula Editor, place the cursor where you want to insert the dimension name.
  2. In the Dimensions list, select the dimension that you want to insert in the formula.
  3. The dimension name displays in the Members list. If a button displays to the left of the dimension name, then the dimension has children. The following shows the Scenario dimension in the Sample Basic database.

    Figure 384: Formula Editor Dimensions and Members Lists

  4. To insert a dimension name in the formula, click the dimension name in the Members list.
  5. Essbase inserts the dimension name at the cursor position. To insert a member name (a member name other than the dimension name), expand the member branch and select the member you want to insert.

To expand a member branch to display a member's children:

In the Members list, double-click the button next to the member name to display the member's children.

The button changes to a button.

Figure 385: Formula Editor Dimensions and Members Lists, Expanding the Scenario Member

Double-click the button to collapse the member branch.

To collapse a member branch:

In the Members list, double-click the button to collapse the member branch.

Figure 386: Formula Editor Dimensions and Members Lists, Expanded Scenario Dimension

The button changes to a button. Essbase does not display the member's children:

Figure 387: Formula Editor Dimensions and Members Lists, Collapsing the Scenario Dimension

Searching for Members

To search for a specific member in Formula Editor:

  1. In the Dimensions list, select the dimension that you want to search for a member.
  2. For example, select the Measures dimension from the Sample Basic database.

  3. Click Find Member.
  4. Essbase displays the Find dialog box.

    Figure 388: Formula Editor Find Dialog Box

  5. In the Find what text box, enter the characters that you want to search for.
  6. For example, to search for the Marketing member in the Measures dimension, type market.

  7. Click Find Next.
  8. Essbase finds and selects the Marketing member.

    Figure 389: Formula Editor Members List With Marketing Selected

To expand a dimension to display all members in Formula Editor:

  1. In the Dimensions list, select the dimension for which you want to display all members.
  2. For example, select the Product dimension in the Sample Basic database.

    Figure 390: Formula Editor Dimensions and Members List
    With Product Selected

  3. Click Expand All.
  4. In the Members list, Essbase displays all members in the dimension.

    Figure 391: Formula Editor Dimensions and Members List
    Showing the Children of Product

To display and insert alias names in Formula Editor, check Use Aliases.

Essbase displays the alias names for the members. The following example shows the Product dimension from the Sample Basic database.

Figure 392: Formula Editor Dimensions and Members List with Alias Names

To select a different alias table, from the Alias Table list box, select a table.

When you select a member from the Members list, Essbase inserts the alias name at the cursor position. If required, Essbase automatically encloses the alias name in double quotation marks (" ").

Writing Formulas

The following sections discuss and give examples of the main types of formulas:

For more examples of formulas, see Examples of Formulas.

Before writing formulas, review the guidelines in Understanding Formula Syntax.

Writing Basic Equations

You can apply a mathematical operation to a formula to create a basic equation. For example, you can apply the following formula to the Margin member in Sample Basic.

Sales - COGS; 

In a calculation script, you define basic equations as follows:

Member = mathematical operation;

where Member is a member name from the database outline and mathematical operation is any valid mathematical operation. For example:

Margin = Sales - COGS; 

Whether the example equation is in the database outline or in a calculation script, Essbase cycles through the database subtracting the values in COGS from the values in Sales and placing the results in Margin.

As another example, you can apply the following formula to a Markup member:

(Retail - Cost) % Retail; 

In a calculation script, this would be:

Markup = (Retail - Cost) % Retail; 

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 result in Markup.

For more information on the nature of multidimensional calculations, see Introduction to Database Calculations.

Specifying Conditions

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). You can use the ELSE and ELSEIF commands to specify alternative actions if the test returns FALSE (0). 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).

For more information on the syntax of the IF and ENDIF commands, see Understanding Formula Syntax.

When you use a conditional formula in a calculation script, you must 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.

You use Boolean functions to determine which formula to use. The decision is based on the characteristics of the current member combination. For example, you might want to restrict a certain calculation to the members in the Product dimension that contain input data. In this case, you 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).

You can use the following Boolean functions to specify conditions.

Information You Need To Find
Use This Function

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

@ISACCTYPE

The current member is an ancestor of the specified member

@ISANCEST

The current member is an ancestor of the specified member, or the specified member itself

@ISIANCEST

The current member is a child of the specified member

@ISCHILD

The current member is a child of the specified member, or the specified member itself

@ISICHILD

The current member is a descendant of the specified member

@ISDESC

The current member is a descendant of the specified member, or the specified member itself

@ISIDESC

The current member of the specified dimension is in the generation specified

@ISGEN

The current member of the specified dimension is in the level specified

@ISLEV

The current member matches any of the specified members

@ISMBR

The current member is the parent of the specified member

@ISPARENT

The current member is the parent of the specified member, or the specified member itself

@ISIPARENT

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

@ISSAMEGEN

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

@ISSAMELEV

The current member is a sibling of the specified member

@ISSIBLING

The current member is a sibling of the specified member, or the specified member itself

@ISISIBLING

A specified UDA (user-defined attribute) exists for the current member of the specified dimension

@ISUDA



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 more information on calculation scripts, see Developing Calculation Scripts. For more information on Essbase functions and calculation commands, see the Technical Reference in the docs directory.

Examples of Specifying Conditions

You can apply the following formula to a Commission member in the database outline. In the first 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 need to associate the formula with the Commission member as follows:

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 need to associate the formula with the Payroll member as follows:

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 if 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 if 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 on 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 on to the next member combination.

For more information on the nature of multidimensional calculations, see Introduction to Database Calculations. For more information on the @ISIDESC function, see the Technical Reference in the docs directory.

Value-Related Formulas

Use this section to find information about formulas related to values:

Using Interdependent Values

Essbase optimizes calculation performance by calculating formulas for a range of members in the same dimension at the same time. However, some formulas 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.

In Sample Basic, the Opening Inventory and Ending Inventory values need to be calculated on a month-by-month basis.

.
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 required calculation is:

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 a single 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 need to associate the formula with the Opening Inventory member as follows:

"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 previous month's Ending Inventory. This value is then allocated to the current month's Opening Inventory.
  3. The Ending Inventory is calculated for the current month.

Note: To calculate the correct results, it is necessary to place the above formula on a single 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 means that the value of the previous month's Ending Inventory is not available when Opening Inventory is calculated.

Calculating a Variance or Percentage Variance 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:

By default, Essbase assumes that members are non-expense items and calculates the variance accordingly.

To tell Essbase that a member is an expense item, use this procedure:

  1. In Outline Editor, select the member. The member must be on the dimension tagged as accounts. See Calculating Time Series Data.
  2. Click the button.
  3. Essbase tags the member as an expense item. When you use the @VAR or @VARPER functions, Essbase shows a positive variance if the actual values are lower 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.

    Figure 393: Sample Basic Showing Expense Items

Allocating Values

You can allocate values that are input at the parent level across child members in the same dimension or in different dimensions by using the following allocation functions.

Allocated Values
Function To Use

Values from a member, cross-dimensional member, or value across a member list within the same dimension. The allocation is based on a variety of specified criteria.

@ALLOCATE

Values from a member, cross-dimensional member, or value across multiple dimensions. The allocation is based on a variety of specified criteria.

@MDALLOCATE



Note: For examples of calculation scripts using the @ALLOCATE and @MDALLOCATE functions, see Allocating Values Within or Across Dimensions and the Technical Reference in the docs directory.

Forecasting Values

You can manipulate data for the purposes of smoothing data, interpolating data, or calculating future values by using the following forecasting functions.

Data Manipulation
Function To Use

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

@MOVAVG

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

@MOVMAX

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

@MOVMED

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

@MOVMIN

To apply a smoothing spline to a set of data points. A spline is a mathematical curve that is used to smooth or interpolate data.

@SPLINE

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

@TREND



For more information on Essbase functions, see the Technical Reference in the docs directory.

Using the Member Relationships to Look Up Values

You can use the member combination that Essbase is currently calculating to look up specific values. These functions are referred to as relationship functions.

Look-up Value
Function To Use

The ancestor values of the specified member combination

@ANCESTVAL

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

@ATTRIBUTEVAL

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

@ATTRIBUTESVAL

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

@ATTRIBUTEBVAL

The generation number of the current member combination for the specified dimension

@CURGEN

The level number of the current member combination for the specified dimension

@CURLEV

The generation number of the specified member

@GEN

The level number of the specified member

@LEV

The ancestor values of the specified member combination across multiple dimensions

@MDANCESTVAL

The shared ancestor values of the specified member combination

@SANCESTVAL

The parent values of the specified member combination

@PARENTVAL

The parent values of the specified member combination across multiple dimensions

@MDPARENTVAL

The shared parent values of the specified member combination

@SPARENTVAL

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

@XREF



For more information on Essbase functions, see the Technical Reference in the docs directory.

Using Substitution Variables

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

When you run a calculation script, Essbase replaces the substitution variable with the value you have assigned to it. You can create and assign values to substitution variables using Application Manager 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 script.

For more information on creating and assigning values to substitution variables, see Creating Applications and Databases.

To use a substitution variable in a calculation script:

Type 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 in a calculation script:

@ISMBR(&UpToCurr) 

Before Essbase runs the calculation script, it replaces the substitution variable, as follows:

@ISMBR(Jan:Jun) 

Member-Related Formulas

This section provides information you need to create formulas that refer to members:

Specifying a Member List or Range

In some functions you may need to specify more than one member, 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. You can specify members using the following syntax:

Member List or Range
Syntax

A single 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

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

You can generate member lists that are based on a specified member by using the these member set functions.

Contents of Member List
Function

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

@ALLANCESTORS

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

@IALLANCESTORS

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

@ANCEST

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

@ANCESTORS

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

@IANCESTORS

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

@CHILDREN

All children of the specified member, including the specified member.

@ICHILDREN

The current member being calculated for the specified dimension.

@CURRMBR

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

@DESCENDANTS

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

@IDESCENDANTS

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

@RDESCENDANTS

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

@IRDESCENDANTS

All members of the specified generation in the specified dimension.

@GENMBRS

All members of the specified level in the specified dimension.

@LEVMBRS

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

@SIBLINGS

All siblings of the specified member, including the specified member.

@ISIBLINGS

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

@LSIBLINGS

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

@RSIBLINGS

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

@ILSIBLINGS

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

@IRSIBLINGS

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

@LIST

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

@MEMBER

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

@MERGE

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

@RANGE

A list of members from which some members have been removed.

@REMOVE

All members that match the specified wildcard selection.

@MATCH

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

@PARENT

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

@RELATIVE

All members that have a common (UDA) user-defined attribute defined on OLAP Server.

@UDA

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

@ATTRIBUTE

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

@WITHATTR



For more information on Essbase functions, see the Technical Reference in the docs directory.

Manipulating Member Names

You can work with member names as character strings by using the following functions:
Character String Manipulation
Function To Use

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

@CONCATENATE

To return a member name as a string

@NAME

To return a substring of characters from another character string or from a member name

@SUBSTRING



Using the Cross-Dimensional Operator ( -> )

The cross-dimensional operator points to data values of specific member combinations.

You create the cross-dimensional operator using a hyphen (-) and a greater than symbol (>). Do not leave spaces in between the cross-dimensional operator and the member names.

For example, in this simplified illustration, the shaded data value is Sales -> Jan -> Actual.

Figure 394: Defining a Single Data Value by Using the
Cross-Dimensional Operator

The following example illustrates how to use the cross-dimensional operator. This example allocates miscellaneous expenses to each product in each market.

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. Essbase divides the Sales value for the current member combination by the total Sales value for all markets and all products (Sales -> Market -> Product).
  2. It multiplies the value calculated in step 1 by the Misc_Expenses value for all markets and all products (Misc_Expenses -> Market -> Product).
  3. It allocates the result to Misc_Expenses for the current member combination.

Consider carefully how you use the cross-dimensional operator, as it can have significant performance implications. For detailed information, see Optimizing Calculations.

In Equations in a Dense Dimension

When you use a cross-dimensional operator in an equation in a dense dimension, if the resultant values are from a dense dimension and the operand or operands are from a sparse dimension, Essbase does not automatically create the required blocks.

Consider an example from Sample Basic, in which you want to create budget sales and expense data from existing actual data. Sales and Expenses are members in the dense Measures dimension. Budget and Actual are members in the sparse Scenario dimension.

FIX(Budget) 
      (Sales = Sales -> Actual * 1.1; 
       Expenses = Expenses -> Actual * .95;) 
ENDFIX 

Note that results of the equation, Sales and Expenses, are dense dimension members, and the operand, Actual, is in a sparse dimension. The calculation script above does not create the required data blocks, therefore Budget data values cannot be calculated for blocks that do not already exist.

You can solve this problem using either of these techniques:

Using DATACOPY for Equations in a Dense Dimension

You can solve the problem in In Equations in a Dense Dimension by preceding the above formulas with a DATACOPY command:

DATACOPY Sales -> Actual TO Sales -> Budget;
DATACOPY Expenses -> Actual TO Expenses -> Budget;
FIX(Budget)
      (Sales = Sales -> Actual * 1.1;
       Expenses = Expenses -> Actual *  .95;)
ENDFIX 

With DATACOPY, Essbase copies the data and creates the required blocks, in this example, blocks that contain the Budget values for each corresponding Actual block that already exists.

Avoiding Equations in a Dense Dimension

You can solve the problem in In Equations in a Dense Dimension and also avoid copying the data as described in Using DATACOPY for Equations in a Dense Dimension by ensuring that the results members are from a sparse dimension, not from a dense dimension. In this example, Budget is the results member, and it is from a sparse dimension:

FIX(Sales)
      Budget = Actual * 1.1;
ENDFIX
FIX(Expenses)
      Budget = Actual *  .95;
ENDFIX 

You can also use a member formula that contains the dense member equations:

FIX(Sales, Expenses)
Budget (Sales = Sales -> Actual * 1.1;
      Expenses = Expenses -> Actual * .95;)
ENDFIX 

Formulas with Other Function Types

Use this section to find information about formulas that use other types of formulas:

Performing Mathematical Operations

You can perform many mathematical operations in formulas by using the following mathematical functions.

Operation
Function

To return the absolute value of an expression

@ABS

To return the average value of the values in the specified member list

@AVG

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

@EXP

To return the factorial of an expression

@FACTORIAL

To return the next lowest integer value of a member or expression

@INT

To return the natural logarithm of a specified expression

@LN

To return the logarithm to a specified base of a specified expression

@LOG

To return the base-10 logarithm of a specified expression

@LOG10

To return the maximum value among the expressions in the specified member list

@MAX

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

@MAXS

To return the minimum value among the expressions in the specified member list

@MIN

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

@MINS

To return the modulus produced by the division of two specified members

@MOD

To return the value of the specified member raised to the specified power

@POWER

To return the remainder value of an expression

@REMAINDER

To return the member or expression rounded to the specified number of decimal places

@ROUND

To return the summation of values of all specified members

@SUM

To return the truncated value of an expression

@TRUNCATE

To return the variance (difference) between two specified members. See Calculating a Variance or Percentage Variance Between Actual and Budget Values.

@VAR

To return the percentage variance (difference) between two specified members. See Calculating a Variance or Percentage Variance Between Actual and Budget Values.

@VARPER



For more information on Essbase functions, see the Technical Reference in the docs directory.

Statistical Functions

You can use these statistical functions to calculate advanced statistics in Essbase.

Calculated Value
Function To Use

The correlation coefficient between two parallel data sets

@CORRELATION

The number of values in the specified data set

@COUNT

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

@MEDIAN

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

@MODE

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

@RANK

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

@STDEV

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

@STDEVP

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

@STDEVRANGE

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

@VARIANCE

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

@VARIANCEP



For more information on Essbase functions, see the Technical Reference in the docs directory.

Using Range Functions

You can execute a function for a range of members by using these range functions.

Calculation
Function To Use

The average value of a member across a range of members

@AVGRANGE

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

@CURRMBRRANGE

The maximum value of a member across a range of members

@MAXRANGE

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

@MAXSRANGE

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

@MDSHIFT

The minimum value of a member across a range of members

@MINRANGE

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

@MINSRANGE

The next or n th member in a range of members.

@NEXT

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

@NEXTS

The previous or n th previous member in a range of members

@PRIOR

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

@PRIORS

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

@SHIFT. In some cases, @SHIFTPLUS or @SHIFTMINUS.

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

@SUMRANGE



For more information on Essbase functions, see the Technical Reference in the docs directory.

Calculating Financial Functions

You can include financial calculations in formulas by using these financial functions.

Calculation
Function To Use

An accumulation of values up to the specified member

@ACCUM

The proceeds of a compound interest calculation

@COMPOUND

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

@COMPOUNDGROWTH

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

@DECLINE

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

@DISCOUNT

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

@GROWTH

The simple interest for a specified member at a specified rate

@INTEREST

The internal rate of return on a cash flow

@IRR

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

@NPV

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

@PTD

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.

@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 sum of the year's digits.

@SYD



For more information on Essbase functions, see the Technical Reference in the docs directory.

Using Date and Time Functions

You can use dates with other functions by using this date function.

Date Conversion
Function To Use

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

@TODATE



Using Calculation Mode Functions

You can specify which calculation mode that Essbase uses to calculate a formula by using @CALCMODE.

Specification
Function To Use

To specify that Essbase uses cell, block, bottom-up, and top-down calculation modes to calculate a formula.

@CALCMODE



Note: You can also use the configuration setting CALCMODE to set calculation modes to BLOCK or BOTTOMUP at the database, application, or server level. For details, see the Technical Reference in the docs directory, under "essbase.cfg Settings" for CALCMODE or "Essbase Functions" for @CALCMODE.

Using Custom-Defined Functions

Custom-defined functions are calculation functions that you create to perform calculations not otherwise supported by the Essbase calculation scripting language. You can use custom-defined functions in formulas and calculation scripts. These custom-developed functions are written in the Java programming language and registered on the server. The Essbase calculator framework calls them as external functions.

If you are connected to the server, the Custom-Defined Functions category appears in the Function Templates dialog box where you can choose function names to be inserted into the formula.

For more information about Custom-Defined Functions, see Developing Custom-Defined Calculation Functions.

Checking Syntax

Essbase includes both client-based and 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. If you are connected to a server, 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.

Because server-based formula validation has access to more information about the database and outline, this form of validation can take more time to complete. For quicker syntax checking, you can use client-based formula validation to find syntax-related errors. To avoid saving outlines that contain formulas with errors, perform a server-based formula validation and correct all errors before the outline goes into production.

Checking Syntax on the Client

Use the client-based syntax checker to validate formulas and calculation scripts. You can use this feature whether or not you are connected to the server. The client-based syntax checker identifies each error within a formula.

To use the client-based syntax checker to validate a formula in Formula Editor:

Select Syntax > Check Syntax, or click the button.

Essbase displays the syntax checker results at the bottom of the Formula Editor window. If Essbase finds no syntax errors, it displays the message shown in Figure 395.

Figure 395: Formula Editor Syntax Checker, 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 message shown in Figure 396.

Figure 396: Formula Editor Syntax Checker, Syntax Error Message

To step through errors in Formula Editor:

Select Syntax > Next Error or Syntax > Previous Error.

When you reach the first or last error, Essbase displays the message shown in Figure 397.

Figure 397: Formula Editor Syntax Checker, No More Errors Message

Essbase retains the list of error messages in Formula Editor until you check the syntax again.

Checking Syntax on the Server

You can check the syntax on the server in two ways:

Essbase displays outline errors and warnings in the Verify Outline dialog box, similar to Figure 398.

Figure 398: Example Verify Outline Dialog Box Containing Formula Errors

Select a member name to see associated errors and warnings. For more information about the error or warning, if the error or warning is in the formula attached to the member, click Find to go to that member in the outline. Open Formula Editor, and use the server-based syntax checker to validate the formula.

If Essbase finds no syntax errors, it displays the No Errors message at the bottom of the Formula Editor window, as shown in Figure 399.

Figure 399: Formula Editor Syntax Checker, No Errors Message

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

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.

Estimating Disk Size for a Calculation

You can estimate the disk size that would be required for a single CALC ALL given either a full data load or a partial data load. For details, see the Technical Reference in the docs directory of your Essbase installation, in the ESSCMD section's entry for ESTIMATEFULLDBSIZE and Estimating Calculations.

Working with Formulas in Partitions

An Essbase partition can span multiple servers, processors, or computers. For more information on partitioning, see Designing Partitioned Applications, and Building and Maintaining Partitions.

You can use formulas in partitioning, just as you use formulas on your local database. However, if a formula you use in one database references a value from another database, Essbase has to retrieve the data from the other database when calculating the formula. In this case, you need to ensure that the referenced values are up-to-date and to consider carefully the performance impact on the overall database calculation. For more information, see the information on writing calculation scripts for partitions in Writing Calculation Scripts for Partitions.

With transparent partitions, you need to consider carefully how you use formulas on the data target. For more information, see Transparent Partitions and Member Formulas and Performance Considerations for Transparent Partitions.




© 2002 Hyperion Solutions Corporation. All rights reserved.
http://www.hyperion.com