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

stored-procedures reporting-services hide subreport
Adriaan Stander picture Adriaan Stander · Apr 13, 2012 · Viewed 7.8k times · Source

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

user359040 picture user359040 · Apr 14, 2012

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

Related questions

Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
Search text in stored procedure in SQL Server

I want to search a text from all my database stored procedures. I use the below SQL: SELECT DISTINCT o.name AS Object_Name, o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_…

Read More
SQL Server - SELECT FROM stored procedure

I have a stored procedure that returns rows: CREATE PROCEDURE MyProc AS BEGIN SELECT * FROM MyTable END My actual procedure is a little more complicated, which is why a sproc is necessary. Is it possible to select the output by …

Read More