Solved

Secondary drop down parameter list reliant on first (SSRS)

Posted on 2009-03-31
1
869 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

729 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