x

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.
more ▼

asked Aug 07, 2012 at 07:58 AM in Default

Mrs_Fatherjack gravatar image

Mrs_Fatherjack
4.7k 60 62 66

Can you please give more details? What is the requirement and why you want it like this?
Aug 07, 2012 at 10:46 AM Usman Butt
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first
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.
more ▼

answered Sep 11, 2012 at 01:13 PM

akasselman gravatar image

akasselman
20

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Aug 07, 2012 at 08:13 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.3k 74 78 108

+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, 2012 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, 2012 at 10:55 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x545
x3

asked: Aug 07, 2012 at 07:58 AM

Seen: 1708 times

Last Updated: Sep 11, 2012 at 01:13 PM