question

Mrs_Fatherjack avatar image
Mrs_Fatherjack asked

SSRS DataSet Execution Order

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.
ssrsdatasets
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you please give more details? What is the requirement and why you want it like this?
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
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.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 for the idea. But not sure how prompting the re-run would not refresh the report? OR May be I am still a bit sleepy and missing something obvious ;)
0 Likes 0 ·
the first time the report runs it would only show a message "Please re-run this report with paramRun = 1". On the second execution (or actually any execution with paramRun = 1) it would use the results of Proc02 to show results to the user. The report and the 2 procedures would need to contain logic that controls their execution. It's a 'brown paper and string' solution that I'd be reluctant to actually put in place for your average user as it is not likely to be robust. It is however the only way that I can see to run 2 procedures in a specific order from a report.
0 Likes 0 ·
akasselman avatar image
akasselman answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.