In a recent article (Reporting Services Parameters Not Updating), I showed how report parameters are not automatically updated upon adding parameters to a stored procedure. In this quick blog, I am going to show you how to make selecting the StateProvinceID a little easier (and a little more usable) by loading a drop down list box with each of the values. Download my previous example and a finished version of this blog (SSRS 2008 R2) HERE. Assuming that you have the AdventureWorks database installed on your local system and a shared data source configured to use it, add the above report to a project or recreate it from using my previous article as a guideline.

Step 1: Create the usp_GetStates Stored Procedure that will be used to load the drop down list box. Fire up SQL Server Management Studio (SSMS) and connect to your local AdventureWorks database.

Script 1: Stored Procedure

USE [AdventureWorks]

GO

CREATE PROCEDURE [dbo].[usp_GetStates]

AS

SELECT

      DISTINCT

      s.Name AS StateName

      , StateProvinceID

FROM

      Person.StateProvince s

WHERE

      s.CountryRegionCode = 'US'

ORDER BY

      s.Name

Step 2: Add a new dataset that will be used to gather all of the StateProvinceID’s. Set the query type to Stored Procedure and select the stored procedure created above (shown in figure 1) and click OK.

Figure 1: Dataset Properties

Step 3: Update the StateProvinceID report parameter to get the values from the dataset you just created. To do this, double click on the StateProvinceID parameter and then click on the Available Values tab. Select the “Get values from a query” option and then select your dataset created in step 2. This is shown in figure 2.

Figure 2: Update the Report Parameter Properties

Click OK and then preview the report. You should then see a drop down list box with all of the states listed. If you select Florida from the parameter, your results may look like what is shown in figure 3.

Figure 3: Sample Report Results

I hope that you have enjoyed this post and if you have any questions, please do not hesitate to post a reply below.

Until next time, “keep your ear to the grindstone” – Good Will Hunting

Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works Consultants

Email: bmcdonald@pragmaticworks.com | Blog: BI Developer Network

Convert with DTS xChange  | Develop with BI xPress  | Process with TaskFactory | Document with BI Documenter