Define a Calculated Column

A calculated column is an expression that uses other columns, functions, and constants to derive new data for the model.

Use calculated columns in your dimension structure

  • to create exception dimensions, or new ways of slicing and dicing your data based on a calculation from existing source columns
  • to create customized date values
  • to produce new measure values

When you use a calculated column as a measure, the value is derived before any rollup takes place. A calculated column is similar to a calculated measure with the following exception: if the Regular timing of your calculated measure is set to Before Rollup, no consolidation occurs. For optimal cube size and run-time performance, calculated columns are preferable to before-rollup calculated measures.

For more information about calculated measures, see Define a Calculated Measure.

Note: If currency conversion is supported by your OLAP reporting component, the calculated column is initially calculated using the default (base) currency. After that, conversion occurs dynamically, followed by rollup.

Procedure

  1. In the Data Sources list, click the data source to make it active and then, from the Edit menu, click Insert Column.
  2. In the Column name box, enter a name for the new column.
  3. In the Column type box, click Calculated and click Calculation.

    If you have not specified a Data class, you are prompted to specify whether the column consists of text, date, or numeric data.

  4. In the left pane of the expression editor, expand the Columns and Functions folders as needed, select each parameter you want to use, and click the right-arrow button to insert it into the Expression definition box in the right pane of the editor.
    Tip: You can also double-click or click and drag the parameter to add it to the calculation.

    The list of available functions varies with the Data class. For example, the first-of-month and today functions are only available for the Date data class.

    For more information, see IBM Cognos Transformer Expression Editor.

  5. When the expression is complete, click OK.