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:

• Using Formulas
• Creating Formulas
• Building Formulas in Formula Editor
• Writing Formulas
• Working with Formulas in Partitions

Using Formulas

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

• Apply them to members in the database outline.
• Place them in a calc script.

In most cases you can optimize calculation performance by applying formulas to members in the database outline. However, if you need to control database calculations more carefully, you can use a calc script and include formulas. For more information, see Developing Calc Scripts.

Your use of formulas can have significant implications for calculation performance. Consider the information in Optimizing Calculations before designing and creating formulas.

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 26-1: Calculation of Margin %, Profit %, and Profit per Ounce

Hyperion Essbase provides a comprehensive set of operators and functions, which you can use to construct formula calculations on a database.

You can construct formulas from:

• Operators
• Functions
• Dimension names, member names, and numeric constants

Operators

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

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

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:

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

Calculating Formulas

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

• Run a default (CALC ALL) calculation of a database.
• Run a calc script that calculates the member containing the formula; for example, a CALC DIM of the dimension containing the member, or the member itself. For more information, see Developing Calc Scripts.

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

If a formula is associated with a dynamically-calculated member, Hyperion Essbase calculates the formula when the user requests the data values. In a calc 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 calc script can significantly affect calculation performance. Performance is affected because Hyperion 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.

Guidelines for Formula Syntax

When you create member formulas, you need to apply the following rules:

• End each statement in the formula with a semicolon (;), for example,

  Margin % Sales;
  

• Enclose a member name in double quotation marks ("") if the member name meets any of the following conditions:
  • Contains spaces; for example,

    "Opening Inventory" = "Ending Inventory" - Sales + Additions;
    

  • Is the same as an operator or function name. See the online Technical Reference in the DOCS directory for a list of operators and functions.
  • Includes any non-alphanumeric character; for example, hyphens (-), asterisks (*), and slashes (/).
  • Is all numeric or starts with one or more numerals; for example, "100" or "10Prod"

  For a complete list of member names that must be enclosed in quotation marks, see Rules for Naming Dimensions and Members.

• 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 in between) rather than ELSEIF (one word) in a formula, you must supply an ENDIF for the IF statement.

• Although ending ENDIF statements with a semicolon (;) is not required, it is good practice to follow each ENDIF statement in a formula with a semicolon.

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

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

Creating Formulas

This section provides a step-by-step example of creating and saving a simple formula in an outline. For an example of creating a formula in a calc script, see Developing Calc Scripts For detailed information on creating formulas, and obtaining the required calculation results, consider all the information in Designing and Building a Security System

This example is based on the Sample Basic database, which is supplied with the Hyperion Essbase installation. If you do not have Sample Basic installed, contact your Hyperion 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.

1. Start Hyperion Essbase Application Manager, and connect to your Hyperion Essbase server.
2. Select the Sample application and the Basic database, and click Open to open the Sample Basic outline.

   Figure 26-2: 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.

3. Double-click the Scenario dimension to display its members.

   Figure 26-3: Scenario Dimension in Sample Basic Outline

   

4. Select the Variance member in the outline, and click the button.

   Hyperion Essbase displays the formula in Formula Editor.

   Figure 26-4: Formula Editor Showing Variance Formula

   

5. To re-create the formula, select the existing formula and select Edit > Delete in Formula Editor.

   Figure 26-5: Formula Editor With Variance Formula Deleted

   

6. In the Dimensions list, select Scenario.

   Hyperion Essbase displays Scenario in the Members list.

   Figure 26-6: Formula Editor Dimensions and Members Lists With Scenario Selected

   

7. In the Members list, double-click the button next to Scenario to display the members under Scenario.
8. Select Formula > Paste Function or click the button.

   Hyperion Essbase displays the Function Templates dialog box.

9. In the Categories list, select Math.
10. In the Templates list, select @VAR.

    Hyperion Essbase displays the function and the default arguments below the Categories list.

    Figure 26-7: Function Templates Dialog Box

    

11. Check Insert Arguments to insert default, temporary arguments in Formula Editor.
12. Click OK.

    Hyperion Essbase inserts @VAR(mbrName1, mbrName2)at the cursor position.

    Figure 26-8: Formula Editor With Variance Formula Added

    

13. Click the button, or type a ; (semicolon) to insert the semicolon formula end-of-line character.
14. Click the button to save the formula.
15. Close Formula Editor.
16. 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 Hyperion Essbase 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.

Opening Formula Editor

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

1. In Outline Editor, highlight the member whose formula you want to create or edit.
2. Select Edit > Formula or click the Formula Editor button, .

   Hyperion 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 26-9: 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 use the GETMBRCALC command in ESSCMD to display member formulas. See the online Technical Reference in the DOCS directory for information about this command. See Performing Interactive and Batch Operations Using ESSCMD for information about ESSCMD.

Adding a Formula

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

1. Highlight the member for which you want to add a formula.
2. Click the button to open Formula Editor.
3. 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.

