Cascading of Parameters in SSRS
In many Reports you have a requirement like you want to display report parameter based on other
Report paramters
Let me Explain through Example
--Create a Table for Demo Purpose
create table Projects
(
ProjectName nvarchar(50),
ResourceName nvarchar(50),
Hours int,
Cost float
)
insert into Projects values('ProjA',1,8,3000),('ProjA',2,7,7000),('ProjA',1,3,3000),('ProjB',4,8,3700),('ProjB',1,5,3800),('Projc',1,5,3800)
select * from Projects
--Create a Table for Demo Purpose
create table Projects
(
ProjectName nvarchar(50),
ResourceName nvarchar(50),
Hours int,
Cost float
)
insert into Projects values('ProjA',1,8,3000),('ProjA',2,7,7000),('ProjA',1,3,3000),('ProjB',4,8,3700),('ProjB',1,5,3800),('Projc',1,5,3800)
select * from Projects
You want to display in your report how many Resources have worked in a Project for how many Hours
You have two Parameters in Your Report
1-Project Name
2-Resources
Now You want that whenever user Preview a Report he should select the Project Name
Select Project Name will automatiically Popup the Resources working for that project
Suppose a User Select Project A
Then in Resources filter it will show all the Resources working on Project A...
Means the Report will filter Data Based on Resources working for a Project
Means the Report will filter Data Based on Resources working for a Project
Creating dataset for ProjectName
For this first create a Dataset -
DSTProjectNames
--That will Fetch all Project name
select projectname from Projects
--This will Display Name Like
Proj A
Proj B
Proj C
click ok
Now add a Parameter for ProjectName
-Go to Parameters
-Right Click
-Add parameter
-ProjectName
-Allow Multiple Values Checked
-Available Values
-Check Radio Button -Get Values from a Query
-Dataset-DSTProjectNames
-Value field-ProjectName
-Display field-ProjectName
click ok
Creating Dataset for ResourceName
Now For Resource Name
create a Dataset-DSTResourceName
In Query-
select resourcename,projectname from projects
Go to Dataset properties of DSTResourceName
Filter
ADD-ResourceName
Operator-In
Value-Double click fx sign-ADD your Parameter-ProjectName
click ok
Creating Parameters for ResourceName
-Go to Parameters
-Right Click
-Add parameter
-ResourceName
-Allow Multiple Values Checked
-Available Values
-Check Radio Button -Get Values from a Query
-Dataset-DSTResourceName
-Value field-ResourceName
-Display field-ResourceName
click ok
Now in your Main Dataset
You want to filter your Report Based on ResourceName
-Create Dataset for Main Report
Select projectname,resourcename,cost, hours from projectname
where resourcename in (@Resourcename)
This @Resourcename is your parameter that will filter your Report Based on ResourcesName
Click ok
Now preview the report
Try to click on ResourceName parameter without selecting ProjectName
It will Not allow you to click
Select ProjectName from your ProjectName Parameter
-Automatiically ResourceName parameter will show the Values
Select ResourcesName based on which you filter the Main Report
Click View Report
Your Report will work properly.
No comments:
Post a Comment