Adding a row or column of calculated values

When working with dimensional data, insert a query calculation into your report to add a new row or column with values that are based on a calculation. For example, you create a query calculation 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.

In IBM® Cognos® Analytics - Reporting you can create calculations based on the following types of expressions:

Set expressions: Use set expressions to create calculated sets of members. For example, children ([previousyear]) is a set expression that displays the child members of the previous year.
You must base each set expression on a hierarchy and the set expression must contain only members from this hierarchy.
Value expressions: Create value expressions when you want to insert a string, number, date, or interval value.
Member or tuple-based value expressions: You can create calculated members or calculated measures where the expression is a member or a tuple-based (as opposed to property-based) value expression. For example, [currentyear] - [previousyear] is a calculated member and [Revenue] - [Planned Revenue] is a calculated measure.
You can also create a calculation that uses an intersection (tuple) that you already defined. For more information about creating tuple-based expressions and calculations, see Adding information based on multiple members.

If you are using an IBM Db2® data source, the subtract operator is invalid if you combine the datatypes timestamp2 and packed decimal.

When creating an expression that will be used in a double-byte environment, such as Japanese, the only special characters that will work are ASCII-7 and ~ -- || - $ ¢ £ ¬.

Procedure

Click the Toolbox icon and drag Query calculation to the report.
Click the type of calculation you want to create.

For some types of calculations, you are also prompted to select the hierarchy or measure dimension that contains the data that is the focus of your calculation.
In the Name box, type a meaningful name for the calculation.

For example, if you are calculating the difference between the revenues of the current year and the previous year, you could name your expression Revenue Difference.
In the Available Components pane, define the calculation:
1. Specify how you want to view the available data items in your data package by clicking the View members tree button or the View metadata tree button .
2. Optional: Add additional items to the calculation
  - To add data items that are not shown in the report, click the Source tab , then double-click the data items.
  - To add data items that are in the report but not necessarily in the model, such as calculations, click the Data items tab , then double-click the data items.
  - To add data items from a specific query, click the Queries tab , then double-click the data items.
  - To add functions, summaries, and operators, click the Functions tab , then double-click the elements.
  - To add a value that is derived from a parameter, click the Parameters tab , then double-click a parameter.
    Parameters define prompts, drill-through reports, and master detail relationships.
  - To add a macro, click the Macros tab , then drag the parameter maps, session parameters, or functions that you want to add to the macro.
    Tip: Use Insert macro block to get you started with creating the macro expression. Insert macro block inserts number signs in the expression. Make sure that the macro expression you create is between the number signs.
You can also type the calculation directly in the Expression Definition box.

When typing date values, ensure that the date format is correct for your database type.

To copy and paste expression components in the Expression Definition pane, you can use the Copy button and the Paste button .
Click the Validate button .