Symptom
- Result of a formula not equal to zero.
- Numeric calculation result shows incorrect decimal values.
- A simple subtraction formula does not return zero when both values are equal in Crystal Reports.
- For example: The expected result of 5 - 5 is zero, but if we compare the result of the formula to zero it is not equal in Crystal Reports, why?
Environment
- Crystal Reports XI R2
- SAP Crystal Reports 2008
- SAP Crystal Reports 2011
- SAP Crystal Reports 2013
- SAP Crystal Reports 2016
Reproducing the Issue
- In Crystal Reports, create a report off any data source.
- Add a amount field to Details section.
- Add Group.
- Add Running Total for amount field with reset on change of group to Details section.
- Create a currency parameter {?Value}
- Add a Test formula to Details section
{#RTotal0}-{?Value}=0
- Add Test 2 formula to Details section
{#RTotal0}={?Value}
- Refresh the report and enter an existing value for parameter. For example 16794.25
- See the relevant record returns FALSE for Test and TRUE for Test 2
It means A=B, but for the same record A-B is different than zero
Cause
- A 10 -12 fractional component has been added to the summary. As the result subtraction is not equal to zero.
- The reason this is happening is because real numbers in computer are represented in binary float point format. So some decimal number such as 0.1 can’t be represented exactly, so it is approximately 1.10011001100110011001101 × 2-4, and when performing a calculation like: subtraction, addition, multiplication and division, precision can be lost and result in values that appear to be equal are not when performing a calculation on it.
- For more information consult: What Every Computer Scientist Should Know About Floating-Point Arithmetic
Resolution
- The solution is to use the ROUND function to keep the desired precision and eliminate a fractional component
Example:
Round({@Formula}, 9) // round the summary with 10 -9 accuracy
Keywords
CR, total, summary, zero, incorrect, fractional, decimal , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem
Product
Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016 ; SAP Crystal Reports XI R2