SAP Knowledge Base Article - Public

1576905 - Report containing linked subreports takes long time to refresh, when there is no data in the main report in Crystal Reports

Symptom

  • Report performance issue.
  • Report takes a long time to refresh despite the report returns no data.
  • The same report used to refresh quickly when no data were returned.
  • Linked subreport sending an SQL Query to the database with no Where Clause.
  • Reports that contains at least one linked subreport takes a while to run when the main reports returns no data in Crystal Reports.
  • Linked subreport refresh when the main report returns no data.

Environment

  • SAP Crystal Reports 2008 with Service Pack 3 and higher
  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2013

Reproducing the Issue

  1. In Crystal Reports, create a report off any data source. 
  2. Add one table to the report.
  3. Add a subreport in the Details section of the report off any data source.
  4. Link the subreport to the main report off a database field
  5. Create a Record Selection on the main report that will return no data on the main report.
  6. Refresh the report.

    Notice that despite the report returns no data, it takes long time to display the report.

Cause

  • The issue has been identified and logged under Problem Report ID ADAPT01503419
      
  • The reason it takes more time to display the report, it is due to the subreport sending an SQL Query to the data source without any WHERE clause, and  herefore, despite the main report doesn't returns any data, the report wait for the subreport query to complete the execution. 
        
  • When the main report returns no data, the normal behavior is for the linked subreport to not be executed. Which means, no SQL Query should be sent to the database from the subreport, but from Service Pack 3 of Crystal Reports 2008, it send an SQL Query for the linked subreport even when the main report has no data.

Resolution

  • The issue has been resolved in the following product updates:
      
    • For Crystal Reports 2008:
      • Service Pack 3 - Fix Pack 3.5 and higher.
      • Service Pack 4 - Fix Pack 4.1 and higher.
      • Service Pack 5 - With no Fix Packs.   Important Note: Any Fix Pack applied to Service Pack 5 will reintroduce the issue. You need to apply Service Pack 7 or higher.
      • Service Pack 6 - Fix Pack 6.1 and higher.
          
    • For Crystal Reports 2011:
      • Support Pack 05 - Patch 5.6
      • Support Pack 06 - Patch 6.1
      • Support Pack 07
          
    • For Crystal Reports 2013:
      • Support Pack 02
           
  • After applying the product update, when a report contains linked subreports, and the main report returns no data, the subreport will not be executed, and no SQL Query will be send to the data source from the subreport.

Keywords

Crystal Reports, CR, Subreports, Performance issue, Slow report, SQL Query , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Bug Filed

Product

Crystal Reports 2008 V0 ; Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2011, feature pack 03