By: K. Brian Kelley | 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.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:
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.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:
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
- Review these related tips:
- Creating Dynamic Report Filters in SQL Server Reporting Services (SSRS)
- Custom control and setup of SQL Server Reporting Services report parameters from a web page
- Dynamically Control Data Filtering in SQL Server Reporting Services Reports
- SQL Server Reporting Services Conditional Formatting
- Display Graphics Dynamically Based on Data Content in SQL Server Reporting Services
- Create Centralized Report Headers Using Subreports in Reporting Services
- Passing parameters and other options directly through a URL in Reporting Services
- How to Optimize Report Parameter Dropdowns in SQL Server Reporting Services (SSRS) 2005
About the author
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
Article Last Updated: 2009-12-23
