How to Add a Filter to a Report in SSRS (SQL Server Reporting Services)

In my first post I showed how to create a report, add a datasource and add a dataset to it. Today I'm going to add a filter to that report and it is very important to add a filter because it make easier the user to search data.

First you need to add a parameter to the report data. In my report you can see there is a dataset field called Gender. I'm going to filter my report gender wise. 

(01). Go to report data and right click on parameter folder add a parameter.

you can see there are two fields Name and Prompt. Name is the name of the parameter and Prompt is the display name of the parameter. 

Then name the parameter as you wish. I named it as gender for parameter name and Gender for for parameter prompt

Then you have to set the parameter type. You can set it using Data type combobox. Here my dataset field Gender is a varchar value so I set the type as text.

Then keep the parameter visibility as visible.

(02). Click on the Available Values in report parameter properties. Now you can see three options listed and select the specify values.

Now you have to add and specify values that your report parameter can have.  Gender field can have Male or female. Click on the add button and set the label as Male and value as Male. Click again the add button and set the label value as Female and value as Female. Then click ok.

Now successfully created the report parameter that is used for filter.


(03). Go to report data again and right click on the dataset and go to dataset properties.

In dataset properties view click on filters. Then click on the add button. There you can see Expression, Operator and value lables. In Expression you need to select the field that you are going to filter. So it should be [Gender]. Then set the operator to be (=). Finally the value, this the most important part. In value field you have to give parameter value you pass to the report. value field set with the @ symbol. It should be as [@gender]. Then click ok. 

Now you added the filter to the report successfully. 

(04). Click on the preview tab of the report. Select a the filter value you need to filter the report. Then click on View Report.

Now you know how to add a filter to a report in SSRS. You can improve your report using filters. You can create filters using different parameter types as datetime, booleon, integer instead of text. I will discuss how to group a report in SSRS in my next post. Thank you.