Calculation Command Groups

Overview
Calculation Command Groups
Command List

This section lists calculation commands, grouped by command type:

Data Declarations
Control Flow
Functional

Conditionals
Member Formulas

Data Declaration Commands

These commands are used to declare, and set the initial values of, temporary variables. The values stored in a variable may not be directly returned from a spreadsheet, because they only exist while the calculation script is being processed. If you want to report these values, you need to create members within the database outline, or assign the values from the variables into existing members.

ARRAY

Control Flow Commands

Control Flow commands are used to iterate a set of commands or to restrict the commands' effect to a specified subset (partition) database. They control the calculation flow of a calculation script. The FIX…ENDFIX command can be used to restrict a calculation to a particular member or members, while the LOOP…ENDLOOP command allows you to repeat a command as necessary.

FIX…ENDFIX

LOOP…ENDLOOP

Functional Commands

These commands are used for specific types of calculations. In each case, they start a particular type of calculation, set a particular calculation option, or perform a particular action on data stored in your database. Functional commands are used to perform operations such as calculation, data copying, clearing data, and currency conversion.

AGG
CALC ALL
CALC AVERAGE
CALC DIM
CALC FIRST
CALC LAST
CALC TWOPASS

CCONV
CLEARBLOCK
CLEARDATA
DATACOPY
SET AGGMISSG
SET CACHE
SET CALCHASHTBL

SET CLEARUPDATESTATUS
SETFRMLBOTTOMUP
SET LOCKBLOCK
SET MSG
SET NOTICE
SET UPDATECALC
SET UPTOLOCAL

Conditionals

Conditional commands are used to control the flow of events in formulas. These commands let you to control which formulas are executed within a given member calculation, and test conditions and calculate a formula based on the success or failure of the test.

ENDIF

ELSE

ELSEIF

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

Associate it with a single member
Enclose it 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, performing the following calculations:

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

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

Member Formulas

Member Formulas are used to calculate the default outline format on a custom formula within the script. As with formulas in the database outline, a formula in a calculation script defines mathematical relationships between database members. For example, the following expressions are valid within a calculation script:

"Profit_%";

This would calculate the member using the formula defined in the database outline.

Expenses = Payroll + Marketing;

This formula expresses a simple mathematical relationship, which is used in place of the database outline formula on the Expenses member.

Interdependent Formulas

Hyperion Essbase optimizes calculation performance by calculating formulas for a range of members in the same dimension. However, some formulas require values from members of the same dimension. A good example is that of cash flow, in which the opening inventory is dependent on the closing inventory from the previous month.

For detailed information on and examples of interdependent formulas, see the Database Administrator's Guide.

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

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

If you place the following interdependent 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 associated with the Opening Inventory member, "Opening Inventory" (IF(...)...).

For more detailed information on this interdependent equation example, see the Database Administrator's Guide.