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

• Query gives incorrect results
• Incorrect number of records is returned when using an OR condition in a Record Selection Formula

• SAP Crystal Reports 2008
• SAP Crystal Reports 2011
• SAP Crystal Reports 2013
• SAP Crystal Reports 2016

1. Create a report based on Xtreme Sample Database
2. Insert the Table ‘Product’
3. Insert the Fields ‘Product ID’, ‘Size’ and ‘Color’ in the report
4. Click on Select Expert to create a Record Selection Formula based on the Field ‘Size’
       
   {Product.Size} = "sm"
    
5. Click OK
6. Refresh data when prompted - Query returns 9 records
7. Click on Select Expert > Delete Product.Size Record Selection Formula
8. Create a new Record Selection Formula based on the Field ‘Color’
    
   {Product.Color} = "black"
    
9. Click OK
10. Refresh data when prompted - Query returns 7 records
11. Go to Select Expert and add the following in the Formula editor:

{Product.Size} = "sm"

OR

{Product.Color} = "black"

The query returns 13 records rather than 14 records.

• If Crystal Reports formula faces NULL value in condition 1 of OR statement it ignores the rest of the formula (condition 2, 3, etc.) and returns NULL. As the result Crystal Reports with OR function in records selection does not return records where condition 2 is TRUE, but condition 1 is NULL.
• Here is the logic Crystal Reports uses to proceed OR statements with NULL values:

NULL or FALSE = NULL

NULL or TRUE = NULL

TRUE or NULL = TRUE

FALSE or NULL = NULL

There are two possible solutions:

•  Convert NULL values to default

1. Open the report
2. Go to File> Select "Report Options"
3. Select "Convert Database NULL Values to Default" and "Convert Other NULL Values to Default"
4. Click OK
5. Refresh the report.

Disadvantage of above 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 value was not entered.

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

1. Open Crystal Reports record selection formula
2. Add IsNull check to the condition 1 of the OR statement as per following example:

(not(IsNull({Product.Size})) and {Product.Size} = "sm")OR {Product.Color} = "black"

 

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

Crystal Reports 2008 V0 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016 ; SAP Crystal Reports XI ; SAP Crystal Reports XI R2