Symptom
- Query gives incorrect results
- Incorrect number of records is returned when using an OR condition in a Record Selection Formula
Environment
- SAP Crystal Reports 2008
- SAP Crystal Reports 2011
- SAP Crystal Reports 2013
- SAP Crystal Reports 2016
Reproducing the Issue
- Create a report based on Xtreme Sample Database
- Insert the Table ‘Product’
- Insert the Fields ‘Product ID’, ‘Size’ and ‘Color’ in the report
- Click on Select Expert to create a Record Selection Formula based on the Field ‘Size’
{Product.Size} = "sm"
- Click OK
- Refresh data when prompted - Query returns 9 records
- Click on Select Expert > Delete Product.Size Record Selection Formula
- Create a new Record Selection Formula based on the Field ‘Color’
{Product.Color} = "black"
- Click OK
- Refresh data when prompted - Query returns 7 records
- 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.
Cause
- 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
Resolution
There are two possible solutions:
- Convert NULL values to default
- Open the report
- Go to File> Select "Report Options"
- Select "Convert Database NULL Values to Default" and "Convert Other NULL Values to Default"
- Click OK
- 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:
- Open Crystal Reports record selection formula
- 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"
Keywords
OR condition, incorrect records, wrong results , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem
Product
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