Rporting Service and Sharepoint...simple solution to a colossal problem

Working with Sharepoint and Reporting Service is not something someone is going to enjoy. I can make a list of troubles you will face but that may scare you enough to make different plans for your career(as it did for me), so I am not being a wailer here. Rather I would talk about a common problem, discuss the weird things came into my mind and guide you to a elegant solution that caused me almost 2 days.


At the beginning of the day the requirement is very simple. You have to make a SSRS report which takes 5 parameters and load the report. It's so easy man, you can do it. So you make a report and upload it to reporting service server. And in Sharepoint you do the following

1. Add a Report Viewer web part to your page. By default this web part is not available, so to use this web part copy RSWebParts.cab from C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Reporting Services\SharePoint\ RSWebParts.cab

Then run the following command
STSADM.EXE -o addwppack -filename "RSWebParts.cab" -globalinstall

2. Change the report viewer web part's report server and report path value in edit mode.

So you are done with adding the report to Sharepoint. The client will change the parameter values form the parameter panel of the report and view the report.

Now as the noon comes the requirements get changed. The client does not like the ugly parameter panel anymore and they want some better looking controls for selecting parameter values. So what you have to do is make a custom parameter panel. What I could come up with is a custom Filter web part. My plan was

1. Make a filter web part which will have some server controls(one control per parameter).
2. Connect the report viewer web part to the filter web part using connection. The report viewer web part can take parameter values from connections and get refreshed with the new parameter values.

As I have not mentioned all detail about how to do this, by the time you have done this you will be thanking Google and may be cursing Microsoft!

Your client is still not happy(as they are never). To make your life hell now they want different reports to be displayed in the same place depending on a drop down or button(you are dead!). Moreover they again wants to change the parameter panel. For example they have added a Javascript tree and some other html controls from which you have to take the parameter values.

The first problem with the new requirements is multiple reports in the same report viewer control. Report viewer controls can take both report path and report parameter values using connections. But they can not take them both at the same time. As the custom Filter web part is providing the parameter values the report viewer control can not take report pathusing connection.

The second problem is your HTML controls.How the filter web part is going to provide values taking form a non server control?

After couple of frustrating hours(smoke,tea,coffee and career change plans...) I came up with the following ideas.

1. The parameter panel will be now in HTML and JS. There will be a "Show Report" HTML button.
2. There will be an Iframe in the page.
3. There will be an aspx page stored in sharpeoint which will have a report viewer control(read again, it is not report viewer web part!). The page in its page load will parse it's Query string and find the report name and parameters required. It will then set the report viewer's report path and parameters.
4. When the "Show Report" button is clicked it will set the iframe's source too the aspx page and pass the parameters as Query string.

So this way if you are lucky enough you can make it work. But there is a very good chance of getting stuck at some point. Luckily while struggling with these issues I found something totally unexpected.

The reporting service server manager URL actuallly has an aspx page. And the well kept secret is this page takes query string parameters and load the reports accordingly. So you only pass the report name and parameters as querystring and set the url as source of your iframe. And voila! your report is shown out of no where :) So finally I came up with the following javascript code.