1. Highlight the member that has the formula you want to edit.
2. Click the button to open Formula Editor.
3. Make the required changes to the formula.

Saving a Formula

You can save formulas to the database outline.

1. In Formula Editor, select File > Save or click the button to save the changes in Formula Editor.
2. Close Formula Editor.
3. Click the button in Outline Editor to save the changes in the database outline.

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

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.

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

   Hyperion Essbase displays the formula in the Formula Editor window.

3. Select the text of the formula.
4. Select Edit > Delete to delete the text of the formula.
5. Click the button to save the changes in Formula Editor.
6. Close Formula Editor and click the button to save the changes in the database outline.

   Hyperion Essbase no longer displays the formula beside the member in the database outline.

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.

1. In Formula Editor, click in the formula text area below the toolbar.
2. Type the appropriate text.

   Text is displayed at the cursor position as you type.

   Figure 26-10: Adding a Formula in Formula Editor

   

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

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:
   
   

For example, to insert an addition operator (+):

1. Place the cursor where you want to insert the addition operator (+).
2. Type + or click the button.

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 (->).

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

1. In Formula Editor, place the cursor where you want to insert the function.
2. Select Formula > Paste Function, or click the button.

   Hyperion Essbase displays the Function Templates dialog box.

3. In the Categories list, select a function category. For example, to insert the @VAR function, select Math.
4. In the Templates list, select the required function or operator. For example, scroll down the list and select @VAR.

   Hyperion Essbase displays the function or operator and the default arguments below the Categories list.

   Figure 26-11: Function Templates Dialog Box With Math Category Selected

   

5. If required, select Insert Arguments to insert default, temporary arguments in the function.
6. Click OK.

   Hyperion Essbase inserts @VAR ()at the cursor position.

   Figure 26-12: Formula Editor Showing @VAR Formula

   

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

   Figure 26-13: Formula Editor Showing @VAR with Arguments

   

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

• Select Edit > Cut.
• Click the button.
• Press Ctrl + X.

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

• Select Edit > Copy.
• Click the button.
• Press Ctrl + C.

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

• Select Edit > Paste.
• Click the button.
• Press Ctrl + V.

1. In Formula Editor, select Edit > Find.
2. Hyperion Essbase displays the Find dialog box.

   Figure 26-14: Formula Editor Find Dialog Box

   

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

1. In the Find dialog box, select Match case.

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

2. Click Find Next.

Inserting Members in a Formula

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

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.

   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 26-15: Formula Editor Dimensions and Members Lists

   

3. To insert a dimension name in the formula, click the dimension name in the Members list.

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

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 26-16: Formula Editor Dimensions and Members Lists, Expanding the Scenario Member

Double-click the button to collapse the member branch.

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

Figure 26-17: Formula Editor Dimensions and Members Lists, Expanded Scenario Dimension

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

Figure 26-18: Formula Editor Dimensions and Members Lists, Collapsing the Scenario Dimension

Searching for Members

1. In the Dimensions list, select the dimension that you want to search for a member.

   For example, select the Measures dimension from the Sample Basic database.

2. Click Find Member.

   Hyperion Essbase displays the Find dialog box.

   Figure 26-19: Formula Editor Find Dialog Box

   

3. In the Find what text box, enter the characters that you want to search for.

   For example, to search for the Marketing member in the Measures dimension, type market.

   1. To search for whole words only, select Match whole word only.

      For example, to search for Margin, but not Margin %, type margin in the Find what text box, and select Match whole word only.

   2. To search for case-sensitive characters, select Match case.

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

4. Click Find Next.

   Hyperion Essbase finds and selects the Marketing member.

   Figure 26-20: Formula Editor Members List With Marketing Selected

   

1. In the Dimensions list, select the dimension for which you want to display all members.

   For example, select the Product dimension in the Sample Basic database.

   Figure 26-21: Formula Editor Dimensions and Members List
   With Product Selected

   

2. Click Expand All.

   In the Members list, Hyperion Essbase displays all members in the dimension.

   Figure 26-22: Formula Editor Dimensions and Members List
   Showing the Children of Product

   

Check Use Aliases.

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

Figure 26-23: 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, Hyperion Essbase inserts the alias name at the cursor position. If required, Hyperion Essbase automatically encloses the alias name in double quotation marks ("").

Checking Syntax

Hyperion Essbase includes both client-based and server-based formula syntax checking that tells you about syntax errors in formulas. For example, Hyperion Essbase tells you if you have mistyped a function name. If you are connected to a server, unknown names can be validated against a ist of custom-defined macro and function names. If you are not connected to a server or the application associated with the outline, Hyperion 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 calc 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.

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

Figure 26-24: Formula Editor Syntax Checker, No Errors Message

If Hyperion 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, Hyperion Essbase displays a message similar to the message shown in Figure 26-25.

Figure 26-25: Formula Editor Syntax Checker, Syntax Error Message

Select Syntax > Next Error or Syntax > Previous Error.

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

Figure 26-26: Formula Editor Syntax Checker, No More Errors Message

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

