Cascading of Parameters in SSRS

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

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

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.