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
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.
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.
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.
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!
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With