Insert a Query Calculation

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.

Note: 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 ~ -- || - $ ¢ £ ¬.

Not all data sources support functions the same way. The data modeler can set a quality of service indicator on functions to give a visual clue about the behavior of the functions. Report authors can use the quality of service indicators to determine which functions to use in a report. The quality of service indicators are:

not available (X)
The function is not available for any data source in the package.
limited availability (!!)
The function is not available for some data sources in the package.
limited support (!)
The function is available for all data sources in the package but is not naturally supported for that data source. IBM® Cognos® Business Intelligence uses a local approximation for that function. Because an approximation is used, performance can be poor and the results may not be what you expect.
unconstrained (check mark)
The function is available for all data sources.

When inserting literal strings in an expression, you must enclose the string in single quotation marks. If the string contains a quotation mark, it must be escaped. For example, if you want to insert the string ab'c, you must type 'ab''c'.

Procedure

From the Toolbox tab , drag Query Calculation to the report.
Type a meaningful name for your expression in the Name box.
For example, if you are calculating the difference between 2012 revenue and 2011 revenue, you could name your expression 2012 - 2011 Revenue.
In the Available Components pane, 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 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.
- 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 , double-click the parameter maps, session parameters, or macro functions you want to add to the macro expression.
  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.