Solved

Secondary drop down parameter list reliant on first (SSRS)

Posted on 2009-03-31
1
865 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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.

739 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