CALCULATE ROW

Creates a named row and associates it with a row name or label. This is similar to declaring a variable. This 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 CALCULATE ROW command, the + operator is assumed.

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

Syntax

1:

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

2:

{ CALCULATE ROW "newRow" [ operator ]}

Parameters

"newRow"

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

columnNo

Optional. Column numbers to which Essbase applies the expression.

expression

Row calculation expression. Member names are not supported.

operator

One of the following mathematical operators:

+ Addition.
- Subtraction.
* Multiplication.
%X%Y X as a percentage of Y.
/ Division.
OFF Turns off the row operator.

If omitted, the default is + (add).

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 practical length of the row name is limited by the width of the column(s) in which it is output. Characters to the right that would overwrite information in the next column are truncated.
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.
Expressions cannot contain member names.
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.

Example

The following samples demonstrate row calculations that you can perform. Note that "Total Sales" in the examples represent a calculated row, not a member name.

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

{ CALCULATE 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":

{ CALCULATE 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:

{ CALCULATE 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":

{ CALCULATE 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":

{ CALCULATE ROW "Ending Sales"7 = "Total Sales" }

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

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

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

{ CALCULATE 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 remain #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.