?
Solved

Secondary drop down parameter list reliant on first (SSRS)

Posted on 2009-03-31
1
Medium Priority
?
872 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 1500 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 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