SAP Knowledge Base Article - Public

1212962 - Slow report performance when using the isnull function in the Record Selection in Crystal Reports

Symptom

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

Environment

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

Reproducing the Issue

  1. In Crystal Reports designer, create a report off any data source.
  2. Add a couple of database fields to the report.
  3. Create 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.

Cause

  • This situation occurs because of 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 for evaluation. Sometimes, the functions used cannot be translated in SQL syntax, and this is the case with the IsNull function.
       
  • Because the IsNull function cannot be translated to SQL syntax, that portion of the selection formula must be processed on the client side, slowing down report performance.

Resolution

  • To test for null values in your Record Selection Formula without sacrificing report performance, complete the following steps:
      
    1. From the 'File' menu select 'Report Options'.
        
    2. In the 'Report Options' dialog box, select the 'Convert NULL Field Value to Default' check box. This check box converts all null database values to a default value. Numeric fields are converted to Zero and string fields are converted to "".
         
    3. Edit your record selection formula so that instead of using the IsNull function to test for null values, you use zero or "".

      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 performance.

See Also

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

Keywords

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

Product

Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016