This tutorial explains how to create a simple parameter in SSRS report and use it to filter your data. In this tutorial I will SSRS 2008 R2 and it should be the same in SSRS 2008 and SSRS 2012.
Below you can see the table which shows twelve months average temperature in Majorca. Let's say there is a requirement to show only one month at a time using parameter.
In my design view you can see I already have a dataset and a report table. In order to add a paramater to the report I open "Report Data" pane, right click parameters folder and click add parameter.
Now we need to setup Name (used internally), Prompt (which is what users will see), and Data Type. In this case Name is Month the same for Prompt and data type is Text. Click ok.
NOTE: In this tutorial we won't be discussing importance of data types and other option available but we will write new blog post to cover them.
Now that we have created a parameter we need to use it to filter the datase.
NOTE: There are multiple ways to filter data. We use the most common one and add filter to the SQL statement. Very often you might use stored procedures and pass parameters as a stored procedure argument.
To add the filter to our SQL Select we just add the following where clause:
WHERE Month = @Month
@Month is our parameter and will be replaced with whatever we type in the parameter text box when we run the report.
Let's preview our report.
We can notice Month parameter with empty text box. I will type January. and click view report.
As you can see the report was filtered and I get only one row for January.
NOTE: Very often you will use default values, calendars or drop down boxes as your parameters and I will write some blog posts about it soon.
I hope this SSRS tutorial will help you to create a simple parameter.
Take care
Katie
