How to control execution of a subreport based on its visibility in SQL Server 2008 Reporting Services?

Question

Hello,

I have sever nested subreports that by default are hidden and are only made visible when toggled via an element in the parent report. I want SSRS to not execute the query statements for these subreports untill they are toggled and made visible. The subreports recieve their parameters from their parents, and not from any user input. Is there a way to implement this?

I am using SQL Server 2008 Business Intelligence Development Studio.

Answer 1

In your dataset (if you are using text) preface your query with the following:

If ShowSubreportParameter = "Yes"
BEGIN
   ---Insert Your Query Here
END

If you are using a stored procedure, you could try the same thing but call your sproc from inside the text:

IF ShowSubreportParameter = "Yes"
BEGIN
   Exec MySproc @parameter
END