SQL Server Reporting Services allows the use of parameters to make reports dynamic. One can pass these parameters into textual SQL statements and stored procedures. One thing that I’ve learned over the years of using Reporting Services is that if you change your query or stored procedure, those changes will also need to be made to the report using them. Otherwise you may get an error or incorrectly filtered results. So, if you happen to add a new parameter to a stored procedure reference in a report, you will need to manually add that parameter to the reports that use them.
In this article, I am going to show you exactly what I mean. Let’s start off by creating a stored procedure to get a listing of contacts from the AdventureWorks database. The commented lines will be uncommented later on and are a key part of this article. For this article, I am using SQL Server 2008 R2 Reporting Services and the AdventureWorks database available for download on CodePlex.
Script 1: Create the Procedure
CREATE PROCEDURE [dbo].[usp_GetContactsForState]
-- @StateProvinceID INT --15 is FLORIDA
c.FirstName + ' ' + c.LastName AS FullName
, s.Name AS StateName
JOIN Person.StateProvince s ON a.StateProvinceID = s.StateProvinceID
JOIN Person.Contact c ON c.ContactID = a.AddressID
s.CountryRegionCode = 'US'
--AND s.StateProvinceID = @StateProvinceID
After creating the stored procedure in the AdventureWorks database, leave it open. We will need to update it later.
Now let’s create a simple report that uses this stored procedure and just outputs the results into a basic tabular report layout. After you add a new report item to your project, navigate to your Report Data pane. If you can’t see it, hit CTRL+ALT+D or select to view it under the View menu option. Add a new dataset by right clicking the datasets folder and set the settings like those shown in figure 1 below.
Figure 1: Dataset Properties
Click OK to save your new dataset. Next, just add the table object to your design surface and drag your columns over to your table as shown in figure 2 below.
Figure 2: Sample Report Layout
I have also added a title to the report, but you don’t need to in order to duplicate this issue. Execute your report and verify that the report runs as expected. It should return ALL of the contacts in the AdventureWorks database. Next, go back to your stored procedure in SQL Server Management Studio (SSMS) and change the CREATE to ALTER and uncomment the lines. Your new stored procedure should look like the one that I have in script 2 below.
Script 2: Alter Stored Procedure
ALTER PROCEDURE [dbo].[usp_GetContactsForState]
@StateProvinceID INT --15 is FLORIDA
AND s.StateProvinceID = @StateProvinceID
Click Execute in SSMS or F5 to ALTER your stored procedure to include the new parameter. Go back to your report and execute your report. You should receive an error similar to the one in figure 3.
Figure 3: Error
Click on the Design tab and then go back to your Report Data pane. Double click your dataset and click the “Parameters” tab. As shown in figure 4, you will notice that the parameter is not properly mapped. Update the parameter mapping as shown in figure 5.
Figure 4: Parameter Mapping Missing
Figure 5: Parameter Mapped
Entering in [@StateProvinceID] automatically will create a report parameter with default settings. Click OK to save your dataset properties and then preview your report. You should be prompted for the State Province ID. Enter in 15 as the StateProvinceID and you should see similar results to the figure 6. Note that I have modified the presentation of the report slightly to improve its appearance.
Figure 6: Finished Report Sample
As you can see, the results are filtered with the results of Florida. What you can also see in this picture is that the StateProvinceID was added to the report parameters folder.
In conclusion, if you alter your parameters at the stored procedure level, be sure to go back and map your parameters at the report level to be sure that your reports continue to work and work as designed.
Since I am writing this in order to help others and if you have found this article helpful, please rate it below. I hope that you have enjoyed this article 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, MCSDBusiness Intelligence Consultant – Pragmatic Works Consultants
Email: firstname.lastname@example.org | Blog: BI Developer Network
Convert with DTS xChange | Develop with BI xPress | Process with TaskFactory | Document with BI Documenter