Calculation commands in Essbase is divided into groups based on Types as:
Data Declarations:
ARRAY:
Syntax:
ARRAY arrayVariableName [dimName] = { constList};
Example:
ARRAY discount[Scenario];
yields an array of 4 entries, with the values 1 through 4 entered in those four entries.
ARRAY discount[Scenario] = {1, 2, 3, 4};
ARRAY discount[Scenario], tmpProduct[Product];
yields two arrays:
1. discount, corresponding to Scenario and containing four members
2. tmpProduct, corresponding to Product and containing nine members
VAR:
Syntax:
VAR varName [= value];
Example
VAR Target = 1200;
VAR Break1, Break2, Break3;
Control Flow Commands:
Control Flow commands are used to iterate a set of commands or to restrict the commands' effect to a subset (partition) database. They control the flow of a calculation script. The FIX…ENDFIX and EXCLUDE…ENDEXCLUDE commands restrict calculations to specified members. The LOOP…ENDLOOP command enables repetition.
EXCLUDE…ENDEXCLUDE
Syntax:
EXCLUDE (Mbrs)
COMMANDS ;
ENDEXCLUDE
Example:
The following example excludes calculations on the children of Qtr4, enabling calculation of other quarters in the Year dimension.
EXCLUDE (@CHILDREN(Qtr4))
CALC DIM (Year)
ENDEXCLUDE
FIX…ENDFIX
Syntax:
FIX (fixMbrs)
COMMANDS ;
ENDFIX
Example:
FIX (Budget)
CALC DIM (Year, Measures, Product, Market);
ENDFIX
FIX (Budget, Jan, Feb, Mar, @DESCENDANTS(Profit))
CALC DIM (Product, Market);
ENDFIX
LOOP...ENDLOOP
Syntax:
LOOP (integer, [break])
COMMANDS ;
ENDLOOP
Example:
In this example, the LOOP command finds a solution for Profit and Commission. This operation is done as a loop because Profit and Commission are interdependent: Profit is needed to evaluate Commission, and Commission is needed to calculate Profit. This example thus provides a model for solving simultaneous formulas.
FIX("New York",Camera,Actual,Mar)
LOOP(30)
Commission = Profit * .15;
Profit = Margin - "Total Expenses" - Commission;
ENDLOOP;
ENDFIX
Member Formulas:
"Profit_%"
Specifying a member name with a formula defined in the outline calculates the member using its formula.
Expenses = Payroll + Marketing;
The above formula expresses a simple mathematical relationship, which is used in place of the database outline formula on the Expenses member.
Interdependent Formulas
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.
Functional and Conditional commands are Discussed in later post.
- Data Declarations
- Control Flow
- Functional
- Conditionals and
- Member Formulas.
Data Declarations:
These are used to declare, and set the initial values of, temporary variables. The values stored in a variable are not returned in queries, 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:
Typically, arrays are used to temporarily store variables as part of a member formula. The variables cease to exist after the calculation script ends. The size of the array variable is determined by the corresponding dimension (e.g., if dimension Period has 12 members, ARRAY Discount[Period] has 12 members). To create multiple arrays simultaneously, separate the array declarations in the ARRAY command with commas, as shown in the Example. Do not use quotation marks (") in variables
Syntax:
ARRAY arrayVariableName [dimName] = { constList};
Example:
ARRAY discount[Scenario];
yields an array of 4 entries, with the values 1 through 4 entered in those four entries.
ARRAY discount[Scenario] = {1, 2, 3, 4};
ARRAY discount[Scenario], tmpProduct[Product];
yields two arrays:
1. discount, corresponding to Scenario and containing four members
2. tmpProduct, corresponding to Product and containing nine members
VAR:
Declares a temporary variable that contains a single value. We can also use a single VAR command to declare multiple variables by supplying a comma-delimited list of variable names. The name of the variable cannot duplicate a database member name. If a value is not declared, it is set to #MISSING. VAR commands can only be assigned values within a member calculation or when VAR is declared.
VAR varName [= value];
Example
VAR Target = 1200;
VAR Break1, Break2, Break3;
Control Flow Commands:
Control Flow commands are used to iterate a set of commands or to restrict the commands' effect to a subset (partition) database. They control the flow of a calculation script. The FIX…ENDFIX and EXCLUDE…ENDEXCLUDE commands restrict calculations to specified members. The LOOP…ENDLOOP command enables repetition.
- EXCLUDE…ENDEXCLUDE
- FIX…ENDFIX
- LOOP…ENDLOOP
EXCLUDE…ENDEXCLUDE
The EXCLUDE command allows you to define a fixed range of members which are not affected by the associated commands. The EXCLUDE ENDEXCLUDE calculation block defines commands which are not to be performed on all other than those specified by the EXCLUDE statement that starts the block. The ENDEXCLUDE command ends an EXCLUDE command block. Specifying members that should not be calculated in an EXCLUDE..ENDEXCLUDE command may be simpler than specifying a complex combination of member names in a FIX…ENDFIX command. Use EXCLUDE…ENDEXCLUDE commands only within calculation scripts, not in outline member formulas. You can include EXCLUDE commands within FIX command blocks.
Syntax:
EXCLUDE (Mbrs)
COMMANDS ;
ENDEXCLUDE
Example:
The following example excludes calculations on the children of Qtr4, enabling calculation of other quarters in the Year dimension.
EXCLUDE (@CHILDREN(Qtr4))
CALC DIM (Year)
ENDEXCLUDE
FIX…ENDFIX
The FIX…ENDFIX command block restricts database calculations to a subset of the database. All commands nested between the FIX and ENDFIX statements are restricted to the specified database subset. The FIX command is a block command that allows you to define a fixed range of dimensions or members to which the associated commands are restricted. The FIX command is often used to calculate a subset of the database. You cannot use a FIX statement on a dimension if it is a subset of a dimension that you calculate within the FIX statement. For example you could not use Market "New Mkt" in a FIX statement if you calculate all of Market within the FIX statement.
FIX (fixMbrs)
COMMANDS ;
ENDFIX
Example:
FIX (Budget)
CALC DIM (Year, Measures, Product, Market);
ENDFIX
FIX (Budget, Jan, Feb, Mar, @DESCENDANTS(Profit))
CALC DIM (Product, Market);
ENDFIX
LOOP...ENDLOOP
The LOOP...ENDLOOP command block specifies the number of times to iterate calculations. All commands between the LOOP and ENDLOOP statements are performed the number of times that you specify. LOOP is a block command that defines a block of commands for repeated execution. As with the FIX command, you can nest LOOP statements if necessary.
Syntax:
LOOP (integer, [break])
COMMANDS ;
ENDLOOP
Example:
In this example, the LOOP command finds a solution for Profit and Commission. This operation is done as a loop because Profit and Commission are interdependent: Profit is needed to evaluate Commission, and Commission is needed to calculate Profit. This example thus provides a model for solving simultaneous formulas.
FIX("New York",Camera,Actual,Mar)
LOOP(30)
Commission = Profit * .15;
Profit = Margin - "Total Expenses" - Commission;
ENDLOOP;
ENDFIX
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_%"
Specifying a member name with a formula defined in the outline calculates the member using its formula.
Expenses = Payroll + Marketing;
The above formula expresses a simple mathematical relationship, which is used in place of the database outline formula on the Expenses member.
Interdependent Formulas
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.
Functional and Conditional commands are Discussed in later post.
