How to Handle Sub Reports with No Data

[John Baker]

Hi All:

I have a main report form that contains 6 subreports. Each subreport is based on a separate query. In some cases, there may be no data available to report for a specific subreport.

Each subreport is designed with a 'Report Header' to provide an identifier and a description. For cases where there is no data available to report for a specific subreport, I would like to either: (1) exclude printing the subreport entirely; or (2) print the report header for the subreport along with a message indicating that there is no data available to report.

Currently, if no data is available, a blank page is printing.

Any help or guidance would be appreciated.

Thanks!
John

 

[John Baker]

Report 'On No Data' Event Procedure

Ok, I came across the Report 'On No Data' event procedure option and thought that this may provide my solution.

I added a 'Page Header' to the subreport and placed a label control in the page header with text indicating **** NO DATA TO REPORT ****. I set the default visible property of the page header to 'No' (ie I don't want the page header to print when there is data available).

I added an 'On No Data' Event Procedure for the subreport that changes the visible property of the Page Header to True:

PageHeaderVIP.Visible = True​

If I run this subreport separately, the outcome is exactly what I want. If no data is available to report, the label control in the Page Header prints. However, when I run the main report that contains this subreport, a blank page continues to print for this section when no data is available.

I'm stumped! Any help would be appreciated.

Thanks,
John

 

[Rich]

Set the can shrink property to yes

 

[John Baker]

Can Shrink?

Rich:
I cannot find the 'can shrink' property for the report? Which control are you referring to?

J

 

[John Baker]

Solution Found!

Ok... after several weeks of searching various forums on the internet, I was finally able to solve my problem!!

I have a main report that functions as a container for 6 subreports. I want each subreport to print on a separate page. Each subreport may or may not contain data. If no data exists for a subreport, I want to print the report header for the subreport with a message indicating that 'No Data Is Available To Report'.

Here is my solution:
- I created a variation of each subreport that may not contain data. The format is simple - column headers and a text box in the detail section that indicates data is not available to report.
- I added Event Procedure code for the 'On Format' event in the Detail section of the Main Report (this is the section that acts as the container for all of the subreports) that sets the Visible property of the page break controls that separate each subreport AND determines whether to print the subreport with data or without data.

Here's the Event Procedure code:


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Me.pgbrkOne.Visible = Me.rpt_daily_section2.Report.HasData
Me.pgbrkTwo.Visible = Me.rpt_daily_section3.Report.HasData
Me.pgbrkThree.Visible = Me.rpt_daily_section4.Report.HasData
Me.pgbrkFour.Visible = Me.rpt_daily_section5.Report.HasData
Me.pgbrkFive.Visible = Me.rpt_daily_section6.Report.HasData

' check to see if Frequent Callers section has data
If Me.rpt_daily_section3.Report.HasData Then
Me.rpt_daily_section3_nodata.Report.Visible = False
Me.rpt_daily_section3.Report.Visible = True
Else
Me.rpt_daily_section3_nodata.Report.Visible = True
Me.rpt_daily_section3.Report.Visible = False
End If

' check to see if VIP Callers section has data
If Me.rpt_daily_section4.Report.HasData Then
Me.rpt_daily_section4_nodata.Report.Visible = False
Me.rpt_daily_section4.Report.Visible = True
Else
Me.rpt_daily_section4_nodata.Report.Visible = True
Me.rpt_daily_section4.Report.Visible = False
End If

' check to see if Sev 1 and Sev 2 section has data
If Me.rpt_daily_section5.Report.HasData Then
Me.rpt_daily_section5_nodata.Report.Visible = False
Me.rpt_daily_section5.Report.Visible = True
Else
Me.rpt_daily_section5_nodata.Report.Visible = True
Me.rpt_daily_section5.Report.Visible = False
End If

' check to see if All Open Tickets section has data
If Me.rpt_daily_section6.Report.HasData Then
Me.rpt_daily_section6_nodata.Report.Visible = False
Me.rpt_daily_section6.Report.Visible = True
Else
Me.rpt_daily_section6_nodata.Report.Visible = True
Me.rpt_daily_section6.Report.Visible = False
End If


End Sub