August 17, 2014 at 7:37 pm
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.
August 18, 2014 at 10:13 am
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
Consultant - Straight Path Solutions
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
August 18, 2014 at 10:20 am
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
Consultant - Straight Path Solutions
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
August 18, 2014 at 11:17 am
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.
August 18, 2014 at 11:26 am
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
Consultant - Straight Path Solutions
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
August 19, 2014 at 5:20 pm
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.
----------------------------------------------------
August 19, 2014 at 5:46 pm
agreed.
Create a flag column in temp table. Unique identifier for each if statement.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply