Secondary drop down parameter list reliant on first (SSRS)

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!
tscdAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
shorakConnect With a Mentor Commented:
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
All Courses

From novice to tech pro — start learning today.