Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Getting started with report filters in SQL Server Reporting Services SSRS


By:   |   Updated: 2009-12-23   |   Comments (2)   |   Related: > Reporting Services Dynamic Reports

Problem
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.

Solution
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:

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:

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:

SELECT 
    
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.LastNameP.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:

SELECT 
    
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.LastNameP.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:

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:

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



Last Updated: 2009-12-23


get scripts

next tip button



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

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

Sudeep,

I would check out this tip - http://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/.

Thank you,
Jeremy Kadlec
Community Co-Leader


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

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


Learn more about SQL Server tools