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.
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; Proc01 - when paramRun = 0 it runs and creates your XML data, when it it 1 it does nothing Proc02 - when paramRun = 1 it runs and uses your XML data, when it it 0 it does nothing 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.