Lesson 2: Create a SSRS report which excepts input parameter (Tutorial)

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 movie details. Let's say there is a requirement to show only one movie 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 MovieTitle the same for Prompt and data type is Text. Click ok.

Now that we have created a parameter we need to use it to filter the database. 

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. 

Right click on the Movies dataset and select the option Dataset properties.

To add the filter to our SQL Select we just add the following where clause: 

WHERE Title = @MovieTitle 

@ MovieTitle 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 MovieTitle parameter with empty text box. I will type “Hum” and click view report. As you can see the report was filtered and I get only one row for Hum. 

NOTE: Very often you will use default values, calendars or drop down boxes as your filter condition.

Follow below steps to create a dropdown instead of values in textbox. 

Create a new Dataset “Title”

The dataset gets added as shown below:

Right click on the newly created parameter and select option Parameter properties as show below

Report Parameter properties window will be launched. 

Navigate to Available values tab, check the check box “Get values from a query”. Then select the dataset Title we had created moment ago. Select value field and label field as column Title available in database title. Then click OK.

Next click the preview option in the report designer. You can see a drop down instead of the text box from previous example.

Select Hum in the drop down and click view report. 

We can see the report was filtered and we get only one row for Hum.

Conclusion:

SSRS report created which excepts input parameter.


Related topics:

Lesson 1: CREATE A SIMPLE SSRS REPORT (TUTORIAL)

Lesson 3: CREATE DRILL DOWN VIEW IN SSRS (TUTORIAL)
Lesson 4: NAVIGATING BETWEEN SSRS REPORTS (TUTORIAL)
Lesson 5: WORKING WITH SUB REPORT IN SSRS (TUTORIAL)
Lesson 6: WORKING WITH EXPRESSIONS IN SSRS (TUTORIAL)
Lesson 7: WORKING WITH GRAPHS AND INDICATOR IN SSRS (TUTORIAL)
Lesson 8: MISCELLANEOUS OPTIONS IN SSRS (TUTORIAL)