Getting started with report filters in SQL Server Reporting Services

By:   |   Comments (2)   |   Related: > Reporting Services Dynamic Reports


I want to use user-definable parameters to provide filtering in my reports for SSRS, but I'm not sure how to proceed. I'm basically looking for the case where the user is able to set the value and then execute the report. For instance, I'm looking for the case where the user is able to set the start and end date of a period to report on. In this tip we walk through the steps to implement basic report filtering in SSRS.


Let's start with a basic report.

Here's a report which queries against the AdventureWorks2008 sample database and basically pulls together the amount of money spent by each named customer. The report is shown in Figure 1.

Figure 1:

SSRS Filter SetValue 01

Let's say we want to know what a customer spent during a certain time interval. In this case we'll need to know the start and end dates.

We start by adding a parameter, as in Figure 2:

Figure 2:

SSRS Filter SetValue 02

The Name: is the parameter name and what we'll use for filtering when modifying the query. The Prompt: is what will be shown on the report to tell the user what to do. Since we're looking at specifying dates, we'll set the Data type: accordingly.

We'll need two of these, one for the start date and another for the end date. Now that we have our two parameters (StartDate and EndDate), let's put them to work for us.

The original query for our report looks like this:

    P.LastName + ', ' + P.FirstName [CustomerName] 
  , SUM(SOH.TotalDue) [TotalSpent] 
FROM Sales.SalesOrderHeader SOH 
  INNER JOIN Sales.Customer SC 
    ON SOH.CustomerID = SC.CustomerID 
  INNER JOIN Person.Person P 
    ON SC.PersonID = P.BusinessEntityID 
GROUP BY P.LastName, P.FirstName 
ORDER BY [CustomerName];

We're going to need to add a WHERE clause to filter the dates. We do that by modifying the Dataset properties. If we navigate to where we can see the original query, we'll need to modify it like so:

    P.LastName + ', ' + P.FirstName [CustomerName] 
  , SUM(SOH.TotalDue) [TotalSpent] 
FROM Sales.SalesOrderHeader SOH 
  INNER JOIN Sales.Customer SC 
    ON SOH.CustomerID = SC.CustomerID 
  INNER JOIN Person.Person P 
    ON SC.PersonID = P.BusinessEntityID 
  WHERE SOH.OrderDate BETWEEN @StartDate AND @EndDate 
GROUP BY P.LastName, P.FirstName 
ORDER BY [CustomerName];

Pay particular attention to the line with the WHERE clause. Note that while we called the parameters StartDate and EndDate, when we use them, we actually have to use the @ as a prefix. Now if we deploy the report, we'll see it's changed to include our parameters, as in Figure 3:

Figure 3:

SSRS Filter SetValue 03

Now, once the values are specified, we can view the report and we'll see that the data has changed accordingly. For instance, let's select 1/1/2001 and 12/31/2001 as our dates. We'll see the results in Figure 4:

Figure 4:

SSRS Filter SetValue 04

And if you compare Figure 1 and Figure 4, you'll see that the customers and the amount they've spent is different. Figure 1 represented all named customers and all of their orders. Figure 4 represents the filtered result.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Sunday, August 11, 2013 - 3:23:42 PM - Jeremy Kadlec Back To Top (26229)


I would check out this tip -

Thank you,
Jeremy Kadlec
Community Co-Leader

Sunday, August 11, 2013 - 10:37:36 AM - sudeep Back To Top (26225)

I am getting 8/11/2013 instead of 11/8/2013. Please tell me what should i do for that. I am a beginner.

get free sql tips
agree to terms