Developing Calc Scripts

This chapter explains how to develop calc scripts and how to use them to control how Hyperion Essbase calculates a database. It provides some examples of calc scripts, which you may want to adapt for your own use. For more examples, see Examples of Calc Scripts.

This chapter includes the following sections:

• Using a Calc Script
• Creating a Calc Script
• Building a Calc Script in Calc Script Editor
• Using a Calc Script to Control Intelligent Calculation
• Grouping Formulas and Calculations
• Using Substitution Variables
• Clearing Data
• Copying Data
• Calculating a Subset of a Database
• Writing Calc Scripts for Partitions

For information on developing formulas, see Developing Formulas.

Using a Calc Script

A calc script contains a series of calculation commands, equations, and formulas. You use a calc script to define calculations other than the calculations that are defined by the database outline.

For example, the following calc script calculates the Actual values in the Sample Basic database.

Figure 31-1: Calc Script Editor

You can use a calc script to specify exactly how you want Hyperion Essbase to calculate a database. For example, you can calculate part of a database or copy data values between members. You can design and run custom database calculations quickly by separating calculation logic from the database outline.

For most database calculations, a default calculation provides the required results. However, in certain cases, you may need to write a calc script to control how Hyperion Essbase calculates a database.

For example, you need to write a calc script if you want to do any of the following:

• Use the FIX command to calculate a subset of a database. For more information, see Calculating a Subset of a Database and the online Technical Reference in the DOCS directory.
• Change the calculation order of the dense and sparse dimensions in a database.
• Perform a complex calculation in a specific order or perform a calculation that requires multiple iterations through the data. For example, some two-pass calculations require a calc script. For more information, see Optimizing Calculations.
• Perform any two-pass calculation on a dimension without an accounts tag. For more information, see Optimizing Calculations.
• Perform a currency conversion. For more information, see Designing and Building Currency Applications.
• Calculate member formulas that differ from formulas in the database outline. Formulas in a calc script override formulas in the database outline.
• Use an API interface to create a custom calculation dynamically.
• Use logic in a calculation; for example, if you want to use the IF ... ELSE ... ENDIF or the LOOP ... ENDLOOP commands. For more information, see Controlling the Flow of Calculations and the online Technical Reference in the DOCS directory.
• Clear or copy data from specific members. For more information, see Controlling the Flow of Calculations, Copying Data, and the online Technical Reference in the DOCS directory.
• Define temporary variables for use in a database calculation. For more information, see Declaring Data Variables and the online Technical Reference in the DOCS directory.
• Force a recalculation of data blocks after you have changed a formula or an accounts property on the database outline. For more information, see Using Intelligent Calculation to Optimize Calculation.
• Control how Hyperion Essbase uses the Intelligent Calculation feature when calculating a database. For more information, see Using Intelligent Calculation to Optimize Calculation.

Creating a Calc Script

This section provides a step-by-step example of creating and saving a calc script.

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 Hyperion Essbase server installation. This example increases all budget values by 5%. The example assumes that you have Hyperion Essbase Spreadsheet Add-in installed on your machine.

1. Start Hyperion Essbase Application Manager and connect to the Hyperion Essbase server.
2. Select the Sample application and the Basic database, and click the Calc Scripts button, .

   Figure 31-2: Application Desktop Window

   

   If you do not have Sample Basic installed, contact the Hyperion Essbase administrator.

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

3. Click New to open Calc Script Editor.
4. Type the following calc script. This script increases the expense values of Budget->Marketing by 5%.

   FIX(Budget)
   Marketing = Marketing * 1.05;
   ENDFIX
   

   Figure 31-3: Simple Calc Script

   

   For more information on the FIX command, see Using the FIX Command and the online Technical Reference in the DOCS directory.

5. Click the Check Syntax button, , to verify that the syntax of the formula you entered is correct.

   The message "No errors" should be displayed at the bottom of Calc Script Editor.

6. Click the Save button, , to save the calc script. Type Mycalc1 for the calc script object name, and save the calc script on the server (the default).
7. Close the Calc Script Editor window. Mycalc1 is displayed in the list of calc scripts for Sample Basic.

   Figure 31-4: Application Desktop Window Showing Calc Script

   

Calculating Sample Basic Data

You are now ready to calculate the Sample Basic database, increasing the Budget values by 5%. However, first take a look at the Sample Basic Budget values before running the calculation.

1. Open Hyperion Essbase Spreadsheet Add-in, and select Essbase > Connect to connect to Sample Basic.

   This example assumes that you have not changed the default Hyperion Essbase Spreadsheet Add-in Retrieval Options. For more information, see the Hyperion Essbase Spreadsheet Add-in User's Guide.

2. Select Essbase > Retrieve.

   Hyperion Essbase displays the data value from the top level of each dimension.

   Figure 31-5: Hyperion Essbase Spreadsheet Add-in Showing Initial Data

   

3. Double-click Scenario to display its members.
4. Select Budget and select Essbase > Keep Only.

   Hyperion Essbase displays the Budget values.

5. Double-click Year.

   Hyperion Essbase displays the Budget values for each quarter in the year.

   Figure 31-6: Hyperion Essbase Spreadsheet Add-in Showing Retrieved Budget Data for Each Quarter

   

6. Double-click Measures, then Profit, and then Total Expenses to display the Marketing member.
7. Select Marketing and select Essbase > Keep Only.

   Hyperion Essbase displays the Budget->Marketing values. These are the values that will increase by 5%.

   Figure 31-7: Hyperion Essbase Spreadsheet Add-in Showing Retrieved Data for Budget->Marketing

   