• By making sure that the toggle Options > Server Formula Validation is set ON and then saving the outline to the server. (A check mark next to the option name indicates server formula validation is set ON.)
• By selecting the Formula Editor menu option Syntax > Validate Formula on Server. If the application is not started, Hyperion Essbase starts the application.

Hyperion Essbase displays outline errors and warnings in the Verify Outline dialog box, similar to Figure 26-27.

Figure 26-27: 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 Hyperion Essbase finds no syntax errors, it displays the No Errors message at the bottom of the Formula Editor window.

Figure 26-28: 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:

• The incorrect formula is saved as part of the outline, even though it contains errors.
• The server writes a message in the application log file that indicates what the error is and displays the incorrect formula.
• The 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.

Writing Formulas

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

• Basic equation
• Conditional
• Interdependent

These sections also discuss how to use cross-dimensional operators in formulas. For more examples of formulas, see Examples of Formulas.

Before writing formulas, review the guidelines in Guidelines for 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 calc 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 calc script, Hyperion 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 calc script, this would be:

Markup = (Retail - Cost) % Retail;

In this example, Hyperion 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 Guidelines for Formula Syntax.

When you use a conditional formula in a calc 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.

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 calc script.

For more information on calc scripts, see Developing Calc Scripts. For more information on Hyperion Essbase functions and calculation commands, see the online 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 calc script, you need to associate the formula with the Commission member as follows:

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

Hyperion Essbase cycles through the database, performing the following 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, Hyperion 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 calc 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;)

Hyperion 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, Hyperion 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, Hyperion 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, Hyperion 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 online Technical Reference in the DOCS directory.

Using Interdependent Values

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

Assuming that the Opening Inventory value for January is loaded into the database, the required calculation is:

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 calc 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;)

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

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:

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

Generating Member Lists

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

For more information on Hyperion Essbase functions, see the online Technical Reference in the DOCS directory.

Performing Mathematical Operations

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

For more information on Hyperion Essbase functions, see the online Technical Reference in the DOCS directory.

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:

• Expense items

  You want Hyperion Essbase to show a positive variance if the actual values are lower than the budget values. For example, you want Hyperion Essbase to show a positive variance if actual costs are lower than budgeted costs.

• Non-expense items

  You want Hyperion Essbase to show a negative variance if the actual values are lower than the budget values. For example, you want Hyperion Essbase to show a negative variance if actual sales are lower than budgeted sales.

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

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.

   Hyperion Essbase tags the member as an expense item. When you use the @VAR or @VARPER functions, Hyperion 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 26-29: Sample Basic Showing Expense Items

   

Calculating Statistics

You can use the following statistical functions to calculate advanced statistics in Hyperion Essbase.

For more information on Hyperion Essbase functions, see the online Technical Reference in the DOCS directory.

Allocating and Forecasting 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.

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

For more information on Hyperion Essbase functions, see the online Technical Reference in the DOCS directory.

Using Range Functions

You can execute a function for a sequence of members by using the following range functions.

For more information on Hyperion Essbase functions, see the online Technical Reference in the DOCS directory.

Using the Current Member Combination to Look Up Values

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

For more information on Hyperion Essbase functions, see the online Technical Reference in the DOCS directory.

Calculating Financial Functions

You can include financial calculations in formulas by using the following functions:

For more information on Hyperion Essbase functions, see the online Technical Reference in the DOCS directory.

Using Date and Time Functions

You can use dates with other functions by using the following date function:

Manipulating Member Names

You can work with member names as character strings by using the following functions:

Using Calculation Mode Functions

You can specify which calculation mode Hyperion Essbase uses to calculate a formula by using the following calculation mode function:

Using Custom-Defined Functions

Custom-defined functions are calculation functions that you create to perform calculations not otherwise supported by the Hyperion Essbase calculation scripting language. You can use custom-defined functions in formulas and calc scripts. These custom-developed functions are written in the Java programming language and registered on the server. The Hyperion 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.

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 put 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 26-30: 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));

Hyperion Essbase cycles through the database, performing the following calculation:

1. Hyperion 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.

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 calc script. You cannot use substitution variables in formulas that you apply to the database outline.

When you run a calc script, Hyperion Essbase replaces the substitution variable with the value you have assigned to it. You can create and assign values to substitution variables using Hyperion Essbase Application Manager, MaxL, or ESSCMD.

You can set substitution variables at the server, application, and database levels. Hyperion Essbase must be able to access the substitution variable from the application and database on which you are running the calc script.

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

To use a substitution variable in a calc script:

Type an ampersand sign (&) followed by variable name.

Hyperion Essbase treats any text string preceded by a & 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 calc script:

@ISMBR(&UpToCurr)

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

@ISMBR(Jan:Jun)

Working with Formulas in Partitions

A Hyperion 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, Hyperion 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 calc scripts for partitions in Writing Calc 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.

Copyright © 1991-2000 Hyperion Solutions Corporation. All rights reserved.