1211542 - How to display: 'No matching records', when the subreports contains no data in Crystal Reports?

SAP Knowledge Base Article - Public

1211542 - How to display: 'No matching records', when the subreports contains no data in Crystal Reports?

Symptom

  • How to display a message when a subreport contains no data?
         
  • How to format the subreport to display the message "No matching records" ?
           
  • A report contains a subreport linked to the main report on a common database field.  When this report is previewed, certain instances of the subreport does not return any records.
    This is expected, since not all the values in the main report's linking field have a match in the subreport's linking field. How to display a message the subreport contains no data?

Environment

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

Reproducing the Issue

  1. In Crystal Reports, create a report off any data source.
  2. Insert a subreport on the report.
  3. Link the subreport to the main report.
  4. When refreshing the report, some subreport instances returns no data.
               
    How to display the message that there is no data, instead of displaying a blank subreport?

Resolution

  • To display the message "No matching records" when a subreport doesn't return any records, create an additional report header section. In this section place a formula that returns the string "No matching records" only when the subreport doesn't contain any records. You can then format the other sections in the report to suppress only when the subreport doesn't contain any records. 
         
    1. In the subreport, insert an additional Report Header section:
      • On the 'Format' menu, click 'Section'. The 'Section Expert' dialog box appears.
      • On the 'Sections' list, click 'Report Header', and then click 'Insert'. This creates another report header section, so you now have the sections 'Report Header a' and a 'Report Header b'.
            
    2. Conditionally suppress all the sections in the subreport except 'Report Header a':
      • On the 'Sections list, click 'Report Header b', and then click 'X+2' beside 'Suppress'. The 'Format Formula Editor' appears. Type a formula similar to this:
          
        //Suppresses section when there are no records in subreport Replace {Table.Field} with a field from your subreport
        IsNull({Table.Field})
             
      • Save and close the Formula Editor.      
      • Repeat this procedure for all the sections on the report, except 'Report Header a'.
      • When done, select 'Report Header a' from the 'Sections' list, and then select the 'Suppress if Blank' checkbox.
      • Click 'OK' to return to the report.
              
    3. Create a formula that verifies if the database field is null for the first record on the report.
      • On the 'Insert' menu, click 'Formula Field'.
      • Type a name for the formula, and then click 'OK'. The 'Formula Editor' dialog box appears.
      • Type a formula similar to this: 
           
        //@Message Displays message when there are no records in subreport
        WhilePrintingRecords;
        If IsNull({Table.Field}) then "No matching records"
        Else  ""
             
      • Save and close the 'Formula Editor'.
          
    4. Insert the {@Message} formula in the 'Report Header a' section.
            
      The next time you preview the report, the subreport instances without records only display the message 'No matching records".
         
         
  • For an example of the above solution, see the sample report: No Matching Records.rpt, in the attachments section.

Keywords

Link Subreport No Data Blank Subreport Seagate Crystal Reports Linked subreports Subreports containing no records , c2006069 , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , How To

Product

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

Attachments

No Matching Records.rpt