Running a Calc Script

Now you are ready to run the Mycalc1 calc script, which increases the Budget->Marketing values by 5%.

1. Minimize but do not close the Hyperion Essbase Spreadsheet Add-in window.
2. In Hyperion Essbase Application Manager, connect to the Hyperion Essbase server, if you are not already connected.
3. Select the Sample application and the Basic database, and click the Calc Scripts button, .

   Figure 31-8: Application Desktop Window Showing Calc Script

   

4. Select Mycalc1 and click Run.
5. When Hyperion Essbase prompts you to select a database, ensure that Sample Basic is selected in the Select Database dialog box. Click OK.

   Hyperion Essbase calculates the database.

   Figure 31-9: Calculating Message Box

   

Checking a Calculation

After Hyperion Essbase finishes a calculation, you can check the dimensions calculated and the calculation time in the application event log.

1. In Hyperion Essbase Application Manager, select Application > View Event Log.
2. In the View Log File dialog box, select Date to view the entries for the current date.
3. When Hyperion Essbase displays the application event log, scroll to the end of the file to see the entries for a calculation. The entries will be similar to the following:

   Figure 31-10: Application Event Log Showing Calculation Messages

   

   From the entries, you can see that Hyperion Essbase calculated data values for the Marketing member on the Measures dimension, fixing on the Budget values. Hyperion Essbase calculated the database in 19.989 seconds.

   Note:

   Check the "fixed members [ ]" part of the message to ensure that all members you fixed on were actually included in the calculation.

   These entries are an example of the default level of messages that Hyperion Essbase provides. If required, you can display more detailed calculation messages in the application event log by using the SET MSG command. For more information, see the online Technical Reference in the DOCS directory.

4. Close the application log viewer window.

1. Maximize the Hyperion Essbase Spreadsheet Add-in window.

   The pre-calculation data values should still be displayed. If the pre-calculation data values are not displayed, repeat the steps in Calculating Sample Basic Data.

2. Select Essbase > Retrieve to display the new data values.

   Figure 31-11: Hyperion Essbase Spreadsheet Add-in Showing Retrieved New Data

   

   As you can see, the data values have increased by 5%. The calculation is successful. If required, you can reload the default data into Sample Basic. See Loading Data.

Building a Calc Script in Calc Script Editor

You use Calc Script Editor to build a calc script. You can type the calc script directly into the text area of Calc Script Editor, or you can use the user interface features of Calc Script Editor to build the calc script.

Calc scripts are ASCII text. If desired, you can create a calc script in the text editor of your choice and paste it into Calc Script Editor.

Hyperion Essbase provides a flexible set of commands that you can use to control how a database is calculated. You can construct calc scripts from commands and formulas. Several types of commands are discussed in the following sections, including the following:

• Computation
• Control flow
• Data declaration
• Global settings

Implementing Outline Calculations

You can use the following calculation commands to perform a database calculation that is based on the structure and formulas in the database outline.

Controlling the Flow of Calculations

You can use the following commands to manipulate the flow of calculations. For detailed information on these commands, see the online Technical Reference in the DOCS directory.

You can also use the IF and ENDIF commands to specify conditional calculations. See Controlling the Flow of Calculations.

Declaring Data Variables

You can use the following commands to declare temporary variables and, if required, to set their initial values. Temporary variables store the results of intermediate calculations.

You can also use substitution variables in a calc script. See Using Substitution Variables.

For detailed information on the these commands, see the online Technical Reference in the DOCS directory.

Values stored in temporary variables exist only while the calc script is running. You cannot report on the values of temporary variables.

Variable and array names are character strings that contain any of the following characters:

• Alphabetic letters: a through z
• Numerals: 0 through 9
• Special characters: $ (dollar sign), # (pound sign), and _ (underscore)

Typically, arrays are used to store variables as part of a member formula. The size of the array variable is determined by the number of members in the corresponding dimension. For example, if the Scenario dimension has four members, the following command creates an array called Discount with four entries. You can use more than one array at a time.

ARRAY Discount[Scenario];

Specifying Global Settings for a Database Calculation

You can use the following commands to define calculation behavior.

SET commands in a calc script are procedural. A SET command in a calc script stays in effect until the next occurrence of the same SET command.

For example, consider the following calc script:

SET MSG DETAIL;
CALC DIM(Year);

SET MSG SUMMARY;
CALC DIM(Measures);

Hyperion Essbase displays messages at the detail level when calculating the Year dimension. However, when calculating the Measures dimension, Hyperion Essbase displays messages at the summary level.

Now, consider this calc script:

SET AGGMISSG ON;
Qtr1;

SET AGGMISSG OFF;
East;

Hyperion Essbase calculates member combinations for Qtr1 with SET AGGMISSG (aggregate missing values) turned on. Hyperion Essbase then does a second calculation pass through the database and calculates member combinations for East with SET AGGMISSG turned off. For more information on the setting for aggregating missing values, see the SET AGGMISSG command in the online Technical Reference in the DOCS directory. For more information on calculation passes, see Optimizing Calculations.

Adding Comments

You can include comments to annotate calc scripts. Hyperion Essbase ignores these comments when it runs the calc script.

To include a comment, start the comment with /* and end the comment with */. For example, consider the following comment:

/*   This is a calc script comment
     that spans two lines.*/

Composing Calc Script Syntax

When you create a calc script, you need to apply the following rules:

