In Part 4 of Building a Report from the Ground Up, I showed you how to create a professional looking template for use with all your reports. I also showed you how to use the Matrix data region. In Part 5, we’ll cover how to add parameters to your report using stored procedures. So let’s get this party started!
I’ve written a stored procedure that returns a list of Sales people from Adventure Works. The stored procedure also returns the sales territories, sales year to date, and last year’s sales. Here’s what the query looks like:
SELECT Sales.SalesTerritory.TerritoryID, Sales.SalesTerritory.Name, Person.Contact.FirstName+ ' ' +Person.Contact.LastName AS FullName, Sales.SalesPerson.Bonus,
Sales.SalesPerson.CommissionPct, Sales.SalesPerson.SalesYTD, Sales.SalesPerson.SalesLastYear
FROM Sales.SalesTerritory INNER JOIN
Sales.SalesPerson ON Sales.SalesTerritory.TerritoryID = Sales.SalesPerson.TerritoryID INNER JOIN
HumanResources.Employee ON Sales.SalesPerson.SalesPersonID = HumanResources.Employee.EmployeeID AND
Sales.SalesPerson.SalesPersonID = HumanResources.Employee.EmployeeID INNER JOIN
Person.Contact ON HumanResources.Employee.ContactID = Person.Contact.ContactID AND HumanResources.Employee.ContactID = Person.Contact.ContactID
ORDER by TerritoryID, LastName
And here’s what my report looks like:
Just a note: To add datetime parameters that allow the user to select a beginning and ending dates, declare 2 variables (For example: @startdate datetime, @enddate datetime) and in the WHERE clause do something like: WHERE date >= @startdate and date <= @enddate.
But what if I want my user to be able to select which Sales Territory they wish to view? Then I’ll need to add a parameter. I’ll need to change my stored procedure a little bit by declaring a variable and adding a WHERE statement. To make a change to an existing stored procedure, simply right-click the stored procedure and select Modify. Make the necessary changes to the stored procedure and execute it. Here’s my new stored procedure with the variable and WHERE statement:
ALTER PROCEDURE sp_Sales
-- This is where you add your parameter variables
@Terr nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
--This is the WHERE clause where I specify that I only want to see records
--where the SalesTerritory.Name is equal to my @Terr parameter
WHERE Sales.SalesTerritory.Name = @Terr
For the changes to my stored procedure to be visible in my existing report, I need to refresh my data set by right-clicking my data set in the Report Data pane. Then click Refresh Fields. Now you can see the newly created parameter underneath the Parameter folder in the Report Data Pane:
So now if I click the Preview Tab, I can see the input text box for me to specify which Territory I’d like to view. But this isn’t good enough. I want a drop down box to appear with the territories listed so that my users simply select the territory, rather than having to type the territory out. To do that, I’ll write another stored procedure that is simply a list of the available territories. This is what it’s query looks like:
SELECT Distinct Name
FROM Sales.SalesTerritory
ORDER BY Name
This is just a list of the territories to populate the drop down list. Create a new data set using this stored procedure. Next, right-click on your parameter in the Report Data pane and select Report Parameter Properties. Click Available Values and select Get Values From Query. Specify which data set to use and select the correct fields to use as the Value field and the Label field.
Click OK and click Preview. Now when you preview the report, you see the drop down list to select from. Next time we’ll cover how to add parameters that a user can select multiple values as well as cascading parameters, so keep checking back!
Nice post. I'm using it in a Quickstart :)