SSRS: Dynamically choosing dataset/query

Question

I am porting reports to SSRS, and I have found a report that the webapp currently handles in a strange way. I am trying to scope out a solution.

Currently, the webapp does some logic when the report is called and figures out whether to call one sql query or another. They both provide the same sql database fields. The webapp's report builder populates the one template with the contents of whatever query got used.

OK. How can I reproduce this with SSRS? As far as I can tell, my options are:

Define a single dataset and do logic to pick which select statement to run. (But how?)
Define two datasets and use a ton of IIF statements to pick which dataset/value is displayed in each field. (But will this work with tablixes?)
Something else which I have not thought of.

I just need to know what is possible and efficient.

Thanks.

Harry Harry · Accepted Answer · 2019-05-13T21:29:12

One way to do it is to have a hidden parameter called @logic.

You would already know what the logic is and which query to run for that logic.

lets say for example your current first query is

select table1.columna as a from table1

and your current second query is

select table2.columna as a from table2

what would could now do is,

select table1.columna as a from table1 where @logic = 0
union all
select table2.columna as a from table2 where @logic = 1

Your report will pass the @logic parameter to the dataset depending on the logic.

In case one set @logic = 0, case two set @logic = 1

This will return the dataset you need because the where @logic should take care of the result set.

Hope my explanation makes sense.

Of course.. this is just one way of approaching it!

SSRS: Dynamically choosing dataset/query

1 Answers