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.
In the Data Sources list, click the data source to make it active and then, from the Edit menu, click Insert Column.
In the Column name box, enter a name for the new column.
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.
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 8 Transformer Expression Editor.
When the expression is complete, click OK.
You want to use a calculated column to set up an exception dimension based on a calculation from an existing source column.
From the REVENUE and COST columns, you create a calculated column MARGIN_RANGE to provide new insights into the data. You define the margin ranges for Low, Medium, and High based on the Gross Margin formula (Revenue-Cost)/Revenue, using the following if-then-else statement:
if ("Gross Margin" < 0.50) then ('Low') else (if ("Gross Margin" > 0.70) then ('High') else ('Medium'))…
Drag the MARGIN_RANGE column to the dimension line of the Dimension Map to form an exception dimension.
You have now segmented your data into a set of useful analytical groupings.
You can add a calculated column that consists of numeric data to be used in measure allocation.
Suppose you have two sources of data about a professional sports team. The first source contains data for individual players, and the second contains current and forecast salary figures for each team.
You add a calculated column to the second data source representing each team's forecast salary based on a 13% increase for the next year, such as
"CURRENT_SALARY" * 1.13
You drag the calculated column to the Measures list, which makes these figures available for allocation to the player level. For more information about how to allocate measures, see Allocating Measures.
Your OLAP analysis users can now see each player's salary forecast.