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 6: Cascading and Multivalue Parameters

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

In Part 5 of Building a Report from the Ground Up, I showed you how to add a parameter to your report with a drop down list of selectable values for your users to select from. In Part 6, I’ll show you how to create cascading parameters as well as parameters that allow you to select multiple values. In my example, I’ll be using the report I created in Part 5, so check that out if you haven’t already done so. Don’t forget I’m using the Adventure Works database, as well as RS 2008.

The first thing I’ll need to do is to add an additional parameter to my base data set so that I can specify which Sales person I’d like to view. I declare my new parameter and adjust my WHERE statement. This is what my new base stored procedure looks like:

ALTER PROCEDURE [dbo].[sp_Sales]

      -- Add the parameters for the stored procedure here

      @Terr nvarchar(50)

      ,@LastName 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

WHERE Sales.SalesTerritory.Name = @Terr

and Person.Contact.LastName = @LastName

ORDER by TerritoryID, LastName                     

END

 Refresh the base dataset so that you can see your new parameter, @LastName.

Secondly, I need to create a stored procedure to pull back the values I want to populate the drop down box of my second parameter, which in this case will be all of the sales people that work for Adventure Works. Because I want the list of sales people to depend upon the territory I select, the stored procedure will have one parameter, as seen here:

CREATE PROCEDURE [dbo].[sp_SalesPeople]

      -- Add the parameters for the stored procedure here

      @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.Name, Person.Contact.FirstName + ' ' + Person.Contact.LastName AS FullName, Person.Contact.LastName

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

WHERE Sales.SalesTerritory.name = @Terr

Create a data set that uses this stored procedure. Just a quick note: Make sure that you use the same parameter name in this stored procedure as you used in the base stored procedure. Reporting Services 2008 will recognize automatically that you are trying to create cascading parameters when you add the new data set with a parameter of the same name.

After adding the new data set, right-click the @LastName parameter in your Report Data window inside Visual Studio and Report Parameter Properties. Click Available Values, and make the selections as seen below:

 Sales Report 1

The Value field has to be the LastName field since that’s what I’m using in my stored procedure. I can use the FullName field  as my Label since that will be more appealing to the end user. Click OK and then click Preview. Now when I run the report and I select a Territory from the Territory drop down list, in the Sales Person drop down list should only show Sales Persons associated with the selected territory. Very nifty.

Just a tip: To change the parameter prompt that the users will see, right-click the parameters in the Report Data pane, click properties, and change the Prompt to something that makes more sense, like “Select Territory:” and “Select Sales Person:”.

 Prompt

But what if I want to be able to select multiple sales people from the Sales Person drop down list? Well then I’ll need to use something called a Split Function. A Split Function splits up a string of characters based on a delimiter, such as a comma (,) or a pipe (|). Split Functions can be found all over the web for you to use, so I won’t get into how to create one. Just Google around and you should be able to find one. To add the Split Function to your database, expand the Functions node in your Adventure Works database, right-click on Table Value Functions, and click New Inline Table Valued Function and add your Split Function there. You can see mine here:

USE [AdventureWorks]

GO

/****** Object:  UserDefinedFunction [dbo].[fnSplit]    Script Date: 11/29/2009 20:00:46 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[fnSplit](    @sInputList VARCHAR(8000) -- List of delimited items 

, @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items

)RETURNS @List TABLE (item VARCHAR(8000))

BEGIN

DECLARE @sItem VARCHAR(8000)

WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0

BEGIN

SELECT 

@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))), 

@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList)))) 

IF LEN(@sItem) > 0 

INSERT INTO @List

SELECT @sItem

END

 

IF LEN(@sInputList) > 0

INSERT INTO @List

SELECT @sInputList -- Put the last item in

RETURN

END

Then I need to make an adjustment to my base stored procedure to allow the user to select multiple sales people. In my WHERE statement, I need to call the Split Function. There are different Split Functions out there, so the syntax for using yours may be different, but I’ll show you how I called my Split Function for an example:

WHERE Sales.SalesTerritory.Name = @Terr

and Person.Contact.LastName in (select item from dbo.fnsplit(@LastName,','))

Commit the changes to your stored procedure and refresh the fields in the appropriate data set. Then right-click the @LastName parameter, click properties, and click the check box that says “Allow multiple values.” Now you should be ready to rock and roll. Click preview and enjoy being able to select multiple sales people at one time.

Next time we’ll cover how to use Expressions with your reports to give your report some very cool, conditional formatting.

Print
Categories: Reporting Services
Tags:
Rate this article:
No rating
DustinRyan

DustinRyanDustinRyan

Other posts by DustinRyan

Please login or register to post comments.