SAP Knowledge Base Article - Public

1199467 - When there is null data, formula returns different results than expected in Crystal Reports

Symptom

  • Formula not displaying the expected result.
  • Formula output is blank, when expecting a specific value, or formatting, when the data set contains null values.
  • When refreshing a report in Crystal Reports that contains null data, the result of the Formula, or Record Selection Formula, or Conditional Object Formatting is incorrect, even when using the function isNull in the formula.

Environment

  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016
  • SAP Crystal Reports 2020     

Reproducing the Issue

  1. In Crystal Reports, create a report based on any data source.
       
  2. Create a formula that that verify if a database field equal to a specific value, or if it is null. The formula will look like:
     
     If {Database.Field} = 0 or IsNull({Database.Field}) Then
         "No Data"
     Else
         "Data"
      
  3. Insert the formula on the report.
       
  4. When refreshing the report, notice that even when there is no data, the formula show blank in some case, despite the expectation is to display a specific value when it is null.

Cause

  • When a database field used in a formula contains a NULL value, it will exit out of the formula, and therefore the result on the report will be different than expected. 
       
  • For example, in the following formula, if there is a NULL value for the database field, it will exit the code at: {Database.Field} = 0 
    Therefore the entire formula will not be evaluated, and will return nothing. ( blank output )
               
     If {Database.Field} = 0 or IsNull({Database.Field}) Then
         "No Data"
     Else
         "Data"  

Resolution

  • To ensure the formula is always evaluated when there is null data, always use the function: IsNull, as the first comparison for the database field.
      
    For example: If we keep the same example, the formula will now be:   
      
       If  IsNull({Database.Field}) or {Database.Field} = 0  Then
            "No Data"
       Else
            "Data"
      
       
  • An alternative to using the function IsNull in a formula is to check the report option "Convert Database NULL Values to Default". This option convert NULL string value to an empty string, and NULL numeric value to zero, therefore there is no need to use the IsNull function in any formulas for the report. To check this report option:
          
    1. In Crystal Reports, open the report.
        
    2. Under the menu "File", select "Report Options..."
        
    3. In the "Report Options" window, check the report option: "Convert Database NULL Values to Default"
          
    4. Click "OK"
          
    5. Save the report.

Keywords

IsNull, NULL values, formula, selection formula, different results , 4647423, CR , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To

Product

SAP Crystal Reports 2013 ; SAP Crystal Reports 2016 ; SAP Crystal Reports 2020