The CALCULATE COLUMN command lets you create a report column, perform on-the-fly calculations, and display the calculation results in the newly created column.
Table 96 lists column calculation commands:
Table 96. Report Writer Commands: Column Calculation
CALCULATE COLUMN adds up to 499 ad hoc column calculations to a report. Each new calculated column is appended to the right of the existing columns in the order in which it is created and is given the next available column number. These columns calculate the sum of data across a range of columns or an arithmetic expression composed of simple mathematical operators.
The CALCULATE COLUMN command supports standard mathematical operations.
If you use the same name for multiple columns, Essbase creates only the last column specified in the CALCULATE COLUMN command. Use a leading space with the second name (and two leading spaces with the third name, and so on) to create a unique column name.
Alternately, you can add descriptive text far enough to the right that it is truncated to the column width. You can, for example, use the names Q1 Actual and Q1 Budget to distinguish similar column names without affecting the appearance of the report. Column names are printed with right justification until the column header space is filled. Excess characters are then truncated to the right.
Divide lengthy column name labels into multiple lines. The maximum number of lines across which you can divide a label is equal to the number of column dimensions designated in the report specification. To break a column name, insert a tilde (~) in the name at the point where you want the break. You must also specify at least two members for each column dimension to use the maximum number of lines.
{CALCULATE COLUMN "Year to Date~Actual Total" = 1 : 2} {CALCULATE COLUMN "Year to Date~Budget Total" = 3 : 4}
Sales East Actual Year to Date Budget Year to Date Jan Feb Actual Total Jan Feb Budget Total ===== ====== ============= ====== ====== ============= 400-10 562 560 1,122 580 580 1,702 400-20 219 243 462 230 260 722 400-30 432 469 901 440 490 1,391
As a rule, in symmetric reports, if a calculated column name has fewer levels than the number of column dimensions, the previous member (to the left) of each of the column dimensions, above the top level supplied in the calculated column name, is attributed to the calculated column. If normal PYRAMIDHEADERS mode is in use, the centering of those higher-level column members shifts to the right to include the calculated column or columns. Column header members on the same level as calculated column names are not applied to the new calculated column or columns, and their centering does not shift.
If BLOCKHEADERS mode is in use; that is, if every member applying to a column is repeated above that column, the same rules apply, except that instead of shifting column header member centering, they are repeated in the appropriate higher levels of the calculated column name.
Asymmetric reports do not have groups of columns that share a member property. These reports still allow multiple-level column names up to the number of column levels defined, but member properties from preceding columns are not automatically shared and used for those levels that are undefined.
If there are fewer column header dimensions than the number of levels that you want, you can create multiline column labels. In this case, use TEXT, STARTHEADING, ENDHEADING, and other formatting commands to create a custom heading.