Solved

Suppressing a blank subreport in MicroSoft Reporting Services 2008 using Visual Studio 2008

Posted on 2013-05-10
5
2,399 Views
Last Modified: 2013-05-30
Hello,

    I have a master report that contains thirteen subreports. I put each subreport into a rectangle to force a page break so that each subreport prints on a separate page. I got the subreport to be not visible of there is no data for the report. By doing this, it's a blank page. I don't want that instead is it possible to not have that blank page displayed? Is there some setting for a subreport to be suppressed if there is no data for the report? If so how do you do that? I'm using MS Reporting Services 2008 in Visual Studio 2008.

      Any feedback would be appreciated.

Thanks for your help,
Ting
0
Comment
Question by:pensiongenius
  • 3
  • 2
5 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39157756
This is an issue discussed frequently it seems, and there does not seem to be a single best solution (that I could find).

OK, so I've got this figured now. The answer is to place the subreport into a rectangle. Then set the visibility of the rectangle to something like this:

=IIF(First(Fields![SOMEFEILD].Value, "[SOMEDATASET]") IS NOTHING, TRUE, FALSE)

Where [SOMEDATASET] is a dataset populated in the same way as the one populating the subreport. Then if the subreport is empty, then [SOMEDATASET] will also be empty, and more to the point, the field [SOMEFEILD] will be equal to NOTHING.
http://stackoverflow.com/questions/1006343/how-to-supress-empty-subreports-in-ssrs-2008?rq=1

similar approaches are mention in other places I looked at. Note the 'down-side' here is you need to execute the query twice - once to decides if you need to display, and if it is displayed than again to display it.

Other conversations that may give you hints:
http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/731e3706-ad2f-4ef5-ac0e-527eadb4e45d
http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/3af7479e-8604-4e1a-af12-8170f0272c9d
http://stackoverflow.com/questions/9093763/hide-empty-subreport-in-ssrs2008-r2

I hope these links provide a solution for you.
0
 

Author Comment

by:pensiongenius
ID: 39208877
Hi PorletPaul,

   Thanks for that feedback. Sorry for the delay in responding to your posting.

   The first link that you provided would work if the dataset for my main report and for the subreports were the same.

   It did give me insight to try something based on the Hidden Visibility property of the rectangle. So what I ended up doing was putting each of my subreports into a rectangle. Then in the SQL stored procedure for the master report, I had to declare variables that would be used as record counts for the subreports that I know may or may not have data. Then, I populated fields that I had in a temp table for the subreport record counts with the values of the record count variables. If the record count was zero then I would set the hidden to true for the visibility property of the rectangle and if the record count was not zero then I would set the hidden to false for the visibility property of the rectangle.

Thanks again for your help,
Ting
0
 

Author Comment

by:pensiongenius
ID: 39208886
I've requested that this question be closed as follows:

Accepted answer: 0 points for pensiongenius's comment #a39208877

for the following reason:

The reason why I went with mine versus PorletPaul's solution was I had to tweak the solution that he emailed me in the first link in his posting. I do appreciate his help in providing me additional links to research.

Thanks,
ting
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39209440
>> declare variables that would be used as record counts for the subreports that I know may or may not have data.
good approach, way better than running the sub-report code twice.
0
 

Author Comment

by:pensiongenius
ID: 39209510
thanks.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now