Cascading dropdown - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Cascading dropdown

Author

Topic

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2008-02-08 : 02:00:26

Hi, Any1 knows how to create a cascading dropdown in reporting services.
Its like a One-to-many with dropdownlist.
Eg. In my table I have
Table Deparments.
ID Department
1 HR
2 Finance
3 IT

Table Catergory
CatID DepartmentID CategoryName
1 2 Financial Manager
2 2 Admin
3 1 Head HR

On the dropdownlist1 all departments must appear. The Selected department
must only have the category for that department in dropdownlist2.
How can this be accomplished ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-08 : 03:20:22

Create two datasets for populating the dropdowns. For the first dropdown dataset (Department), return all department info from table Departments with value set as DepartmentID. For the second dataset add a parameter called Department and set value to Parameters!Department.value. and write query to retrieve Categories of passed Dept parameter.
On viewing report you will get two params Dept & Categories. On selection of a Dept, the categories dropdown will refresh to contain only those belonging to selected Dept (because we're passing it as a param).

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2008-02-08 : 04:11:45

So you saying that the departments dropdown will have 1,2,3 & not HR, Finance,IT displayed ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-08 : 04:23:59

Nope it will show HR,Finance,It but value passed will be 1,2,3...
You have two properties for parameter 1 value other is display.
So use this query from database for dept
SELECT ID as Value,Department AS Display from Department

In parameters tab map parameter Available values to 'From Query'
select your dataset in datset drodown
select Value from dropdown for Value field
select Display from dropdown for label field.
then it will show HR,IT,.. in dropdown and will pass values 1,2,3...internally. you can check this in report by placing a textbox with expression Pramaeters!Department.value. pass this to second dataset (category) as parameter.

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2008-02-08 : 06:05:19

Thanks.... Much appreciated.
Will try it & let u know.
This is always my first stop when having a sql problem. I always get quick help.

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2008-02-08 : 06:58:41

I get an error.
The report parameter 'Category' has a defaultValue or a ValidValue that depends on the report parameter 'DepartmentID'. Forward dependencies are not valid

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2008-02-08 : 07:18:11

Is this right. I have 3 datasets.
Dataset1 GetAllInfo. Query = FROM Complaint_Details AS a INNER JOIN
Status AS d ON a.Status = d.StatusID INNER JOIN
Department_Details AS e ON a.Department = e.DepartmentID INNER JOIN
Complaint_Origin AS c ON a.ComplaintOrigin = c.ComplaintOriginID INNER JOIN
Complaint_Type AS b ON a.ComplaintType = b.ComplaintTypeID INNER JOIN
Complaint_Category AS f ON a.Category = f.CategoryID
WHERE (a.Department = @DepartmentID) AND (f.CategoryID = @CategoryID)

DataSet2 = Department. Query = SELECT DepartmentID, Department FROM Department_Details

DataSet3 = Category. Query = Select CategoryID, DeparmentID, CategoryName from Complaint_Category
WHERE (DepartmentID = @DepartmentID)

DataSet1 should execute last. How do I do this ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-08 : 07:18:14

You need to define Department before category. both parameters and also datasets. the order is dept followed by Cat

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2008-02-11 : 03:32:14

Thanks all working fine......

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-11 : 04:10:25

Cool.You are welcome.