Creating 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® Workspace Advanced, 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, [2005] - [2004] is a calculated member and [Revenue] - [Planned Revenue] is a calculated measure.
Use set expressions to create calculated sets of members. For example, children ([2004]) is a set expression that displays the child members of 2004.
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.

Assigning a Hierarchy or Dimension

You must assign each query calculation to a hierarchy or dimension. For best results, 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 regardless of its Products context as it appears in the report. Therefore,

the calculation ([Camping Equipment]-[Mountaineering 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 is not reliable.

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.

Quality of Service Indicators

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 as follows:

not available (X)
This 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. This approximation may cause poor performance and unexpected results.
unconstrained (no symbol)
The function is available for all data sources.

Using Quotation Marks in Literal Strings

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 modified. For example, if you want to insert the string ab'c, you must type 'ab''c'.