SSRS run SQL/DataSet conditionally

Question

Hello....

I have an SSRS report that contains several subreports. The user has the ability to select/deselect which subreports they want to produce using several Boolean parameters. If a subreport is deselected then it is not rendered by setting the Visibility property. However, the DataSet associated with the de-selected subreport still executes causing the execution time to take longer than expected.

Is there any way to tell a dataset on a subreport or Tablix not to execute based on a Parameter selection?

Thanks

Answer 1

Yes. Just check the parameters in each dataset and use an IF/ELSE construct to return actual data or dummy data of the same shape to prevent errors. Assuming your parameter is named @ShowThisData then you can do this:

IF @ShowThisData = 0
    SELECT '' FIELD1, '' FIELD2, <etc... to create a dataset that matches the normal output.>
ELSE
    <whatever you normally do to get the data>

Answer 2

Include an ...AND :ParameterName = 'Y'... condition in the where clause - if your parameter is not 'Y', the query will still fire but it will immediately return 0 records.