Deploying SSRS Reports to Sharepoint

11/14/2008 2:00:00 AM

I recently went through the experience of deploying a SQL Server Reporting Services (SSRS) project to Sharepoint.  My goal was to get the reports in Sharepoint, but then use them with a ReportViewer control in an ASP.Net page.  This was a new concept for me as I usually deploy the reports to a native SSRS installation rather than one in Sharepoint integrated mode. The correct configurations were not really well documented elsewhere, so here they are, at least what worked for me.  This applies to SSRS 2005 and Sharepoint Services 3.0.  It should equally apply to Microsoft Office Sharepoint Server (MOSS) 2007.  SSRS 2008 may or may not be the same.

The steps here assume that you already have SSRS installed correctly in Sharepoint integrated mode and everything is ready to go.  How to do that is covered pretty well, so I don't wan to rehash that.

Configurations for Deployment

You need to decide were to place your reports in the Sharepoint organization.  In this case, we made a site within the base Sharepoint portal.  Then we created two folders, one for the shared Data Sources and one for the reports themselves.

Once your Sharepoint and SSRS installations are set up correctly, you need to deploy your reports project to the system.  To do that, you can set up a configuration in SQL Server Business Intelligence Design Studio aka Visual Studio.  I set up the Production configuration for my project to point to the Sharepoint server and kept my Debug configuration set to my local instance.

Steps

  1. Open the project properties window.
  2. Change the configuration combo box to Production.
  3. In the properties page, you need to set the three locations that are needed to deploy the project.
    • TargetDataSourceFolder: http://bakerwss/sites/LaborVarianceReporting/Shared Documents/Data Sources
      TargetReportsFolder: http://bakerwss/sites/LaborVarianceReporting/LVR%20Reports
      TargetServerURL: http://bakerwss/sites/LaborVarianceReporting/
  4. Notice that the Server URL is similar to what you are used to seeing with a stand alone installation.  You just set it to the path of the site containing the reports.  The difference is in the other two locations.  In a normal installation, they would set to relative folders within the SSRS Report Manager site.  But in Sharepoint integrated mode, you use the whole path.

When you deploy the project to production, then the reports and any shared Data Sources will be inserted into Sharepoint and you will be able to see them on the site.

ASP.Net ReportViewer Configurations

In many cases, getting the reports on to Sharepoint would be the end of it.  But I needed to be able to use them in an ASP.Net web site with a ReportViewer control.  So I had to figure out the correct paths to set for the ReportViewer control.  These were not obvious.

When you use a ReportViewer in Remote mode, you set a property for the server and the report paths.  With a stand alone SSRS installation, you set the ReportServerURL property to something that looks like http://servername/Reportserver.  With Sharepoint it is similar.  Although not obvious, when you install the SSRS integration into Sharepoint, there is a Reportserver directory.  So the ReportServerURL property would be the same in this case, i.e. http://bakerwss/ReportServer.  Notice that this points to the root of the Sharepoint portal, not the site that we made.

The ReportPath property in a stand alone installation can be a relative path to the report e.g. BELVR/Budgetedhours, for the Budgeted Hours report.  In Sharepoint mode you need specify a complete path, i.e. http://bakerwss/sites/LaborVarianceReporting/LVR%20Reports/BudgetedHours.rdl.    Notice that this path includes the .rdl file extension.  That has to be included for the path to work.

Hopefully this will help you get your SSRS reports deployed to Sharepoint a little easier.