Using Dimensional Calculations

Insert a calculation to make your report more meaningful by deriving additional information from the data source. For example, you create an invoice report, and you want to see the total sale amount for each product ordered. Create a calculated column that multiplies the product price by the quantity ordered.

You build calculations in the expression editor using functions.

If a calculation is used in multiple reports, or by different report authors, ask your modeler to create the calculation as a standalone object in the model and include it in the relevant package.

Calculation Solve Order

When calculations in the rows and columns of a report intersect, calculations are performed in the following order:

addition or subtraction
multiplication or division
aggregation (rollup)
remaining arithmetic functions: absolute, round, round down, average, minimum, maximum, medium, count
percentage, % difference (growth) or % of total
rank, quartile, quantile, or percentile

If both calculations have the same precedence, for example, if they are both additions, then the row calculation takes precedence.

If you have the necessary permissions, you can open your report in IBM® Cognos® Report Studio and override the order of precedence by changing the solve order property for a data item. For more information, see the Report Studio User Guide.

Limitations of Calculations

Use only the expressions and functions available in Cognos Workspace Advanced, and follow their syntax. For best results, do not build calculations or summaries over other calculations that were authored in Report Studio.

Minimal checking exists for calculations. If your calculation uses an invalid expression, your report results may contain unexpected values.

In addition, you should define member summaries as follows:

summary_function (currentMeasure within set set_reference)

where set_reference is a level or set inserted from the Source tab .

Unless otherwise required, summary_function should be the aggregate function. If you use an explicit summary function, you may encounter problems with measures (such as profit margin, distinct count, and so on) that have complex rollup values, and/or scenario or account dimension members that do not roll up.

Know your data, and confirm with the owner of the cube where overriding the automatic aggregation is safe.

Because of the these limitations, summaries of calculations may not provide reliable values. For convenience, you may need to build reports where row summaries and calculated member columns intersect. In such reports, these intersections may contain unexpected values. In contrast, row calculations intersecting with column aggregates using the aggregate function are safe because the calculation is performed on the reliably summarized values.

If you need to create more complex expressions, use Report Studio.