{CALCULATE ROW}

Command

The CALCULATE ROW command creates a named report row and performs on-the-fly calculations.

Syntax

{ CALCULATE ROW "newRow" [ columnNo ] = expression}

{ CALCULATE ROW "newRow" [ operator]}

"newRow" Name of a new row, enclosed by quotation marks, that was declared with CALCROW, SAVEROW, or SAVEANDOUTPUT.

columnNo Optional. Designation of column numbers to which Hyperion Essbase selects and applies the expression.

expression Any valid row calculation expression.

operator Any valid mathematical operator. The following mathematical operators are supported in row calculations:
+ Addition operator.
- Subtraction operator.
* Multiplication operator.
% X % Y Evaluates X as a percentage
of Y .
/ Division operator.
OFF Turns off the row operator.

Default Value

If an operation or equation is not specified, the default is + (add).

Description

This command creates a named report row and performs on the fly calculations. CALCULATE ROW creates the row and associates it with a row name or label. This is similar to declaring a variable. The CALCULATE ROW command can also specify an operation (+, _, *, /, or OFF) as an equation consisting of constants, other calculated rows, and operators.

Equations are evaluated at the time of declaration. If an operator is specified, subsequent output rows have the operator applied to them with the result stored in the calculated row.

This is useful for aggregating a series of rows to obtain a subtotal or total. The operator can be reset at any point with SETROWOP. If neither an equation nor an operator are specified in the CALC ROW command, the + operator is assumed.

SETROWOP defines a calculation operator to be applied to all subsequent output data rows. You can display the calculation results in the newly created row with the PRINTROW command.

Example

The following samples demonstrate row calculations that you can perform:

To compute "Avg Sales" by dividing by the constant 2, use:

{ CALC ROW "Avg Sales" = "Total Sales" / 2 }

To multiply the first six data columns of the calculated row "Total Sales" by the six factors and store the result in the calculated row "Factored Sales," use:

{ CALC ROW "Factored Sales" = "Total Sales" * [1.0 1.3 1.9 2.3 3.0 3.7 ] }

To store five factors in the first five columns of "Factors," for use in later calculated row computations and/or PRINTROW output, use:

{ CALC ROW "Factors" = [ 1.3 2.6 3.1 2.3 5 ] }

To store the value from the seventh column of "Total Sales," multiplied by 1000, in every column of the calculated row "Ending Sales," use:

{ CALC ROW "Ending Sales" = "Total Sales" 7 * 1000 }

To set the value in column 7 of "Ending Sales" to the corresponding value from the row "Total Sales," use:
{ CALC ROW "Ending Sales"7 = "Total Sales" }

"Total" refers to itself in this calculation and divides itself by 1000:

{ CALC ROW "Total" = "Total" / 1000. }

To show a variety of operations used in one expression, use an expression like this:

{ CALC ROW "xyz" = [ 11 12.3 -6 ] / 7 + "abc"2 % 4300. + 10 }

This expression divides the three values in the array by the constant 7 (if there are currently more than three data columns, the extra columns remains #Missing), adds the value from column 2 of "abc" to every column, and computes the resulting row's values as percentages of the constant 4300, and adds the constant 10 to all columns, storing the final result in "xyz". Note that if there are more than three data columns, the result in the extra columns is 10, since prior to the last operation, they were #Missing.

Notes

Row name can have multiple levels, separated by the tilde (~} character, for use when there is more than one row name column in the report. For example, the calculated row name "Actual~Sales", if output (using PRINTROW) in a report with at least two row name columns, results in Sales in the right-most row name column, and Actual in the row name column to its left. If a multiple level row-name is used in a report with only one row-name column, only the rightmost part of the name appears in the report.
The length of the row name may be up to several thousand characters, but its practical length is limited by the width of the column(s) in which it is being output. Characters to the right that would overwrite information in the next column are truncated on output.
If you need to store a multiple-value array into a calculated row prior to the point where you have defined your columns (with your column dimension member selections), you can use NS to pre-allocate a larger number of columns with which to work with. If you supply fewer values than there are data columns, the operation using the array stops after the last array value and there are no changes to the remaining columns based on that operator. If the extra columns are currently missing, they stay missing; if they are non-missing, they retain their current values.
Expressions are always computed from left to right. Parentheses may not be used for grouping.
Commands that designate columns must use valid data column numbers, as determined by the original order of the columns.
All operators in an expression must be preceded and followed with a single space.
Integer and floating point constants are supported in expressions as single entries or members of an array.
Row calculations are created with three commands: CALCULATE ROW, SETROWOP, and PRINTROW.

"newRow"	Name of a new row, enclosed by quotation marks, that was declared with CALCROW, SAVEROW, or SAVEANDOUTPUT.
columnNo	Optional. Designation of column numbers to which Hyperion Essbase selects and applies the expression.
expression	Any valid row calculation expression.
operator	Any valid mathematical operator. The following mathematical operators are supported in row calculations: + Addition operator. - Subtraction operator. * Multiplication operator. % X % Y Evaluates X as a percentage of Y . / Division operator. OFF Turns off the row operator.