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


ssrs 2008 report pass parameter values to stored proc


ssrs 2008 report pass parameter values to stored proc

Author
Message
wendy elizabeth
wendy elizabeth
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1630 Visits: 780
In an SSRS 2008 r2 report, I would like to know how to pass parameter values to a stored procedure that is called within the
main dataset of the report.

In an SSRS 2008 r2 report, I want to add the following sql to the main dataset of the report:

DECLARE @endYear SMALLINT = 2014,
@singlecalendarID INT = 1,
@PeriodSchedules CHAR(1) = N'C'
CREATE TABLE roomResults(
personID INT,
firstName VARCHAR(35),
middleName VARCHAR(30),
lastName VARCHAR(40),
suffix VARCHAR(50),
fullName VARCHAR(108)
)


INSERT #roomResults
EXEC [dbo].[sproom] @endYear, @calendarID, @Schedules

When I run the sql in SSIS manager, it runs fine. However when I try to run the sql listed above,
I am having problems getting the sql to run. I get the error message that it does not recognize the
parameter value for @endYear.

Thus to pass parameter values to a stored procedure [dbo].[sproom] from an SSRS report is what I believe the issue is.
Thus can you tell me the following:

1. For the 3 parameter values (@endYear, @calendarID, @Schedules) that the stored procedure called [dbo].[sproom] needs,
do I set these up as the exact parameter names to the ssrs 2008 r2. If so, what kind of values do I give these parameters like the available or default values. To the main dataset that uses these parameters, do I set @endYear, @calendarID, @Schedules
as parameters to the main dataset?

2. If I do not setup the parameter values @endYear, @calendarID, @Schedules so the stored procedure called [dbo].[sproom] as
parameters to the ssrs 2008 r2 report, how do I pass the parameter values to the stored procedure?

3. The should the parameter values to the stored procedure be the exact parameter names used in the SSRS 2008 r2 report?

Thus can you tell me and/or show me in code how to pass parameter values to a called stored procedure from an SSRS 2008 r2 report?
Tony Sweet
Tony Sweet
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 66
First I would create this stored procedure within your instance and not within SSRS.

Then when you create the dataset, under Query type select stored procedure. Find your procedure name and click it. If it is not there then you do not have your data source configured correctly.

Once you find it and select it click the Refresh Fields button. This will automatically add all of the stored proc parameters to your report.

The answer on this MSDN question should help:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/f7b85430-66f3-4932-a69c-3e41ddee4f3a/how-to-pass-report-parameters-to-stored-procedure-in-report-builder-30?forum=sqlreportingservices

- Tony Sweet
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