Solved

Secondary drop down parameter list reliant on first (SSRS)

Posted on 2009-03-31
1
859 Views
Last Modified: 2013-11-10
I am attempting to learn how to create a report using parameters with multiple drop down lists, in which the first drop down list will limit the available values in the second drop (and potentially beyond) down list.  Is this possible, and if so how can this be accomplished?

For example (using AdventureWorks DB) if I have my first dataset:

SELECT * FROM Sales.SalesOrderHeader WHERE SalesPersonID=@SalesPersonID

The second dataset:

SELECT SalesPersonID, LastName + ', ' + FirstName AS FullName
FROM Sales.vSalesPerson
ORDER BY LastName, FirstName

I go into Reports -> Report Parameters, and change the Data Type to Integer, and value to From Query.  In those drop down boxes, I select my 2nd data set, and then SalesPersonID for Value Field, and FullName for Label Field.

If I preview this report, it shows exactly what is expected.  How can I say, select the TerritoryID first, and therefore limit the available sales people in the second drop down list to those only in the territory I selected in the first drop down?

Obviously, this is just an example, but I intend to use this for our company so they can select first by one aspect (say a region) and then a particular sales department, and so forth and so on.

Thank you kindly for your assistance!
0
Comment
Question by:tscd
1 Comment
 
LVL 11

Accepted Solution

by:
shorak earned 500 total points
ID: 24037386
Hi.. this isnt  hard to do..

First, you need to create a dataset that will return a unique list of regions.. e.g

Select  distinct RegionDescription, RegionCode from RegionTable

Create your first parameter and give it a name eg. paraRegion and set it up so it gets the values from the dataset you have created.


Now create a second dataset that will give you the list of your sales departments based on the region select in your first parameter.. eg

Select distinct SalesDepartmentDescription, SalesDepartmentCode from SalesDepartmentTableWHERE Regioncode in (@paraRegion)

Now create your second report parameter and set it up so that it gets the values from the 2nd dataset you have created.

The important thing to be aware of is that the report parameters have to be sorted in the order they will be used.. ie the paraRegion parameter needs to be at the top of the parameters list in the parameter setup screen followed by the sales deparment parameter.

If you wanted to add a 3rd drop down parameter that gives you a list of salesperson based on the selected sales department then just create another dataset and create a sql statement that uses the salesdeparment parameter in the where clause and then create a report parameter that refers to the dataset.

This is also known as cascading parameters as each paramater depends on what is selcted in another parameter.

Hopefully this will work.

Rob




0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now