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.

asked Aug 07 '12 at 07:58 AM in Default

Mrs_Fatherjack
4.6k ● 48 ● 53 ● 61

Can you please give more details? What is the requirement and why you want it like this?

Aug 07 '12 at 10:46 AM Usman Butt

(comments are locked)

2 answers:

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

akasselman
20

(comments are locked)

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.

answered Aug 07 '12 at 08:13 AM

Fatherjack ♦♦
38.8k ● 56 ● 73 ● 104

+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 ;)

Aug 07 '12 at 10:51 AM Usman Butt

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.

Aug 07 '12 at 10:55 AM Fatherjack ♦♦

(comments are locked)