SSRS- Cascading Parameter Report

Cascading parameters provide a way of managing large amounts of report data. We can define a set of related parameters so that the list of values for one parameter depends on the value chosen in another parameter.

1. In this case, the first parameter is independent and might present a list of Department.
2. Second parameter is dependent on the value of the first parameter such as Sub-Department.

When the user views the report, the values for both the Department and Sub-Department parameters are used to filter the report data.

For cascading parameterized report, we are assuming that we have the following data tables in our database called DemoDatabase –

Reservation Master
Project Code	Project Name	Project Manager		Project Budget
PC001	Reservation	Mike Towery		125,000
PC002	Attendance	Chris Gray		155,000
PC003	Human Resources	Kimmy Wang		225,000
Primary Key= Project Code
Department Master
Dept Id	Department Name
D001	Database
D002	Testing
D003	IT
Primary Key = Dept Id

Employee Master
Emp Id	Employee Name	Dept Id	EmployeeRatePerDay
E0001	Ryan Arjun	D001	240
E0002	Tony Towery	D002	230
E0003	Lucy Gray	D003	250
E0004	Will Smith	D001	245
E0005	Chao Milk	D002	225
E0006	Chris Gyal	D003	210
E0007	Bill Gray	D001	190
E0008	Red Bill	D002	210
E0009	Tom Ramsay	D003	200
Primary Key = Emp Id

Reservation Staff
Project Code	Emp Id
PC001	E0001
PC002	E0002
PC003	E0003
PC001	E0004
PC002	E0005
PC003	E0006
PC001	E0007
PC002	E0008
PC003	E0009
Composite Key  ( Unique Key) = Project Code + Emp Id

On the basis of the above tables, I created a cascading parameterized video captured report in Microsoft SQL Server Data Tools.

You can view all the steps "How to create a cascading parameterized report in Microsoft SQL Server Data tool" in this video here: