Secondary drop down parameter list reliant on first (SSRS)
Posted on 2009-03-31
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
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!