SSRS - Can I run datasets from different datasources sequentially

Question

I have a report (SSRS / SQL Server 2008 R2) where I need to access 2 datasources and each datasource has a dataset. I need the query in DataSource1-Dataset1 to run and complete before the query from DataSource2-DataSet2 starts.

Ultimately I'd like to use a result of Dataset1 as an input parameter to Dataset2. I know if there is only one datasource I can check the 'Use single transaction' in the Data Source Properties, however with 2 different datasources that didn't help. I have also tried creating a hidden parameter that has a default value based on dataset1. That worked up until dataset1 needed a parameter itself.

Any suggestions on how to set this up would be very helpful. Thanks in advance.

Answer 1

In the SSRS report data explorer, the datasets are listed in order that they will be evaluated and started. Normally they are run in parallel, but if you make a dependency between them, they will run in sequence. Create a parameter that is sourced from dataset 1 and use that parameter in dataset 2. This will force SSRS to complete the results in order.

Keep in mind however that this will slow down the overall presentation speed of your report.