I wonder if anyone can help. I'm trying to control the order of execution of 2 datasets in an SSRS report. I need dataset 1 to run which populates data prior to dataset 2 which returns the data. I can't put both in a stored procedure as the first dataset returns some XML and this causes confusion in the report.
Any ideas, gratefully received.
asked Aug 07 '12 at 07:58 AM in Default
I had a similar issue, and found a solution at http://blogs.msdn.com/b/robertbruckner/archive/2008/08/07/dataset-execution-order.aspx , which involves serialising the order in which the stored proc gets called. In that case, you can run the stored proc once, to populate your data (e.g. into a table) and thereafter call the stored proc again (with different parameters) in order to return your data from the temp table.
answered Sep 11 '12 at 01:13 PM
The only way I can think of doing this is with a report parameter but it's not elegant...
Set each proc to evaluate a parameter of the same name but take different action. So;
Have a parameter with a default of 0. Have the report run and give the user the prompt to re-run the report with the parameter value of 1. This time the second proc will run and give the full report details to the user.
This will have issues if more than one person tries to run the report at the same time. You will have to persist the XML data in the database as the report wont be able to use any temp tables between runs.