CODEWITHSUNDEEP
sql-serverstored-proceduresreporting-services
Liz Roodberg
Liz Roodberg

Reputation: 11

Report Variable Values from Stored Procedure onto SSRS Report

I have a stored procedure with 3 nested loops which works perfectly in Query Analyzer.

I need to create an SSRS report to output the data from the stored procedure.
The stored procedure uses several variables to hold calculated data for the output as it loops through the code.

How do I pull those variables onto my SSRS report? When I add the variables as parameters, SSRS errors with "parameter is missing a value". The value is derived from the stored procedure. I have not found a way to make this connection.

Upvotes: 0

Views: 999

Answers (2)

Grey
Grey

Reputation: 1

Consider having your stored procedure create global temporary tables of the two additional datasets you need. E.g. SP:

Calculate dataset 1 into #t1
Calculate Dataset 2 into ##t2
Calculate Dataset 3 into ##t3
select * from #t1 order by <key1>

In your SSRS report, result 1 = original SP result #t1, then in your 2nd and 3rd tables simply "select * from ##t order by ..." as needed.

The negative of this approach is that the global table stays until the connection is dropped which means concurrency issues carry for an extended period. While you cannot eliminate concurrency completely, you can minimize it by having the DataSet tables for set 2 and 3 "select * into #t2 from ##t2" to localizethe data and then immediately destroy the ##t table, and finally select your localized temp table contents for the related SSRS table. I needed to solve this issue about 5 years ago, and this was by far the cleanest method, and minimized the concurrency issue (two independent requests for the same SSRS report/same global table simultaneously).

Upvotes: 0

benjamin moskovits
benjamin moskovits

Reputation: 5458

Its very annoying but you can only return one set of rows from a proc and for you to pass back anything it has to be in that table. So you will have to add columns to the passed back table which will contain the values you want on your report. I know this means that if you have a thousand rows these values will repeat 1 thousand times but that is the only way to return them.

Upvotes: 1

Related Questions