reporting services - Hiding Sub report in SSRS based on Parameter (and not executing the Stored Procedure)

Question

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?

Answer 1

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).