• End each formula or calc script command with a semicolon (;), as shown in the following four examples:

  CALC DIM(Product, Measures);
  
  DATACOPY Plan TO Revised_Plan;
  
  "Market Share" = Sales % Sales->Market;
  
  IF
    (Sales <> #MISSING) Commission = Sales * .9;
  ELSE 
    Commission = #MISSING;
  ENDIF;
  

You do not need to end the following commands with semicolons: IF, ELSE, ELSEIF, FIX, ENDFIX, LOOP, and ENDLOOP.

• Enclose the 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.
    • Includes any non-alphanumeric character; for example, hyphens (-), asterisks (*), and slashes (/).
    • Is all numeric or starts with a numeral; for example, "100" or "10Prod".
    • Begins with an ampersand (&). The leading ampersand (&) is reserved for substitution variables. If a member name begins with &, enclose it in quotation marks. Do not enclose substitution variables in quotation marks in a calc script.
    • Contains a dot (.); for example, "1998.Jan" or ".100".

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

• Do not enclose variable names in quotation marks.
• If you are using an IF statement or an interdependent formula, enclose the formula in parentheses and associate it with the specified member. For example, the following formula is associated with the Commission member in the database outline:

  Commission
  (IF(Sales < 100)
      Commission = 0;
  ENDIF;)
  

• End each IF statement in a formula with an ENDIF statement. For example, the following formula contains a simple IF...ENDIF statement:

  Profit
  (IF (Sales > 100)
      Commission = Sales * .1;
  ENDIF;)
  

• If you are using an IF statement that is nested within another IF statement, end each IF with an ENDIF. For example, consider the following 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, consider the following:

  Marketing
  (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.
• End each FIX statement with an ENDFIX statement. For example:

  FIX(Budget,@DESCENDANTS(East))
  CALC DIM(Year, Measures, Product);
  ENDFIX
  

  The FIX and ENDFIX statements do not need to be followed by a semicolon (;).

When you write a calc script, you can use the Calc Script Editor syntax checker to check the syntax. For more information, see Checking Syntax.

Opening Calc Script Editor

Open Calc Script Editor to create a new calc script or open an existing calc script.

For information on opening an existing calc script, see Changing a Calc Script.

1. Open Hyperion Essbase Application Manager and connect to the Hyperion Essbase server.
2. In the application desktop server window, select the desired application and database.
3. Click the Calc Scripts button, .

   Hyperion Essbase displays a list of all the calc scripts associated with the application and database that you selected.

   Figure 31-12: Application Desktop Window

   

4. To create a new calc script, click New.

   To open an existing calc script, select it in the Calc Scripts list and click Open.

   Hyperion Essbase opens Calc Script Editor:

   Figure 31-13: Calc Script Editor

   

Adding a Calc Script

You can use Hyperion Essbase Application Manager to add a new calc script.

1. In the application desktop server window, select the application and database with which you want to associate the new calc script.
2. Click the Calc Scripts button, , and then click New.

   Alternatively, from the Hyperion Essbase Application Manager menu, select File > New > Calc Script.

   Hyperion Essbase opens Calc Script Editor. You can now build a calc script. Hyperion Essbase prompts you to name a calc script when you save it. See Saving a Calc Script.

Changing a Calc Script

To change a calc script, open it in Calc Script Editor. How you do that depends on where the calc script is stored.

1. In the application desktop server window, select the application and database that contains the calc script.

   The following example shows the application desktop server window for an Hyperion Essbase server called Aspen. The Sample Basic database is selected.

   Figure 31-14: Application Desktop Server Window

   

2. Click the Calc Scripts button, .

   Hyperion Essbase displays the calc script files (.CSC files) stored in the \ARBORPATH\APP\appname\dbname directory on the server machine, where ARBORPATH is the directory in which you installed Hyperion Essbase and appname and dbname are the current application and database.

   For example, assuming that the Hyperion Essbase install directory is C:\ESSBASE, if you select Sample Basic, Hyperion Essbase displays the.CSC files in the C:\ESSBASE\APP\SAMPLE\BASIC directory.

3. In the Calc Scripts list, select the calc script you want to modify.
4. Click Open.

   Hyperion Essbase opens Calc Script Editor.

You can now edit the calc script.

1. Ensure that the focus is on the application desktop server window.
2. From the Hyperion Essbase Application Manager menu, select File > Open.

   Hyperion Essbase displays the Open Server Object dialog box.

   Figure 31-15: Open Server Object Dialog Box

   

3. Click Connect to connect to the other server, and click OK.
4. In the Open Server Object dialog box, select the application and database that contain the calc script.
5. In the Objects list, select the required calc script, and click OK.

   Hyperion Essbase displays the calc script in Calc Script Editor.

1. In the application desktop client window, select the application and database that contains the calc script.
2. Click the Calc Scripts button, .

   Hyperion Essbase displays the calc script files (.CSC files) stored in the \ARBORPATH\CLIENT\appname\dbname directory on your client machine, where ARBORPATH is the directory in which you installed Hyperion Essbase, and appname and dbname are the current application and database on your client machine.

   For example, assuming that the Hyperion Essbase install directory is C:\ESSBASE, if you have an application called MYAPP01 and a database called MYDB01 on your client machine, Hyperion Essbase displays the.CSC files in the C:\ESSBASE\CLIENT\MYAPP01\MYDB01 directory on your client machine.

   In this example, there are three calc scripts already created for the MYDB01 database.

   Figure 31-16: Application Desktop Client Window

   

3. In the Calc Scripts list, select the required calc script.
4. Click Open.

   Hyperion Essbase opens Calc Script Editor. You can now edit the calc script.

1. From the Hyperion Essbase Application Manager menu, select File > Open.

   Hyperion Essbase displays the Open Client Object dialog box.

   Figure 31-17: Open Client Object Dialog Box

   

2. Click File System to connect to the other server.

   Hyperion Essbase displays the Open Client File dialog box.

3. Select the file that contains the required calc script, and click OK.

   Hyperion Essbase displays the calc script in Calc Script Editor.

Saving a Calc Script

You can save a calc script as either of the following:

• An object on the Hyperion Essbase server or on a client machine. You can associate the calc script object with either of the following:
  • An application and all the databases within the application, which means that you can run the calc script on any of the databases in the application
  • A database, which means that you can run the calc script on the database
• A file on your client machine

If you want other users to have access to the calc script, you need to save it on the Hyperion Essbase server. If you save a calc script on your client machine, other users do not have access to the calc script. While you are developing a calc script, you may want to save it on your client machine. Then move the completed script to the Hyperion Essbase server.

When you save a calc script from Calc Script Editor, by default Hyperion Essbase associates it with the current application and database.

Calc scripts created using Hyperion Essbase Application Manager are given a.CSC extension by default. If you run a calc script from Hyperion Essbase Application Manager or from Hyperion Essbase Spreadsheet Add-in, it must have a.CSC extension. However, a calc script is an ASCII file, and you can use MaxL or ESSCMD to run any ASCII file as a calc script.

A calc script can also be a string defined in memory. You can access this string via the API on the Hyperion Essbase client or Hyperion Essbase server. Thus, from dialog boxes, you can dynamically create a calc script that is based on user selections.

1. In Calc Script Editor, click the Save button, .

   Hyperion Essbase displays the Save Server Object dialog box.

   Figure 31-18: Save Server Object Dialog Box

   

2. Associate the calc script with an application or database.

   To associate the calc script with an application and all the databases within the application:

   1. In the Application list, select the required application.
   2. In the Database list, select "(all dbs)" (all databases).

   To associate the calc script with a database:

   1. In the Application list, select the application containing the database.
   2. In the Database list, select the required database.
      
      
3. In the Object Name text box, type the name that you want to give the calc script. You can type up to 8 alphanumeric characters.

   Figure 31-19: Save Server Object Dialog Box

   

4. Click OK.

   Hyperion Essbase saves the calc script as a calculation script object on the Hyperion Essbase server.

Calc script objects associated with an application are saved in the \ARBORPATH\APP\appname directory on the Hyperion Essbase server machine. Calc script objects associated with a database are saved in the \ARBORPATH\APP\appname\dbname directory on the Hyperion Essbase server machine. ARBORPATH is the Hyperion Essbase install directory, and appname and dbname are the application and database with which you have associated the calc script.

For example, consider the following:

• If you associate a calc script called CalcTwo with the Sample Basic database and the Hyperion Essbase install directory is C:\ESSBASE, Hyperion Essbase saves CalcTwo as CALCTWO.CSC in C:\ESSBASE\APP\SAMPLE\BASIC.
• If you associate a calc script called CalcTwo with the Sample application and the Hyperion Essbase install directory is C:\ESSBASE, Hyperion Essbase saves CalcTwo as CALCTWO.CSC in C:\ESSBASE\APP\SAMPLE.

1. In Calc Script Editor, click the Save button, .

   If the calc script is new, Hyperion Essbase displays either the Save Server Object dialog box or the Save Client Object dialog box, depending on whether you opened Calc Script Editor from the application desktop server window or the client window.

   If Hyperion Essbase displays the Save Server Object dialog box, under Location, select Client. The Save Client Object dialog box replaces the Save Server Object dialog box.

   Figure 31-20: Save Client Object Dialog Box

   

2. Associate the calc script with an application or database.

   To associate the calc script with an application on your client machine and all the databases within the application:

   1. In the Application list, select the required application.
   2. In the Database list, select "(all dbs)" (all databases).

   To associate the calc script with a database:

   1. In the Application list, select the application containing the database.
   2. In the Database list, select the required database.
      
      
3. In the Object Name text box, type the name that you want to give the calc script. You can type up to 8 alphanumeric characters.
   
   
4. Click OK.

   Hyperion Essbase saves the calc script as a calc script object on your client machine.

Calc script objects associated with an application are saved in the \ARBORPATH\CLIENT\appname directory on the Hyperion Essbase client machine. Calc script objects associated with a database are saved in the \ARBORPATH\CLIENT\appname\dbname directory on the Hyperion Essbase client machine. ARBORPATH is the Hyperion Essbase install directory, and appname and dbname are the application and database with which you associate the calc script.

For example, consider the following:

• If you associate a calc script called CalcTwo with the MYDB01 database in the MYAPP01 application and the Hyperion Essbase install directory is C:\ESSBASE, Hyperion Essbase saves CalcTwo as CALCTWO.CSC in C:\ESSBASE\CLIENT\MYAPP01\MYDB01.
• If you associate a calc script called CalcTwo with the MYAPP01 application and the Hyperion Essbase install directory is C:\ESSBASE, Hyperion Essbase saves CalcTwo as CALCTWO.CSC in C:\ESSBASE\CLIENT\MYAPP01.

1. In Calc Script Editor, click the Save button, .

   If the calc script is new, Hyperion Essbase displays either the Save Server Object dialog box or the Save Client Object dialog box, depending on whether you opened Calc Script Editor from the application desktop server window or the client window.

   If Hyperion Essbase displays the Save Server Object dialog box, under Location, select Client. The Save Client Object dialog box replaces the Save Server Object dialog box.

2. In the Save Client Object dialog box, click File System.

   Hyperion Essbase displays the Save Client File dialog box.

3. Enter the required directory and file name, and click OK.

   Hyperion Essbase saves the calc script in the directory you specified.

1. In Calc Script Editor, open the calc script that you want to copy. For more information, see Changing a Calc Script.
2. From the Hyperion Essbase Application Manager menu, select File > Save As.

   Hyperion Essbase displays the Save Client Object dialog box.

   Figure 31-21: Save Client Object Dialog Box

   

3. Under Location, select Server.

   The Save Server Object dialog box replaces the Save Client Object dialog box.

   Figure 31-22: Save Server Object Dialog Box

   

4. Select the application or database with which you want to associate the calc script. For more information, see Saving a Calc Script.
   
   
5. In the Object Name text box, type the name that you want to give the calc script. You can type up to 8 alphanumeric characters.
   
   
6. Click OK.

   Hyperion Essbase saves the calc script on the Hyperion Essbase server. For more information, seeSaving a Calc Script.

Running a Calc Script

You can run a calc script from any of the following:

• Hyperion Essbase Application Manager
• Hyperion Essbase Spreadsheet Add-in (See the Hyperion Essbase Spreadsheet Add-in User's Guide.)
• MaxL (using execute calculation)
• ESSCMD

If you run a calc script from the Hyperion Essbase Application Manager, you can run it on your Hyperion Essbase client machine or on the Hyperion Essbase server.

When you run a calc script from Hyperion Essbase Application Manager or from Hyperion Essbase Spreadsheet Add-in, you can view the calculation messages in the event log file. When you use MaxL or ESSCMD to run a calc script, Hyperion Essbase displays the messages ESSCMD terminal and in the ESSCMD or MaxL output file, if output logging is turned on. To display the event log file, select Application > View Event Log from the Hyperion Essbase Application Manager menu.

Hyperion Essbase displays both of the following:

• The calculation order of the dimensions for each pass through the database
• The total calculation time

You can use these messages to tune a database during calculation. To display more detailed information, you can use the SET MSG SUMMARY, SET MSG DETAIL, and SET NOTICE commands in a calc script. For more information, see Specifying Global Settings for a Database Calculation.

You can run a calc script from the Hyperion Essbase Application Manager desktop or from the Hyperion Essbase Application Manager menu.

1. If the calc script is saved on the Hyperion Essbase server, open the application desktop server window.

   If the calc script is saved on your client machine, open the application desktop client window.

2. In the application desktop server window or the client window, select the application and database that contains the calc script you want to run.
3. Click the Calc Scripts button, , to display the calc scripts associated with the application and database that you selected. The following example shows the application desktop server window for a server called Aspen:

   Figure 31-23: Application Desktop Server Window

   

4. In the Calc Scripts list, select the calc script that you want to run, and click Run.

   Hyperion Essbase displays the Select Database dialog box.

   Figure 31-24: Select Database Dialog Box

   

5. Select the Hyperion Essbase server, application, and database against which you want to run the calc script.

   If you are not currently connected to the server, click Connect.

6. Click OK.

   Hyperion Essbase runs the calc script against the database that you selected.

1. In the application desktop server window, select the application and database that contains the calc script that you want to run.
2. From the Hyperion Essbase Application Manager menu, select Database > Calculate.

   Hyperion Essbase displays the Calculate Database dialog box.

   Figure 31-25: Calculate Database Dialog Box

   

3. In the Calc Scripts list, select the calc script that you want to run and click OK. Only scripts to which you have security access are displayed in the list.

   Hyperion Essbase runs the calc script against the database you selected in the application desktop server window.

You can use execute calculation in MaxL or the RUNCALC command in ESSCMD to perform this task. See the online Technical Reference in the DOCS directory for information.

For information on running a calc script from Hyperion Essbase Spreadsheet Add-in, see the Hyperion Essbase Spreadsheet Add-in User's Guide.

Printing a Calc Script

You can print a calc script from Calc Script Editor.

1. In Calc Script Editor, open the calc script. For more information, see Opening Calc Script Editor.
2. Select File > Print, or click the Print button, .

   Hyperion Essbase displays the Print Calc Script dialog box.

3. If you want to print page numbers on a calc script, check Page Numbers.
   
   
4. Click Print.

Deleting a Calc Script

How you delete a calc script depends on where it is saved.

1. In the application desktop server or client window, select the application and database with which the calc script is associated.
2. Click the Calc Scripts button, .

   Hyperion Essbase displays a list of all the calc scripts associated with the application and database that you chose.

   Figure 31-26: Application Desktop Server Window

   

3. In the Calc Scripts list, select the calc script that you want to delete.
   
   
4. From the Hyperion Essbase Application Manager menu, select File > Delete.

   Hyperion Essbase displays a Confirm Delete message box.

5. Click Yes to delete the calc script.

You cannot delete the file using Hyperion Essbase Application Manager. Delete the calc script file by using the client machine's file system.

In Calc Script Editor, select Edit > Undo, or click the button.

Using Formulas in a Calc Script

You can place member formulas in a calc script. When you place formulas in a calc script, they override any conflicting formulas that are applied to members in the database outline.

In a calc script, you can do both of the following:

• Calculate a member formula on the database outline
• Define a formula

To calculate a formula that is applied to a member in the database outline, simply use the member name followed by a semicolon (;). For example:

Variance;

calculates the formula applied to the Variance member in the database outline.

To define a formula in a calc script, use Calc Script Editor. For example:

Expenses = Payroll + Marketing + Misc;

cycles through the database, adding the values in the members Payroll, Marketing, and Misc and placing the result in the Expenses member. This formula overrides any formula placed on the Expenses member in the database outline.

Basic Equations

You can define basic equations in a calc script 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, the following formula causes Hyperion Essbase to cycle through the database, subtracting the values in COGS from the values in Sales and placing the result in Margin:

Margin = Sales - COGS;

The next formula cycles through the database subtracting the values in Cost from the values in Retail, calculating the resulting values as a percentage of the values in Retail, and placing the results in Markup:

Markup = (Retail - Cost) % Retail;

For more information on the nature of multidimensional calculations, see Multidimensional Concepts.

Conditional Equations

When you use an IF statement as part of a member formula in a calc script, you need to do both of the following:

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

For example:

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

Hyperion Essbase cycles through the database and performs the following calculations:

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

The whole of the IF ... ENDIF statement is enclosed in parentheses and associated with the Profit member, Profit(IF(...)...).

Interdependent Formulas

When you use an interdependent formula in a calc script, the same rules apply as for the IF statement. You need to do both of the following:

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

Consider the interdependent formula discussed earlier. If you place the formula in a calc script, you construct it as follows:

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

The whole of the formula is enclosed in parentheses and is associated with the Opening Inventory member, as follows:

"Opening Inventory"(IF(...)...)

Inserting Text and Operators in a Calc Script

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

1. Click in the text area below the toolbar.
2. Type the appropriate text.

   Text is displayed at the cursor position as you type.

   Figure 31-27: Calc Script Editor Showing Calc Script

   

1. Place the cursor where you want to insert the equal sign (=).
2. Type = or click the button.

1. 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 (+), place the cursor where you want to insert the addition (+) operator, and type + or click the button.

1. 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 Developing Formulas.

1. Place the cursor at the end of the formula.
2. Type a ; (semicolon), or click the button.

1. 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 the 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 31-28: Function Templates Dialog Box

   

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

   Hyperion Essbase inserts @VAR at the cursor position.

   Figure 31-29: Calc Script Editor With @VAR Function Inserted

   

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

   Figure 31-30: Calc Script Editor With @VAR Function and Arguments Inserted

   

1. Select the text that you want to cut.
2. Select Edit > Cut, click the button, or press Ctrl + X.

1. Select the text that you want to copy.
2. Select Edit > Copy, click the button, or press Ctrl + C.

1. Select the text that you want to paste.
2. Select Edit > Paste, click the button, or press Ctrl + V.

1. Select Edit > Find.

   Hyperion Essbase displays the Find dialog box.

   Figure 31-31: Calc Script Editor Find Dialog Box

   

2. In the "Find what" text box, type the characters you want to search for, and click Find Next.

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

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

2. Click Find Next.

Associating a Calc Script with a Database

If you want to insert member names in a calc script by selecting them within Calc Script Editor, you need to associate the calc script with the database outline that contains the members.

1. Click the button or, from the Calc Script Editor menu, select Options > Associate Outline.

   Hyperion Essbase displays the Associate Client Outline Object or Associate Server Outline Object dialog box.

   Figure 31-32: Associate Server Outline Object Dialog Box

   

2. Do one of the following:
   • To associate an outline saved on your client machine, under Location, select Client.
   • To associate an outline saved on the Hyperion Essbase server machine, under Location, select Server.
3. In the Server, Application, and Database lists, select the server, application, and database that contain the outline that you want to associate with a calc script.
4. In the Objects list, select the database outline.
5. Click OK.

   Hyperion Essbase displays the dimension names of the associated outline in the Dimensions list. You can now insert members from this list. See Associating a Calc Script with a Database.

Hyperion Essbase associates the calc script with the database outline only while you are editing the calc script. When you close Calc Script Editor, Hyperion Essbase cancels the association. If you want to insert members from the database outline in the future, you need to re-associate the outline with the calc script.

1. Associate the database outline that contains the dimensions you want to insert. See Associating a Calc Script with a Database.
2. In Calc Script Editor, place the cursor where you want to insert the member name.
3. In the Dimensions list, select the dimension that contains the member you want to insert in a formula.

   The name of the dimension is displayed in the Members list. If a button is displayed to the left of the dimension name, the dimension has children. The following shows the Scenario dimension in the Sample Basic database.

   Figure 31-33: Inserting Dimensions and Members In a Calc Script

   

   If you want to insert the name of the dimension in a formula, click the dimension name in the Members list. Hyperion Essbase inserts the dimension name at the cursor position.

1. To display a member's children, in the Members list, double-click the button next to the member name.

   The button changes to a button.

   Figure 31-34: Expanding a Member Branch

   

2. To collapse the member branch, double-click the button.

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

   Figure 31-35: Collapsing a Member Branch

   

1. In the Dimensions list, select the dimension in which 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 31-36: Searching For Members

   

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

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

   Figure 31-37: Searching For Members

   

   1. To make the search case-sensitive, check Match case.
   2. To search for whole words only, check Match whole word only.

      For example, to search for Margin, but not Margin % in the Sample Basic database, type margin, and check Match whole word only.

4. Click Find Next.

   Hyperion Essbase finds and selects the appropriate member.

   Figure 31-38: Searching For Members

   

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

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

   Figure 31-39: Expanding a Dimension

   

2. Click Expand All.

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

   Figure 31-40: Expanding a Dimension

   

1. 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 31-41: Displaying and Inserting Alias Names

   

2. To select a different alias table, from the Alias Table list box, select the 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 a syntax checker that tells you about any syntax errors in a calc script. For example, Hyperion Essbase tells you if you have mistyped a function name.

The syntax checker cannot tell you about semantic errors in a calc script. Semantic errors occur when a calc script does not work as you expect. To find semantic errors, always run the calculation, and check the results to ensure they are as you expect.

Select Syntax > Check Syntax or click the button.

Hyperion Essbase displays the syntax checker results at the bottom of the Calc Script Editor window. If Hyperion Essbase finds no syntax errors, it displays the following 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 calc script command, Hyperion Essbase displays a message similar to the following:

Select Syntax > Next Error or Syntax > Previous Error.

When you reach the first or last error, Hyperion Essbase displays the message:

Hyperion Essbase maintains the list of error messages until you check the syntax again.

Using a Calc Script to Control Intelligent Calculation

Assume that you have a formula on a sparse dimension member and the formula contains either of the following:

• A relationship function (for example, @PRIOR or @NEXT)
• A financial function (for example, @NPV or @INTEREST)

Hyperion Essbase always recalculates the data block that contains the formula, even if the data block is marked as clean for the purposes of Intelligent Calculation. For more information, see Using Intelligent Calculation to Optimize Calculation.

Grouping Formulas and Calculations

You may achieve significant calculation performance improvements by carefully grouping formulas and dimensions in a calc script. For more information and examples, see Calculating a Series of Member Formulas and Calculating a Series of Dimensions.

When you run a calc script, Hyperion Essbase automatically displays the calculation order of the dimensions for each pass through the database. Thus, you can tell how many times Hyperion Essbase has cycled through the database during the calculation.

Hyperion Essbase displays these information messages in the ESSCMD window and in the event log file. To display the event log file, select Application > View Event Log from the Hyperion Essbase Application Manager menu.

Calculating a Series of Member Formulas

When you calculate formulas, avoid using parentheses unnecessarily. The following formulas cause Hyperion Essbase to cycle through the database once, calculating both formulas in one pass:

Profit = (Sales - COGS) * 1.5;
Market = East + West;

Similarly, the following configurations cause Hyperion Essbase to cycle through the database only once, calculating the formulas on the members Qtr1, Qtr2, and Qtr3:

Qtr1;
Qtr2;
Qtr3;

or

(Qtr1;
Qtr2;
Qtr3;)

However, the inappropriately placed parentheses in the following example cause Hyperion Essbase to cycle through the database twice, once calculating the formulas on the members Qtr1 and Qtr2 and once calculating the formula on Qtr3:

(Qtr1;
Qtr2;)
Qtr3;

Calculating a Series of Dimensions

When you calculate a series of dimensions, you can optimize performance by grouping the dimensions wherever possible.

For example, the following formula causes Hyperion Essbase to cycle through the database only once:

CALC DIM(Year, Measures);

However, the following syntax causes Hyperion Essbase to cycle through the database twice. It cycles through once for each CALC DIM command:

CALC DIM(Year);
CALC DIM(Measures);

Using Substitution Variables

You can use substitution variables in calc scripts. Substitution variables are useful, for example, when you reference information or lists of members that change frequently.

When you include a substitution variable in a calc script, Hyperion Essbase replaces the substitution variable with the value you specified for the substitution variable.

You create and specify values for substitution values in Hyperion Essbase Application Manager. For more information, see Creating Applications and Databases.

You can create variables at the server, application, and database levels. When you use a substitution variable in a calc script, it must be available to the calc script. For example, if you create a substitution variable at the database level, it is only available to calc scripts within the database. However, if you create a variable at the server level, it is available to any calc script on the server.

The ampersand (&) character prefaces a substitution variable in a calc script. Hyperion Essbase treats any string that begins with a leading ampersand as a substitution variable, replacing the variable with its value before parsing the calc script.

For example, &CurQtr; becomes Qtr1; if you have given the substitution variable &CurQtr the value Qtr1.

Consider an example in which you want to calculate Sample Basic data for the current quarter. You can use the following calc script:

FIX(&CurQtr)
CALC DIM(Measures, Product);
ENDFIX

You then define the substitution variable CurQtr as the current quarter; for example, Qtr3. Hyperion Essbase replaces the variable CurQtr with the value Qtr3 when it runs the calc script.

Clearing Data

You can use the CLEARDATA and CLEARBLOCK calculation commands to remove data values and data blocks from a database. You can use the CLEARBLOCK DYNAMIC command to remove blocks for Dynamic Calc And Store member combinations. For more information, see Dynamically Calculating Data Values.

When you use the CLEARBLOCK command, Hyperion Essbase removes the entire contents of a block, including all the dense dimension members. Hyperion Essbase removes the entire block, regardless of any FIX command on members within the block.

The following examples are based on the Sample Basic database. If the Scenario dimension is dense, the following example removes all the data blocks that do not contain input data values. Hyperion Essbase ignores the FIX command:

FIX(Actual)
CLEARBLOCK NONINPUT;
ENDFIX

If the Scenario dimension is sparse, the following formula removes only the blocks whose Scenario dimension member is Actual. The other blocks remain:

FIX(Actual)
CLEARBLOCK NONINPUT;
ENDFIX

When you use the CLEARDATA command, Hyperion Essbase changes the values of the cells you specify to #MISSING. The data blocks are not removed unless all cells in a block are cleared (that is, all cells in the block are set to #MISSING).

For example, the following formula clears all the Actual data values for Colas:

CLEARDATA Actual->Colas;

You can use the FIX command with the CLEARDATA command to clear a subset of a database. If you want to clear an entire database, you can select the Clear Data command from the Database menu in Hyperion Essbase Application Manager.

For more information on the CLEARBLOCK and CLEARDATA calculation commands, see the online Technical Reference in the DOCS directory.

Copying Data

You can use the DATACOPY calculation command to copy data cells from one range to another range in a database. The two ranges must be the same size.

For example, in the Sample Basic database, the following formula copies Actual values to Budget values:

DATACOPY Actual TO Budget;

You can use the FIX command to copy a subset of values.

For more information on the FIX command, see the online Technical Reference in the DOCS directory.

Calculating a Subset of a Database

You can calculate a subset of a database, which means that you can use different formulas to calculate separate sections of a database.

To calculate a subset of a database, you can use either of the following:

• Member set functions to calculate lists of members
• The FIX ... ENDFIX commands to calculate a range of values

For more information, see Calculating Lists of Members and Using the FIX Command.

Calculating Lists of Members

You can use a member set function to generate a list of members that is based on a member you specify. For example, you can use the @IDESCENDANTS function to generate a list of all the descendants of a specified member.

In the Sample Basic database, @IDESCENDANTS("Total Expenses"); generates the following list of members: Total Expenses, Marketing, Payroll, and Misc.

When you use a member set function in a formula, Hyperion Essbase generates a list of members before calculating the formula.

For detailed information on these and other member set functions, see the online Technical Reference in the DOCS directory.

Using the FIX Command

The FIX ... ENDFIX commands are particularly useful to calculate a carefully defined subset of the values in a database. For example, the following calc script calculates only the Budget values for only the descendants of East (New York, Massachusetts, Florida, Connecticut, and New Hampshire) in the Sample Basic database:

FIX(Budget,@DESCENDANTS(East))
CALC DIM(Year, Measures, Product);
ENDFIX

The next example fixes on member combinations for the children of East that have a user-defined attribute (UDA) of New Mkt. For information on defining user-defined attributes, see Creating and Changing Database Outlines.

FIX(@CHILDREN(East) AND @UDA(Market,"New Mkt"))
Marketing = Marketing * 1.1;
ENDFIX

The next example uses a wildcard match to fix on member names that end in the characters -10. In Sample Basic, this example fixes on the members 100-10, 200-10, 300-10, and 400-10.

FIX(@MATCH(Product, "???-10"))
Price = Price * 1.1;
ENDFIX

When you use the FIX command only on a dense dimension, Hyperion Essbase retrieves the entire block that contains the required value or values for the member or members that you specify. Thus, I/O is not affected, and the calculation performance time is improved.

When you use the FIX command on a sparse dimension, Hyperion Essbase retrieves the block for the specified sparse dimension member or members. Thus, I/O may be greatly reduced.

Hyperion Essbase cycles through the database once for each FIX command that you use on dense dimension members. When possible, combine FIX blocks to improve calculation performance. For example, the following calc script causes Hyperion Essbase to cycle through the database only once, calculating both the Actual and the Budget values:

FIX(Actual,Budget)
CALC DIM(Year, Measures);
ENDFIX

However, this calc script causes Hyperion Essbase to cycle through the database twice, once calculating the Actual data values and once calculating the data values for Budget:

FIX(Actual)
CALC DIM(Year, Measures);
ENDFIX
FIX(Budget)
CALC DIM(Year, Measures);
ENDFIX

You cannot FIX on a subset of a dimension that you calculate within a FIX statement. For example, the following calc script returns an error message because the CALC DIM operation calculates the entire Market dimension, although the FIX above it fixes on specific members of the Market dimension.

FIX(@CHILDREN(East) AND @UDA(Market,"New Mkt"))
CALC DIM(Year, Measures, Product, Market);
ENDFIX

You cannot use the FIX command within a member association. The code example in the following four lines is incorrect:

Jan (
Fix (@IDESCENDANTS(Sales))
Actual=5;
Endfix;
)

Within a member association, use an IF statement instead of a FIX command. The previous example should be coded as follows:

Jan (
If (@ISIDESC(Sales))
Actual=5;
EndIF;
)

For detailed information on using the FIX command, see the online Technical Reference in the DOCS directory.

Writing Calc Scripts for Partitions

A Hyperion Essbase OLAP Server partitioned application can span multiple servers, processors, or computers. For more information on partitioning, see Designing Partitioned Applications and Building and Maintaining Partitions.

You can achieve significant calculation performance improvements by partitioning applications and running separate calculations on each partition.

However, when you use partitioning, you need to do both of the following:

• Consider carefully the performance impact on the overall database calculation. You might choose to do any of the following:
  • Redesign the overall calculation to avoid referencing remote values that are in a transparent partition in a remote database.
  • Dynamically calculate a value in a remote database. See Dynamically Calculating Data Values.
  • Replicate a value in the database that contains the applicable formula. See Designing Partitioned Applications. Ensure that you replicate only the required values. For example, if you are replicating quarterly data for the Eastern region, replicate only the values for Qtr1, Qtr2, Qtr3, and Qtr4, and calculate the parent Year values locally.
• Ensure that a referenced value is up-to-date when Hyperion Essbase retrieves it. Choose one of the options previously discussed (redesign, dynamically calculate, or replicate) or calculate the referenced database before calculating the formula.

Calculating Multiple Databases

You need to calculate databases in a specific order to ensure that Hyperion Essbase calculates the required results. For example, consider the following partitions in which you view information from the West, Central, and East databases transparently from the Corporate database.

Figure 31-42: Calculating Partitions

West, Central, and East contain only actual values. Corporate contains actual and budgeted values. Although you can view the West, Central, and East data in the Corporate database, the data exists only in the West, Central, and East databases; it is not duplicated in the Corporate database.

Therefore, when Hyperion Essbase calculates Corporate, it needs to take the latest values from West, Central, and East. To obtain the required results, you need to calculate West, Central, and East before you calculate Corporate.

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