SAP Knowledge Base Article - Public

1438520 - Incorrect subtraction formula results when both numbers are equal in Crystal Reports

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

  1. In Crystal Reports, create a report off any data source.
      
  2. Add a amount field to Details section.
      
  3. Add Group.
      
  4. Add Running Total for amount field with reset on change of group to Details section.   
      
  5. Create a currency parameter {?Value}         
      
  6. Add a Test formula to Details section                      
                                                                                                                                       
    {#RTotal0}-{?Value}=0                         
                                                                                                                                      
  7.  Add Test 2 formula to Details section                               
                                                                                                                                       
    {#RTotal0}={?Value}                                       
                                                                                                                                       
  8. Refresh the report and enter an existing value for parameter. For example 16794.25        
                                                     
  9.  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