SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


One SP, several datasets, SP gets aexecuted several times


One SP, several datasets, SP gets aexecuted several times

Author
Message
kiril.lazarov.77
kiril.lazarov.77
SSC-Addicted
SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)

Group: General Forum Members
Points: 458 Visits: 2248
Hi all,

I have a single SP that I use to populate my data sets, something like this:



CREATE PROCEDURE RptSample
AS
@Id int,
@From datetime,
@To datetime,
@DSVar varchar(20)

BEGIN

SELECT Col1, Col2, Col3
INTO #TMP
FROM t
WHERE Id = @Id
AND Date BETWEEN @From AND @To


IF @DSVar = 'Tablix01'
BEGIN
-- Code to populate data set for Tablix 01 from #TMP
END

ELSE IF @DSVar = 'Tablix02'
BEGIN
-- Code to populate data set for Tablix 02 from #TMP
END

-- ...

ELSE IF @DSVar = 'Tablix10'
BEGIN
-- Code to populate data set for Tablix 10 from #TMP
END




Each data set uses the above parameters, values for @Id, @From, @To will be the same for each data set, however @DSVar will be different for each (Tablix01, Tablix02, etc.). My problem is that the SP gets executed 10 times (once for each data set) when I run the report.

Since each data set is populated from #TMP I would only like this to be executed once. Any ideas how this could be resolved? Thank you.
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18474 Visits: 14896
You can't do that natively in SSRS. Each data set in a report is executed whenever the report is run. SSRS doesn't currently support multiple result sets.

It might be possible to do something in custom code to make things work they way you want, but I don't know how you'd code it.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18474 Visits: 14896
Just thought of something, that might help you, but because I don't know how you are consuming the data in report. Here's the idea:

1 data set that returns the data with a column (or columns) that show what data regions (tablixes or is it tablii?) will consume the data. Then you use the same data set for each data region, but have a filter on each data region using the column (or columns) you added to the data set. Then your query is run once, returning all the data you need and the filtering is done by the data regions within the report.

I actually don't think the current way the report is working is really a problem, but you could do it the way I describe as well, if you think a single call is better than 10 calls.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
kiril.lazarov.77
kiril.lazarov.77
SSC-Addicted
SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)

Group: General Forum Members
Points: 458 Visits: 2248
Thanks for your response.

The result sets returned for those data sets are different (number of columns returned / data types) so not sure if it's possible to do this in one data set. The SELECT INTO query at the top is a massive one touching 15-20 tables / views and although it is optimized I wouldn't want it to run 10 times. Any other ideas? Thank you for your time.
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18474 Visits: 14896
clayman (8/18/2014)
Thanks for your response.

The result sets returned for those data sets are different (number of columns returned / data types) so not sure if it's possible to do this in one data set. The SELECT INTO query at the top is a massive one touching 15-20 tables / views and although it is optimized I wouldn't want it to run 10 times. Any other ideas? Thank you for your time.


The only way I know of to do it in one call is how I already posted. You need to return all the data to the report in one data set and then use that one data set (with filters) for each data region. You only have to use the columns you need for that data region.

I wouldn't design the solution this way. I'd have a specific query/SP for each data set that is optimized for that data set instead of using a catch-all query to populate a temp table and then returning different data for each data set based on a parameter passed in. The temp table is being created and load for each of the 10 data sets. The only way I'd use a "temp table" like this is if I could have a job that pre-populates a permanent table once a day and then just query that permanent "temp" table in each of the data sets for the report.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
MMartin1
MMartin1
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2761 Visits: 2031
One thing that you might try, call the procedure to populate all the rows and have the user do the slicing and dicing in Excel using a pivot table. This would be ideal if the only differences with the reports is the value of a single attribute.

----------------------------------------------------
How to post forum questions to get the best help
Andrew Elias
Andrew Elias
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 18
agreed.
Create a flag column in temp table. Unique identifier for each if statement.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search