0

In an SSRS report expression, is there any way to refer to the "Hidden" property of a ReportItem?

More specifically: my SSRS report consists of a summary, with a subreport containing detail. I'd like to be able to stop the subreport running if it's hidden, as in this question. The answer to that question recommends changing the query based on a parameter, but my problem is how to set that parameter. Actually, I only need the parameter to be 1 or 0; the query will just return nothing if it's 0, and that's good enough.

I'd like to be able to show or hide the subreport using a textbox as a ToggleItem, and have an expression as the parameter value that goes something like this:

=iif(ReportItems!SubReport1.Hidden=False, 1, 0)

...or this:

=iif(ReportItems("SubReport1").Hidden=0, 1, 0)

...but the "Hidden" property doesn't appear for either.

Other options I've explored:

  1. Putting in an invisible text box, and using its value in the parameter expression as per this MSDN article. The problem then becomes how to set the value of the text box.
  2. Using a variable; same problem applies (actually I'm not even sure how you use variable in SSRS, or even if you can).
  3. Keeping the subreport as a separate report, and linking to it; a bit inelegant, and means losing the other report content when the user wants to see the detail.
  4. Extensive Googling; returns a lot of ways to set the "hidden" property, but no way to reference it.

Suggestions, moral support, constructive criticism and barefisted abuse all gratefully received.

Community
  • 1
  • 1
probablyrichard
  • 25
  • 6

2 Answers2

0

The question you linked above shows how to use a report parameter to control the visibility and execution of subreports. But that's different from using a report item such as a text box to do it.

It sounds like what you're really looking for is on-demand execution of subreports, which I'm afraid isn't possible. The report rendering only happens once when the report is first displayed unless you change parameters and run it again.

I'm afraid your only option for on-demand execution of the detail report is to use a "drill-through" design. That means text boxes on your report that serve as links to the detail report instead of the detail report being used as a subreport.

SSRS 2008 R2 Drill Down On-Demand to Sub Report

Community
  • 1
  • 1
JC Ford
  • 6,756
  • 2
  • 22
  • 33
0

As the answer here stated, you can't stop a hidden item from running. If you don't want the subreport to run when you render the main report, you'd need to run the main report with this variable that prevents it from running in the first place.

To this end, it would make sense to simple hide or show this subreport by using the parameter, rather than a textbox toggle. You can reference the parameter's value for this on the subreport by using:

Visibility -> 'Show or Hide Based on an Expression' -> f(x)

IIF(Parameters!ToggleSub.Value = 1, true, false)

The thing is, if you included a textbox to toggle the visibility at this point, the only way to do it would be to pass back a parameter and refresh the report anyway. Once you choose not to render the subreport, it will only render if the entire report is refreshed.

I hope this helps you out.

Community
  • 1
  • 1
Sonny Childs
  • 560
  • 2
  • 13
  • Thanks very much; that's really helpful. As it's a small report, and this would be very useful functionality, I'm wondering if the smart thing might be to redo it in ASP.NET, although that's a whole new can of worms and would probably take more time than it's worth. But that's another story. Thanks again for the answer. – probablyrichard Mar 10 '15 at 12:43
  • I've experimented with this and it's actually pretty easy to write your own dashboard/report view in C# using winforms. There's a neat little 'ReportViewer' control. My next project is to create numerous little reports that can all be rendered individually on a C# GUI frontend. – Sonny Childs Mar 10 '15 at 13:00