[SOLVED] Do Queries inside a Sub Report still execute if it is Hidden

Do Queries inside a Sub Report still execute if it is Hidden

I have a situation where I want to show/hide sub-reports inside a tablix dynamically. Ideally, the content inside the hidden sub-reports would not execute to save resources. Anyone know how this works?

Thanks

Solution

As far as I am aware the queries will still run. You could double check by running SQL Server Profiler when you run the report and capture the queries that are executed.

Way back in SQL 2008 the queries would definitely run. We had a situation where we would only want sub reports to show based on parameters passed to the report so what we did was pass the parameters to the subreport and use those to determine if the query should run.

If the query did not need to run we would return an empty dataset but, as SSRS insists on always having the same structure returned the query ended up looking like this...

DECLARE @results TABLE(EmplID int, EmpName varchar(50))
IF @hidden = 0 -- sub report needs to show
    BEGIN
        INSERT INTO @results
        SELECT EmpID, EmpName FROM myTable
    END
SELECT * FROM @results

So although the query runs, if no data is required all that happens is an table variable is created which is so fast you won't notice.