Query Parameter in SSRS

Query Parameters are used to filter the result set return from the database similar to where condition applied in a T-SQL statement. It limits the number of rows return back from the database to the report, thus improving the performance.

Let's create a report to see how Query Parameters work.

For this, we will  apply Query Parameter on a Tabular Report we created in an earlier article.

Step 1: Create a simple Tabular Report
First Create a simple Tabular Report using above link. Once report is created we would apply Query Parameters on the report

Step 2: On Report Data Tab, Click Parameters>Add Parameter

Configure Report Parameter Properties as below and click OK

Step 3: This will add GroupName Parameter under Parameters like below.

Step 4: Now let's apply the Parameter on DataSet to filter the result-set. In Report Data Tab right click DataSet1 under Datasets and then click Dataset Properties

Step 5: In Query Tab under Dataset Properties, change the Query to

select  * from HumanResources.Department where GroupName = @GroupName

Step 6: Select Parameters Tab, you will find Parameter Name @GroupName that we added in the query in Where clause in previous Step. Select Parameter Value as @GroupName from the list (created in step 3) and click OK

Step 7: At this step we are done with adding Query Parameter to the Report. Now let's build the Solution and run the report. The Parameter Group Name that we created started appearing in the report to filter the result set. Enter Manufacturing in the text-box and click View Report

Look at the output, the report is filtered with Group Name as Manufacturing