1620798 - How to evaluate null field values in formulas or Record Selection in Crystal Reports?

• When previewing the report built on a database field contains values and null values, a list of values and blank values appears in the Details section of the report in Crystal Reports.
• But, Null Field values does not appear in formula fields.

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

1. In Crystal Reports, create a report based on any data source.
     
2. Add a Number field, {YourDatabaseTable.NumberField}, to the Details section of the report.
   
   Field values contained in the database:
   1
   <Null>
   <Null>
   2
   3
      
   Field values that appear in Crystal Reports:
   
   1
   <blank space>
   <blank space>
   2
   3
   
   NOTE:
   The <blank space> in Crystal Reports represents the null values from the database field.
   
   
   
3. Add the following record selection criteria to the report: {YourDatabaseTable.NumberField} < 3
     
4. Upon adding the record selection criteria, the values that appear on the report are not what you expected.
   
   Expected field values based on record selection criteria
   
   1
   <blank space>
   <blank space>
   2
   
   Field values that appear in CR based on the record selection criteria
   
   1
   2

• The record selection criteria do not evaluate the null values. Null values in the Number field are not evaluated as zero (0) values by default.

• To return the expected data set, you can either:
• Convert NULL values to default; or
• Evaluate the NULL within the Record Selection Formula, or Formulas
     
    
• Convert the null value to the default value of their data type.
  
  Example of default values by Data Type:
• The null values in a Number field are converted to a zero (0) number value.
• The null values in a String (Text) field are converted to an empty string ("") value.
• The null values in a Date field are converted to zero (0, 0, 0) date values.
• The null values in a Date-time field are converted to zero (0, 0, 0, 0, 0, 0) date-time values.
  
  
• To convert the NULL values to default :
1. In Crystal Reports under the menu "File", select "Report Options"
2. Select the 'Convert NULL Field Values to Default' check box
3. Click 'OK'
4. Save the report.

• Evaluate the null value within record selection criteria or within formulas using the IsNull function.
    
1. In Crystal Reports, open the report.
2. Create formula or record selection criteria.
3. Use the IsNull function at the beginning of the formula or record selection criteria.
4. If applicable, specify a value or action if the IsNull function evaluates a null record.

Null Values or Null field values, CR , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To

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