1212962 - Slow report performance when using the function: isNull, in the Record Selection in Crystal Reports

• Slow report performance.
• Report takes a long time to refresh.
• After adding the function: "isNull" in the Record Selection Formula of a report in Crystal Reports, and the report is refreshed, the report performance is slower than expected.

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

1. In Crystal Reports, create a report based a relational data source.
2. Add a couple of database fields to the report.
3. Create a simple Record Selection to filter the report data.
4. Refresh the report, and take note of how long it takes to refresh.
5. Add in the Record Selection the function "isNull' to eliminate NULL values from coming to the report.
6. When refreshing the report, notice it takes longer to refresh the report.

• This situation occurs because for some data sources, the function 'isNull' is evaluated on the Crystal Reports side, and not on the database server side.
     
• Whenever possible, Crystal Reports translates the Record Selection Formula into SQL syntax and passes this SQL Query to the database server. Sometimes, the functions used cannot be translated in SQL syntax, and this is the case with the IsNull function for some data sources.
     
• When the IsNull function cannot be translated to SQL syntax, the portion of the Record Selection Formula must be processed on the client side, slowing down report performance.

• To test for null values in a Record Selection Formula, without sacrificing report performance, check the Report Option "Convert NULL Field Value to Default":
    
1. In Crystal Reports, open the report.
     
2. Under the 'File' menu select: 'Report Options'.
     
3. In the 'Report Options' window, check the option: 'Convert NULL Field Value to Default'
     
   This will converts all null database values to a default value. Numeric fields are converted to zero (0), and string fields are converted to blank value ("").
        
4. Edit the report Record Selection Formula, and instead of using the IsNull function to test for null values, you use zero (0) or blank value ("").
   
   For example:
   
   To test for null values if the field a string, write the following code:
   
             {table.field} = ""
   
   If the field is a number:
   
             {table.field} = 0
   
   If the field is a date:
   
             {table.field} = Date(0,0,0)
   
   This code can be passed to the database server for evaluation, therefore maximizing report performance.

• For more information on how to improve report performance, see the SAP Knowledge Base Article 1636361

CR, isNull, performance issue, slow report. , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem