Add Parameter to SSRS Report

In a previous posting, I showed you how to create a Vendor listing report using SSDT and SSRS.  This was a handy report, but it showed all of the data in the tables and you could not search on specific criteria.  This week I am going to show you how to create a parameterized version of the report so you can search by area code or really any part of the phone number.

Below is the vendor report that we created previously.

Clicking the Preview tab shows all of the data.

 

What we need to do is add a parameter to the report so we can search on vendors within a certain Area Code.  First on the left side on the Report Data tab, double click Dataset1 which has our query.

This brings up the query that the report uses.

 

We want to add the following “WHERE” clause so we can search by area code in the phone number.

where s.phonenumber like '%' + @Area_Code + '%'

The @ sign is the parameter name that we chose, so the where clause when it is run looks something like this:

s.phonenumber like ‘%@Area_Code%’.

 

When we put an @ sign in our sql statement, a parameter is automatically created with the name we picked in the Report Data tab, under Parameters and on our report.

 

Clicking the Preview tab now shows an empty Area Code box.

 

I put in 423 for an Area Code and click View Report and now only vendors with an area code of 423 show up.  You can of course put additional parameters in your report.

 

This was a quick posting to show how easy it is to add parameters to your SSRS reports.