Report Studio

Unexplained Discrepancies in Number Calculations

You might find unexplained discrepancies in number calculations due to round-off errors.

For example:

• You run regression tests and find difference in numbers. They are different only because of the rounding off of decimal places.
• You choose to not display zeros in reports, but the zeros are displayed anyway because there are decimal places (0.0000000000000000426, for example) that are rounded off to zero in reports.

Round-off problems are not specific to Cognos. They can occur in any environment where rounding off occurs

• Binary Round-Off Errors

  Discrepancies in calculations might occur due to binary round-off errors. For example, if the number 1.1 is represented as a binary floating point number and your report format includes a large number of decimal places, the number 1.1 might actually be something like 1.09999999999997.

  If your report is formatted to use only one decimal point, decimal round-off takes place, compensating for the binary round-off. So the number appears to be 1.1 when it is really 1.09999999999997. When the number is used in calculations, you might get round-off errors. For example, Microsoft Excel calculations use binary numbers (without rounding off decimal places) but formatting in reports shows rounded off decimal places, which can create small discrepancies.

• Division Round-Off Errors

  Calculations that involve division typically incur round-off errors, regardless of how the numbers are represented. Examples of such calculations are Average and Percent of Base.

Design Guidelines to Minimize Round-Off Effect

The best solution is to change the underlying database schema or cube model but that may not always be possible. Another solution is to minimize the round-off effect by following these guidelines when authoring reports:

• In reports, avoid division whenever possible. When division is unavoidable, try to do it as late as possible in the calculation process. For example, instead of Total([Revenue]/1000), use Total([Revenue])/1000.
• When doing comparisions, add a margin to allow for round-off. For example, you may want [Profit %] to be a fractional value formatted as a percentage with no decimals. However, the filter [Profit %]<>0 (or [Profit %] NOT BETWEEN 0 and 0 ) rejects zero values and may still return values that appear to be 0% after formatting.
  To avoid this, filter in one of these two ways:
  - [Profile %] NOT BETWEEN -0.005 and 0.005
  - ([Profit %] <- 0.005) OR ([Profit %]> 0.005)
  
  Note that 0.005 is equivalent to 0.5%, which displays as either 0% or 1%, depending on floating point precision losses.
  
  In some cases, you may prefer to control round-off errors by rounding values explicitly. For example, instead of [Profit %], use round([Profit %],2).

• HRESULT=DB_E_CANTCONVERTVALUE Error When Filtering on a_make_timestamp Column

  You cannot filter on a_make_timestamp column, and the following error messages appear:

  UDA-SQL-0114 The cursor supplied to the operation "sqlOpenResult" is inactive

  UDA-SQL-0206 The OLEDB driver returned the following value: HRESULT=DB_E_CANTCONVERTVALUE

  RSV-SRV-0025 Unable to execute this request

  The solution is to apply the filter after aggregation and not before

Related Topics:

• Problems Creating Reports—Troubleshooting common problems when creating Report Studio reports.
• Problems Editing Reports—Troubleshooting common problems when editing Report Studio reports.
• Problems Running Reports—Troubleshooting common problems when running Report Studio reports.
• Problems When Drilling Through—Troubleshooting common problems when drilling through in Report Studio reports.

Copyright © 2018 Daptiv Solutions, LLC. All Rights Reserved