Crystal Reports: Suppress a subreport, if it has no data

 

A. Introduction

In this article, I would like to explain how to hide / suppress a subreport, if it contains no data. This could be the case when you would like to avoid scenarios like having the header of a subreport displayed announcing some details to follow, but then there is NOTHING there to show. Or another inconvenient output is – for instance - having the subreport reserving a certain space on the main report's sheet that is simply BLANK.

Well, don’t worry… The fix is very quick and simple :)
The example used in this article is based on a simple database table called BRANCHES consisting of three columns, namely Branch ID, Branch Name and Branch Address. Our subreport just displays the content of that table.

So, let us see what happens, when the DB table gets empty? How would the subreport as well as the main report (the parent) behave?

 

B. Database preparations

Create the example database table and fill it with data using the following script:

create table BRANCHES(
Branch_ID varchar(6) not null,
Branch_Name varchar(20) not null,
Branch_Address varchar(100) not null,
constraint pk_branches primary key (Branch_ID)
);

insert into BRANCHES values
('1111', 'Sydney', 'xxx - yyy, Sydney'),
('2222', 'Perth', 'aaa - bbb, Perth'),
('3333', 'Canberra', 'kkk - lll, Canberra');

 

C. Report Creation

1. Create a new blank report and add 2 new Report Header sections.
2. In Report Header a, add a Text Object and write any text that marks that you are BEFORE the subreport section.
3. In Report Header b, add a Subreport and call it – for instance – Branches.
4. In Report Header c, add a Text Object and write any text that marks that you are AFTER the subreport section.
   
   
   
5. Now double-click the Branches subreport and edit it by writing any text in the Report Header section and adding the 3 columns of the Branches table to the Details section.
   
   
   
6. The report is ready for testing. Click the Preview button and you should see the data of the three branches.
   
   
   
7. So far so good. Now delete all the data of the Branches table from the database using this command: delete * from Branches;
8. And now regenerate the report’s Preview. Can you see the problem?
   
   

 

D. Let’s fix it

1. Double-click the Branches subreport.
2. Go to File > Report Options.
3. Select the option Suppress Printing if No Records.
   
   
   
4. Now preview the report. As there are no records to display, the subreport has suppressed itself. BUT the main report is still reserving a space for the subreport.
   
   
   
5. Right-click the subreport object and select the command Format Subreport.
6. Go to the Subreport tab and select the option Suppress Blank Subreport.
   
   
   
7. Now preview the report. The main report realizes that the subreport is empty and therefore it suppresses the whole subreport object. BUT still the section containing the subreport is showing on the screen!!!
   
   
   
8. Go to Report > Section Expert.
9. Select the section containing the subreport (in our example, it is Report Header b) and select the option Suppress Blank Section.
   
   
   
10. Now preview the report. Finally all the traces of the subreport has been removed