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 2011
  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016
           
  • SAP Crystal Reports for Enterprise 4.0
  • SAP Crystal Reports for Enterprise 4.1
  • SAP Crystal Reports for Enterprise 4.2 

Reproducing the Issue

  1. In Crystal Reports, create a report off 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.
           
  • If we keep the same example, the formula will now be:
        
     If  IsNull({Database.Field}) or {Database.Field} = 0  Then
         "No Data"
     Else
         "Data"

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 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016