Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Building a Report from the Ground Up-Part 5: Parameters

  • 1 December 2009
  • Author: DustinRyan
  • Number of views: 7133
  • 0 Comments

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:

Region Sales Report 

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

      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

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

ORDER by TerritoryID, LastName

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:

 Parameter

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.

 

Parameter Properties

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!

Print
Categories: Reporting Services
Tags:
Rate this article:
5.0
DustinRyan

DustinRyanDustinRyan

Other posts by DustinRyan

Please login or register to post comments.