Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

One SP, several datasets, SP gets aexecuted several times Expand / Collapse
Author
Message
Posted Sunday, August 17, 2014 7:37 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 10:54 AM
Points: 299, Visits: 1,904
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.
Post #1604255
Posted Monday, August 18, 2014 10:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:26 AM
Points: 10,381, Visits: 13,436
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

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
Post #1604544
Posted Monday, August 18, 2014 10:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:26 AM
Points: 10,381, Visits: 13,436
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

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
Post #1604550
Posted Monday, August 18, 2014 11:17 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 10:54 AM
Points: 299, Visits: 1,904
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.
Post #1604586
Posted Monday, August 18, 2014 11:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:26 AM
Points: 10,381, Visits: 13,436
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

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
Post #1604590
Posted Tuesday, August 19, 2014 5:20 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 6:42 PM
Points: 459, Visits: 1,065
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.
Post #1605242
Posted Tuesday, August 19, 2014 5:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 10:17 PM
Points: 1, Visits: 17
agreed.
Create a flag column in temp table. Unique identifier for each if statement.
Post #1605248
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse