Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to supress empty subreports in SSRS 2008

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

like image 606
Brian Avatar asked Jun 17 '09 10:06

Brian


People also ask

How do I hide subreports in SSRS?

Use the Hide This Item checkbox to hide the report. This will hide the selected report from the regular view. The user could still find it, though, by going to the settings and checking the Show Hidden Items box.

How do I hide blank rows in SSRS?

We need to click on Tablix Detail Row and then go to Row Visibility and then write below expressions. This expression is going to check if Region is blank, and if yes, then it will make the row invisible.


2 Answers

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.

like image 127
Brian Avatar answered Nov 13 '22 22:11

Brian


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!

like image 37
Amazigh.Ca Avatar answered Nov 13 '22 22:11

Amazigh.Ca