CODEWITHSUNDEEP
reporting-servicesuploaddatasetconditional-statements
MikeTWebb
MikeTWebb

Reputation: 9279

SSRS run SQL/DataSet conditionally

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?

Upvotes: 8

Views: 13448

Answers (3)

phil gilbert
phil gilbert

Reputation: 11

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

Upvotes: 1

user359040
user359040

Reputation:

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.

Upvotes: 8

JC Ford
JC Ford

Reputation: 7066

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>

Upvotes: 3

Related Questions