Thursday, May 28, 2015

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:

1 comment:

Popular Posts

Get Sponsored by Big Brands