Shanth's techwitz: 2010

Guys,

Most of the time we would have come across getting the cascaded drop downs in most of the web pages( for example, creation of a login account in any website will allow you to choose country followed by state followed by city). I wish to give a post on how to do that in SSRS 2005.

Note: I have used AdventureWorks database tables to build this sample.

Create 4 datasets as like below

dscountry - select * from HumanResources.CountryMaster

dsState - select * from HumanResources.StateMaster where CountryID = @P_CountryID

dsCity - select * from humanresources.citymaster where stateid = @P_StateID

dsResultSetFetch -
select required columns from
HumanResources.Employee inner join
HumanResources.EmployeeCity on
HumanResources.Employee.employeeid = HumanResources.EmployeeCity.employeeid
inner join
HumanResources.CityMaster on HumanResources.EmployeeCity.cityid=HumanResources.CityMaster.cityid
inner join
HumanResources.StateMaster on HumanResources.CityMaster.stateid = HumanResources.StateMaster.stateid
inner join
HumanResources.CountryMaster on HumanResources.StateMaster.countryid = HumanResources.CountryMaster.countryid
where HumanResources.CityMaster.cityid = @P_CityID

Now go to the layout tab to create a respective table so as we can drag and drop the necessary fields inside it.

Click on the Report Menu->ReportParameters which will open up as like in the picture attached. Assign the respective datasets as provided for state and city following country

Now try to preview your report. Click on country, it will automatically populate the state and the selection of state will automatically populate the city and finally click view report so as to get the report for the city data.