1245596 - Record Selection formula with “OR” condition does not fetch all expected records in Crystal Reports

• Query gives incorrect results.
• Filtering data using conditions separated by the operator: OR, does not return the expected result.
• Incorrect number of records is returned when using an OR condition in a Record Selection Formula in Crystal Reports.

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

1. In Crystal Reports, create a report based on any relational data source.
2. Add a Record Selection formula like:  
   

   {Color} = "Blue" OR {Color} = "Yellow" 

3. When refreshing the report, it does not return all the expected rows.

• When a formula is evaluated in Crystal Reports, if the database filed used in the first condition has a NULL value, then the rest of the formula will be ignore, therefore in this case, the OR statement is ignored. As the result the Records Selection Formula does not return records where the second condition is TRUE, when the first condition is NULL.

• To return the expected result set in Crystal Reports, you can either:
• Convert NULL values to default; or
• Add the function isNull in the formula

 

•  Convert NULL values to default

1. In Crystal Reports, open the report.
    
2. Under the menu "File", select "Report Options..."
     
3. Check the option "Convert Database NULL Values to Default"
     
4. Click "OK"
     
5. Save the report.
      
   Note: The disadvantage of this solution is that NULL value for number field will be converted into zero, and it is not possible to differentiate records where value equals to zero or when a value was not entered.
       

• Modify formula by adding IsNull check to the first condition:

1. In Crystal Reports, open the report.
    
2. Under the menu "Report", select "Selection Formula - Record..."
     
3. In the "Record Selection Formula Editor", add the isNull function to catch the situation where the filed value is null.
   
   Example: If we re-use the same example as in the section "Reproducing the Issue", the formula will now be:
    
        Not isNull({Color}) and ({Color} = "Blue" OR {Color} = "Yellow")
   
   
4. Save and Close the Record Selection Formula.
     
5. Save the report.   

OR condition, incorrect records, wrong results, CR , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

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