How do I change the query execution order in SSRS?

Question

How do I control which stored proc runs first in SSRS. My second stored proc needs to use the temp table data from the first stored proc. Thanks so much.

Answer 1

What you are proposing is a significantly bad idea. If both datasets are sharing a table can you merge them into one result set, then filter or aggregate it in the report.

That said, have you tried reordering the datasets in the RDL (XML) file. I imagine reporting service will run these in order, though it may run them asynchronously. No guarantees.

EDIT:

Adolf's Link confirms it, They do run in parallel unless you set Use Single Trasaction in the datasource. Then they run in the order of the RDL file as I suspected.

Answer 2

If you use a value from the first procedure's dataset to be used to populate an input parameter used by the second procedure, this should ensure that they are called in the desired order.

That said, I agree with jimconstable - it would make more sense to use a single dataset and filter out any unwanted results.

Answer 3

Dataset Execution Order

http://blogs.msdn.com/b/robertbruckner/archive/2008/08/07/dataset-execution-order.aspx