Cascading Parameters in SSRS.

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.

This is the final output of the Cascaded parameters.

I hope this post could help you to dig more on the cascaded parameters on SSRS.

Happy Reporting.


KarthikShanth!!!.


View Karthikeyan Shanthakumar's profile

Installing and configuring SSRS 2005

Folks,
I have many times had the experience of trouble shooting the installation and configuring the SSRS 2005 and it made me to create a standard document to enrich this process quicker and easier.

I will be happy if you guys contact me for the document via a comment or karthik.shanth@live.com.

Content of Installation document:
--> Step by step procedure to show Installation of SSRS 2005

Content of the configuration document:
-->
1) IIS Configuration for Reports and Reportserver
2) Steps to upload the RDL files in report server
3) Create a New folder named “Reports”
4) Click on “New Data Source” menu
5) Create a new sample Data source named “DS” with the SQL server connection string mentioned in the Connection string text box to connect to the database server
6) The following screen will appear once data source has been created
7) Uploading the RDL files
8) Configuring the data sources for the Report files

Happy Reporting!!
KarthikShanth.
View Karthikeyan Shanthakumar's profile

quick bite on ssrs rendering format control

Guys, you can very well control the rendering formats of the report to your clients.

Yes. You can decide what is the export data format of your client.
Go to your reportserver installed path of your server

"C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\rsreportserver.config"

Open it and search for this keys



Remove the rendering formats key which you want to hide.
You can play and control the rendering limits whenever you give the reports to your clients.

Happy Reporting!!
KarthikShanth.
View Karthikeyan Shanthakumar's profile

Introduction

I would like to write some tit bits of Microsoft SSRS in this blog. So expect something and i would welcome your comments as well which enhances me a lot :)
View Karthikeyan Shanthakumar's profile