Using Calculation Commands

Calculating the Database Outline

The following calculation commands perform a database calculation based on the structure and formulas in the database outline.

Table 24-1 List of Commands for Calculating a Database

Command	Calculation
CALC ALL	The entire database, based on the outline
CALC DIM	A specified dimension or dimensions
CALC TWOPASS	All members tagged as two-pass on the dimension tagged as accounts
membername	The formula applied to a member in the database outline, where membername is the name of the member to which the formula is applied
CALC AVERAGE	All members tagged as Average on the dimension tagged as accounts
CALC FIRST	All members tagged as First on the dimension tagged as accounts
CALC LAST	All members tagged as Last on the dimension tagged as accounts
CCONV	Currency conversions

Controlling the Flow of Calculations

The following commands manipulate the flow of calculations:

Table 24-2 List of Commands to Control the Flow of Calculations

Command	Calculation
FIX…ENDFIX	Calculate a subset of a database by inclusion
EXCLUDE…ENDEXCLUDE	Calculate a subset of a database by exclusion
LOOP…ENDLOOP	Specify the number of times that commands are iterated
FIXPARALLEL...ENDFIXPARALLEL	Enable parallel calculation on a block of commands

You can also use the IF and ENDIF commands to specify conditional calculations.

Note:

Essbase does not allow branching from one calculation script to another calculation script.

Declaring Data Variables

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

You can also use substitution variables in a calculation script. See Using Substitution Variables in Calculation Scripts.

Table 24-3 List of Commands for Declaring Data Variables

Command	Calculation
ARRAY	Declare one-dimensional array variables
VAR	Declare a temporary variable that contains a single value
THREADPARVAR and TASKPARVAR	Declare a temporary variable that can be used within a FIXPARALLEL block.

Values stored in temporary variables exist only while the calculation 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:

Letters a–z
Numerals 0–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:

ARRAY Discount[Scenario];

You can use multiple arrays at a time.

Specifying Global Settings for a Database Calculation

The following commands define calculation behavior:

Table 24-4 List of Commands for Defining Calculation Behavior

Command	Calculation
SET AGGMISSG	Specify how Essbase treats #MISSING values during a calculation.
SET CACHE	Adjust the default calculator cache size.
SET CALCPARALLEL	Enable parallel calculation. See Enabling CALCPARALLEL Parallel Calculation.
SET CALCTASKDIMS	Increase the number of dimensions used to identify tasks for parallel calculation. See Identifying Additional Tasks for Parallel Calculation.
SET CLEARUPDATESTATUS	Control how Essbase marks data blocks for Intelligent Calculation. See Using the SET CLEARUPDATESTATUS Command.
SET CREATEBLOCKEQ	Turn on and turn off the Create Blocks on Equation setting, which controls the creation of blocks when you assign nonconstant values to members of a sparse dimension. See Nonconstant Values Assigned to Members in a Sparse Dimension.
SET CREATENONMISSINGBLK	Enable calculations on potential data blocks and save these blocks when the result is not #MISSING.
SET FRMLBOTTOMUP	Optimize the calculation of sparse dimension formulas in large database outlines. See Optimizing Formulas on Sparse Dimensions in Large Database Outlines.
SET MSG SET NOTICE	Display messages to trace a calculation.
SET RUNTIMESUBVARS	Declare runtime substitution variables that are used in a calculation script. See Using Runtime Substitution Variables in Calculation Scripts Run in Essbase and Using Runtime Substitution Variables in Calculation Scripts Run in Smart View
SET UPDATECALC	Turn on and turn off Intelligent Calculation. See Turning Intelligent Calculation On and Off.

A SET command in a calculation script stays in effect until the next occurrence of the same SET command.

In the following calculation script, Essbase displays messages at the detail level (SET MSG DETAIL;) when calculating the Year dimension and displays messages at the summary level (SET MSG SUMMARY;) when calculating the Measures dimension:

SET MSG DETAIL;
CALC DIM(Year);

SET MSG SUMMARY;

CALC DIM(Measures);

Some SET calculation commands trigger additional passes through the database.

In the following calculation script, Essbase calculates member combinations for Qtr1 with SET AGGMISSG turned on, and then does a second calculation pass through the database and calculates member combinations for East with SET AGGMISSG turned off:

SET AGGMISSG ON;
Qtr1;
SET AGGMISSG OFF;

East;

Also see Using Two-Pass Calculation.