Insert a Query Calculation

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® Report Studio, you can create the following types of calculations:

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, [2013] - [2012] is a calculated member and [Revenue] - [Planned Revenue] is a calculated measure.
Use set expressions to create calculated sets of members. For example, children ([2012]) is a set expression that displays the child members of 2012.
You must base each set expression on a hierarchy and the set expression must contain only members from this hierarchy.
Create value expressions when you want to insert a string, number, date, or interval value.

You can also create a calculation that uses an intersection (tuple) that you already defined.

If you are using a 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

From the Toolbox tab , drag Query Calculation to the report.
In the Name box, type a meaningful name for your calculation.
For example, if you are calculating the difference between 2012 revenue and 2011 revenue, you could name your expression 2012 - 2011 Revenue.
Click the type of calculation you want to create and select the hierarchy that contains the data that is the focus of your calculation.
In the Available Components pane, define the calculation:
- Specify how you want to view the available data items in your data package by clicking the view member tree button or the view package tree button .
- 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 data items from a specific query, on the queries tab , double-click data items.
- To add functions, summaries, and operators, on the functions tab , double-click elements.
  Tip: To filter the visible functions, summaries, and operators in the Available Components pane, click the filter button and filter by the function type, what the function returns, or what the function acts on.
- To add a value that is derived from a parameter, on the parameters tab , double-click a parameter.
  Parameters define prompts, drill-through reports, and master detail relationships.
- To add a macro, on the macros tab , drag the parameter maps, session parameters, or functions 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 .

For information about specific functions, see Using the expression editor.
Click the validate button .
Any validation errors appear on the Errors tab of the Information pane.

Tip: To copy and paste expression components in the Expression Definition pane, you can use the copy button and the paste button .