How to supress empty subreports in SSRS 2008

Question

I'm creating a 'master' report in SSRS 2008 that collaborates other reports about a person. Sometimes not all of the other reports are relevant and as such return nothing. I'd like to be able to exclude this from the master report so it does not leave a blank page.

I'm aware of the 'no-rows-message' feature, but a whole page with simply "Not applicable for this person" is hardly the best solution!

Essentially I'm looking for a way to determine if a subreport is 'empty' and use that in a visibility expression.

Any help is most appreciated

Gallenz

Answer 1

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.

Bada-Bing! One report that is not cluttered with paper-wasting empty pages.

Note: there is one bad side-effect to this approach, in that, the SQL server will be sending the same information twice, once to populate the subreport's dataset, and again to populate the duplicate dataset in the report. For me, this is acceptable, others may want to be aware of this.

Gallenz

Good job. (buffer to meet 15 char limit) – Gasteropod

After musing over the problem during my lunch break, I've decided that returning the exact same data twice is never a good idea. I'm going to write a stored procedure that returns just the row count of the of the stored procedures used to populate the subreports. Then test to see if these counts are 0 or not and set the visibility of the appropriate subreports accordingly. – Gallenz

thanks for the idea, I needed to grey out a cell in tablix report if subreport returns nothing. So I just put a subreport into a rectangle and set background color of rectangle to grey. I did not even have to use expressions since SSRS by default returns nothing if a subreport returns nothing. The only problem I had when I printed my report it did not show grey cell printed or on PDF (but did show it on screen), so I just set No rows text for a subreport to =CHR(32) and it did the trick. Thanks for the idea! – Hoppe

This solution did not work for me because the actual report's first is not empty. My tablix is using the group functionality and only certain objects in the group have data from the reports I need to show. Everything looks good is that there is a blank page being generated for the group values that do not have data from that report. Any ideas how to hide the subreport when data is empty for the selected group? I tried the Group visibility option but still exports (PDF) a blank page. – Ikeikebana

Answer 2

This solution worked for me as expected, eliminating the hyperlink to the subrepot. Within the «Action» menu in the section where you specify the subreport name:

=iif(fields!SomfieldName.Value <> Nothing, "YourSubReportName",Nothing)

Simple and easy solution!

Psalmody