Dynamic selection of dataset in SSRS

Question

I am going to design a report using SSRS 2005. Is it possible to have the selection of dataset done dynamically?

Let's say I am going to have two tables, where the first one is the summary and the second is the detail data. One of my parameters (with values Summary and Detail) will determine which version of the report is to be displayed. Can I have two different datasets, and while clicking the view report button in parameter view dialog (or before) set which dataset which will be used?

Answer 1

You can use the single data set by using switch statement some thing like this: You can type this in data tab but the results can be seen only in preview tab, but not the data tab like regular queries.

=Switch(Parameters!ToDecide.Value = "Summary",
"Your query for Summary", 
Parameters!ToDecide.Value = "Detail",
"Your query for Detail") 

1. Make sure you don't have any excess spaces with in the query(not more than one space where needed)
2. Both the queries should return the values required by the Table in Layout.

Answer 2

You'll probably have to have 2 pairs of table contained in, say, a rectangle, and hide the unused one (at the container level). Or use subreports.

If you find a way to switch datasets, then you'll have to make sure that all columns are the same as well as making sure that any functions using the DataSet as a scope parameter are changed too... which makes me think it just isn't supported.

Answer 3

You might want to try using a single dataset and having a summary and detail data region (table, list, etc.) in the report, one of which is visible and one of which is not. In this scenario, the dataset is only evaluated once and you are still meet your requirement.

Hope this helps,

Bill

Answer 4

It would be easier to create and maintain if you have two separate tables and hide one or the other depending on the choice made.

I haven't tried this, but I think another possibility would be to use three reports: a container, a summary subreport, and a detail subreport. Switch between the subreports according to the choice.

There is a performance gotcha to the first implementation though. SSRS will try to fill every DataSet in the report when it loads, even ones that are not used in the report's output or by other DataSets or expressions. In other words, just because you're displaying the summary report, doesn't mean that you're not asking the database to fill out your detail data as well.

There is a workaround that can improve the performance a bit, but will still end up with a trip to the server. The hack is to set a flag parameter to determine whether or not the query should actually return any real results or if it should just return the columns you want.

Answer 5

You may try adding condition that depends on a cetain parameter to execute. Then from there, You may use different data set. Assuming that you'll use it on separate hidden tablix.