IBM Cognos BI report has calculation between columns. Resulted values get different number formatting under CQM and DQM.
Under DQM simple multiply calculation 'Calc1' defined as 'Qi1 * Qi2' produces result value with decimal format, as: '2.00' whereas under CQM, same report produces value '2'.
Design change. In DQM, when local processing takes place, the resulting value will be formatted using different rules than the usual relational list values. Under DQM values will be formatted using a format service designed for OLAP queries where 3 decimal representation is by default.
In this example assume that of two query items, only second 'Qi2' query item value gets locally-processed and resulted values for that data item contain 3 decimals. As the data item 'Calc' is a multiplication involving 'Qi2' will it also inherit the decimalSize=3 formatting property under DQM.
Diagnosing the problem
Model is published as CQM and DQM, same report specification is set to run against both type of packages and no number formatting is applied in model or in report for Items that are involved.
Resolving the problem
1) Under DQM, use the _round() function for calculation.
2) Or set the decimal size directly in the report layout in Report Studio:
- Right-click on the column in question and choose Style --> Data format...
- For "Format type", select "Number" and for "No. of Decimal Places" type in a number (such as 2).
- Click OK and run the report.