How do I pass a report parameter value to stored procedure

Question

Post reply

How do I pass a report parameter value to stored procedure

fstop

Default port

Points: 1482
More actions
September 29, 2014 at 11:55 am

#297042

I have created a report that uses a stored proc to provide a list of choices to the user as a parameter. The stored proc returns a Name and an ID. In the @Name parameter, I set Available Values as follows:
Dataset: The stored proc (dataset) proving the list of choices
Value Field: the ID
Label Field: the name
When the user selects one of the choices, I need the ID of the selection to be used as input to the main stored proc that populates a table. How do I accomplish that? Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63501 More actions · Answer 1

Say you have this stored procedure to return a list of cities with their corresponding CityIDs (which are numeric)

CREATE PROC uspGetCityIDs

AS

SELECT CityID, CityName

FROM Cities

ORDER BY CityName;

then you could create a dataset based on that. Nothing doing.

Then if you had another dataset (your main report dataset) that showed some data about that city, you could filter it like this:

CREATE PROC GetCityData

@CityID INT

AS

SELECT [field list]

FROM CityData

WHERE CityID = @CityID

Then use the uspGetCityIDs stored procedure to populate the dropdown, and specify CityID as the VALUE field and CityName as the LABEL field, and you're off to the races.

Wait... What do you mean by "When the user selects one of the choices, I need the ID of the selection to be used as input to the main stored proc that populates a table. How do I accomplish that?" Are you trying to write the choice back to a table somewhere? If so, you would do it inside the stored procedure that your report is based on. There would be an INSERT statement that used the parameter to write the value to some table in your database.

fstop Default port Points: 1482 More actions · Answer 2

Thanks for your reply. You have it spot on. However, the Value field in the dropdown list (CityID in your example) isn't making the connection to the main dataset query where clause (even though the spelling is the same). How does that connection get made?

Regarding your second para, I'm not trying to insert anything. I just want the selection made by the user in the dropdown to be used by the main query.

pietlinden SSC Guru Points: 63501 More actions · Answer 3

However, the Value field in the dropdown list (CityID in your example) isn't making the connection to the main dataset query where clause (even though the spelling is the same). How does that connection get made?

Assuming that each CityID uniquely identifies a City (in my example), you don't pass the City Name to your stored procedure at all. That's why I posted the dummy stored procedure. You pass the CityID, which is a number. You just show the CityName in the dropdown. (That's why the dataset has two columns). Does that make sense?

fstop Default port Points: 1482 More actions · Answer 4

Yes, of course. I understand that. Are you inferring that SSRS automatically knows to look for a dataset that has a variable with the same name as the VALUE field (@CityID in your case) in order to connect the two? In other words, there should be nothing I have to do manually to make that connection?

fstop Default port Points: 1482 More actions · Answer 5

I finally got it to work. I had to manually add a parameter to the main dataset that links to the parameter I set up that's associated with the dataset for the dropdown.

Thanks for your help.

pietlinden SSC Guru Points: 63501 More actions · Answer 6

The only way for SSRS to know is if you specify a dataset for the parameter to get its values from. If you right-click on your parameter, and go to Parameter Values and then down to Available Values then specify the Dataset the values come from. Under that are two dropdowns - the first is for the value that gets passed back to the report-level parameter, and the other is what the user sees.

So say you were choosing a CustomerID from (CustomerID, CustomerName). The value field would be CustomerID and the Label field would be CustomerName.

You would just make sure that Customers dataset was sorted by CustomerName, not CustomerID.

Make sense now?

pietlinden SSC Guru Points: 63501 More actions · Answer 7

Oh... I thought you already had a report-level parameter... maybe that was part of my problem. Glad you got it sorted.