Insert a Calculated Member or Measure

When working with dimensional data, insert a calculated member or measure into your report to add a new row or column with values that are based on a calculation. For example, you create a calculated measure named Euros that converts dollars to euros by multiplying an existing dollar measure by a conversion rate. Euros can then be displayed to end users in a separate row or column.

Insert a calculated measure when the focus of the calculation is one or more measures and when you want the calculation to behave like a measure or to replace a measure. The expression used to define the calculation must not depend on the current measure context.

Insert a calculated member when you want the calculation to behave like a member or to replace a member. In this case, you must choose a hierarchy. The value expression must not depend on the current member context.

You use the expression editor to define the calculation. You can also create a calculation that uses an intersection (tuple) that you already defined .

Assigning a Hierarchy or Dimension

You must assign each calculated member or measure to a hierarchy or dimension. We recommend that you select the hierarchy or dimension upon which your calculation focuses. For example, if your calculated member is based on years, select the Time hierarchy. If you create a calculated measure, select the Measures dimension.

Select only a hierarchy that cannot affect the value of the calculation. For example, the hierarchy Camping Equipment has the same value no matter what Products context it appears in the report. Therefore,

• the calculation ([Camping Equipment]-[Mountaneering Equipment]) has a well-defined meaning only in the Products hierarchy, so select that hierarchy.

• the calculation tuple([Revenue], [Camping Equipment]) can be assigned to either the Products or Measures hierarchy, as it is not affected by either context.

• the calculation ([Revenue] - [Camping Equipment]) does not have a well-defined meaning in any hierarchy, and therefore cannot be used reliably at all.

Automatic Database Aggregation

You can use automatic database aggregation only when using IBM Cognos PowerCubes and Microsoft SQL Server 2005 Analysis Services (SSAS) data sources.

Steps

1. In the Insertable Objects pane, click the Toolbox tab .

2. Drag Calculated Member or Calculated Measure to the report.

3. In the Name box, type a name for the calculation.

4. In the Hierarchy or Dimension box, select the hierarchy that contains the measure or member that is the focus of this calculated member or measure.

5. In the Available Components box, define the calculation:

   • To add data items that are not shown in the report, on the Source tab , double-click the data items.

   • To add data items that are in the report but not necessarily in the model, such as calculations, on the Data Items tab , double-click the data items.

   • To add functions, summaries, and operators, on the Functions tab , double-click elements.

   Tip: You can also type the calculation directly in the Expression Definition box. When typing date values, use the YYYY-MM-DD format.

6. Click the validate expression button .