TEST

Company	Contact	Country
www	www	www
Centro comercial Moctezuma	Francisco Chang	Mexico
Ernst Handel	Roland Mendel	Austria
Island Trading	Helen Bennett	UK
Laughing Bacchus Winecellars	Yoshi Tannamuri	Canada
Magazzini Alimentari Riuniti	Giovanni Rovelli	Italy

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.

Few important power shell commands

Before trying any of the following commands in powershell make sure that service SharePoint 2010 Administrationis started. if it is not started then start it.

Go to Start -> All Programmers -> Microsoft SharePoint 2010 Products -> SharePoint 2010 Management Shell


To install a solution we use the Add-SPSolution command. If you are using a Sandboxed solution you would use Add-SPUserSolution instead. But for Sandbox solution I would prefer adding the wsp file using GUI in Central Admin.

PS C:\Users\user1> Add-SPSolution C:\mySolution.wsp

Now to deploy the solution to your Sharepoint site we shall use Install-SPSolution


PS C:\Users\user1> Install-SPSolution -Identity mySolution.wsp -WebApplication https://mySiteURL –GACDeployment

To upgrade solution use Update-SPSolution

PS C:\Users\user1>Update-SPSolution –Identity mySolution.wsp –LiteralPath C:\mySolution.wsp –GACDeployment

To retract a solution we use the Uninstall-SPSolution

PS C:\Users\user1>Uninstall-SPSolution –Identity mySolution.wsp –WebApplication https://mySiteURL

And to remove it from solution store use Remove-SPSolution

PS C:\Users\user1>Remove-SPSolution –Identity mySolution.wsp

Few little troubleshooting.
1. You may sometime see the following error when trying Install-SPSolution

Install-SPSolution : This solution contains no resources scoped for a Web appli
cation and cannot be deployed to a particular Web application.

Then try this instead
PS C:\Users\user1> Install-SPSolution -Identity mySolution.wsp –GACDeployment

2. If any of your deploy command fails you will not be able to deploy it again. If you try again you will see the following error

A deployment or retraction is already under way for the so
lution "yourSolution", and only one deployment or retraction at a time is supported.....

To handle this issue open central admin. Go to Monitoring->Review Timer Job Definitions
Then find the job which has you solution name in title. Delete it.

3. You may see the following error if SharePoint 2010 Administration service is not started.

Install-SPSolution: Admin SVC must be running in order to create deployment timer job

So all you need to do is to go to the Services section in Control Panel\System and Security\Administrative Tools and look for the service called SharePoint 2010 Administration, it was on manual start up, changed it to automatic and started it and gotten rid of this error.