Sub Reports with no data

[Georgina]

Hi

I have read a most of the posts that I have found and was looking for an answer on how best to manage the problem that I have!

I have a report with 16 subreports which are linked by a company id number. Everything works OK when all the subreports have data, but when they don't a blank page is printed.

I have previously been using the statement me.mypagebreak.visible = me.subreportname.report.hasdata which did work, but for some reason now doesn't work.

As I have been reading I have also found information about shrinking reports which I don't think I understand!

Does anyone have an idea what the best way to have sub reports on one report when I must have a new page between each report where data is present on that report, but also not have a blank page where there is no data.

Hope that this is enough information and thanks to anyone that can help me!

George

 

[reclusivemonkey]

Does the query that feeds the report include Company IDs even though they don't have any data? You should be able to create a query that only includes the Company IDs of ones that have data; that should eliminate any blank space. As far as I can tell from your description you are getting blanks because you are pulling out all the Company IDs, regardless of whether they have data. I could be wrong.

Shrinking tends to be used on fields rather than subreports. I would see if you can achieve what you require through the report query first.

Basically, your report should group on Company IDs, then page break before/after depending on how the report is structured. Hope this helps.

 

[Georgina]

I knew that I hadn't explained this well!

The report is basically from a customer questionnaire. The main report includes the company id that the customer responded about and then all the subreports are customer comments about certain questions. Each one of these reports has the customer id field on it, but is set to not visible, the reports are then linked via this customer id.

What I am finding a problem is that questions 1-5 may have comments, question 6 has none etc and then the subreport that picks up the comments for question 6 is blank and prints a blank page

 

[reclusivemonkey]

Ah ha, I think I have a clearer idea now of your report. This does sound like it could benefit from the CanShrink/CanGrow property.

As I understand it, you have a report for each CUSTOMER. Each CUSTOMER also has ONE COMPANY ID, then a series of questions/answers. Not each CUSTOMER has all the questions/answers. One thing I am still not clear on is your subreports. From what I read, you have a subreport for each question; is this so that each questions appears on its own page?

 

[Georgina]

Indeed it is - some of the comments could run onto more than one page, so it felt easier to have them as subreports for each question - I can then run each subreport individually if I need to find all the comments for an individual question.

Thanks for persevering with me...!

 

[John Baker]

Solution for Subreports With No Data

Here is the solution I developed to address subreports with no data... not sure if it pertains to your situation; however, it worked well for me.

John



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