SSRS run SQL/DataSet conditionally

Question

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

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

Answer 1

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.

Answer 2

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 3

This works when I try it in Oracle

select
case when :ShowThisData = 0
        then    (SELECT 'Y' 
                    from dual)
    ELSE    (select 'N'
              from dual)
end test1
from dual

allowing you to use multiple statements in 1 query