4

I have an issue where I wish to hide/show a sub-report based on a parameter that is passed to the main report.

I can set the hidden property using an expression which will hide/show the sub-report, but what I found using SQL Profiler, is that the sub-report stored procedure is still executed, even if the sub-report is hidden.

Is anybody aware of a way to avoid this, other than changing the stored procedure itself. If not, does anybody know the reason why the stored procedure is executed even thought the sub-report is hidden?

Adriaan Stander
  • 162,879
  • 31
  • 289
  • 284

3 Answers3

8

Hidden report elements in SSRS are still processed - the hidden property simply determines whether they are displayed in the output.

One way to ensure that the procedure is only executed conditionally would be to change the name of the stored procedure in the dataset to an expression - something like:

=iif(Parameters!RunMyDataset.Value="Y","sp_MyDataSet","sp_Dummy")

- where sp_Dummy is a stored procedure that performs minimal processing (eg. SELECT NULL).

  • Would the dummy sp not have to return the same columns as the original sp? – Adriaan Stander Apr 19 '12 at 04:50
  • @astander: If it didn't, the report would generate a number of warnings (but no errors) when previewing in BIDS. So ideally it would be a good idea to generate the same set of columns in the dummy procedure (to prevent generating those warnings), but it isn't essential. –  Apr 19 '12 at 07:31
  • That would then imply that I would have to create a dummy sp per sub report/sp and have to maintain 2 SPs per sub report that needs to be hidden. Do you know what the reasoning behind the MS decision was to force the SP execution? Anyhow, I would have to think of something else, as I dont want the SP to have the display logic built in for visibility. – Adriaan Stander Apr 19 '12 at 08:19
  • @astander: As I said, it isn't essential. You can use a single dummy stored procedure and just ignore the warnings (which I wouldn't expect to be visible to the end users). –  Apr 19 '12 at 08:21
  • Still seems like a hack to me. It would be better if you could avoid the SP call entirely on request. – Adriaan Stander Apr 19 '12 at 11:47
  • @astander:Do you found any way to avoid the SP call if found then please post it as answer i am also in need of the same functionality – Mahesh Aug 14 '13 at 12:53
  • We ended up using a parameter from the SSRS called @ShouldRun (Y/N), passed to the Stored Procedure. The very first statement in the SP was IF(@ShouldRun = 'N') RETURN; – Adriaan Stander Aug 14 '13 at 13:05
  • @astander Can you please post an answoer with soulutin How Do you set parametar ShouldRun , I also trying to aviod running sp for subreports if they are hiden , But I do not know how to set ShouldRun param. How do you check is some cell (row) is visibile or not. – adopilot Aug 14 '13 at 14:08
1

There's a method to solve this. Base on your description of the question. For example, you have 4 subreports and named them page1 through page4. And main report will pass a parameter "Investor_ID" value to all these 4 pages. In the main report, you need to create 4 boolean parameters and set hidden property of the subreport to control these 4 subreports visible or not.

For instance, Use expression =iif(Parameters!Page1.Value = true, false, true) so that when you select true for parameter page1 then page1 will be shown otherwise it will be hidden.

Then you need to config parameters in "Subreport Properties" for subreports.

For Parameter Investor_ID, which the subreport needed, in the value choose expressing and use =IIF(Parameters!Page1.Value = true, Fields!Investor_ID.Value, nothing). That means if you choose hide page1 then mainreport will pass nothing to subreport page1. Then Page1 will not be run and will show "Error: Subreport could not be show" since the Investor_ID parameter is null. But we hide that subreport. So page1 will not show up.

In this way you can control the hidden subreport execute or not.

SheldonZ
  • 157
  • 1
  • 3
0

  1. Right-click on the sub report object in the main report and select “Format Subreport…”. This will open the Format Editor.

  2. Select the “Subreport” tab in the Format Editor.

  3. place a check mark in the “Suppress Blank Subreport” check box.

Vidya Vijayan
  • 1
  • 1