Dynamic selection of dataset in SSRS

Question

Hi,

Please clarify me with this.

I am going to design a report using SSRS 2005. Is it possible to have the selection of dataset done dynamically. Lets say i am going to have two tables, first one is the summary and second one is the detail data. One of my parameter (with values 'Summary' and 'Detail')is deciding which version of the report to be displayed. Can I have two different datasets and while clicking (or before) the viewreport button in parameter view dialog should decide which dataset to be called?

Thanks in advance. San

Answer 1

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 2

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 3

Hi,

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

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.