More and more people are migrating their Report Server environment to SharePoint. This migration will give you the ability to open your reports in SharePoint instead of just another UI. For a complete explanation on this migration check a previous blog of my at SQL Server 2005 Service Pack 2 (Integration between Reporting Services and SharePoint 2007). Before you can integrate between Reporting Services and SharePoint you have to decide if you'll setup a separate Application Tier. Almost all my customers have a similar setup as pointed out below.
SERVER 1 - Data Tier
.NET Framework 2.0
SQL Server 2005 SP2 (Database)
SERVER 2 - Application Tier
Internet Information Server 6.0
.NET Framework 2.0 & 3.0
Windows SharePoint Services 3.0
SQL Server 2005 SP2 (Reporting Services)
|Reporting Services Add-in for Microsoft SharePoint Technologies|
It's also possible to install all these products on one machine, but it isn't advisable. Reporting Services installs some Web Services and a Web manager (will be replaced by the Reporting Services Add-in for Microsoft SharePoint Technologies). After installation you can find all files at the Program FilesSQL ServerMSSQL.#Reporting ServicesReportServer directory. If you've installed al of the above products and updated it with the latest service packs and updates you can start the Reporting Services Configuration Manager. This tool can be started for the Start menu at All Programs - SQL Server 2005 - Configuration Tools - Reporting Services Configuration. Depending on the choice of installation you will see a similar screen like this.
If some of the above sections displays a red cross mark fulfill these steps.
- The Report Server virtual Directory should point to the web site where you installed and configured SharePoint.
- The Report Manager Virtual Directory will become obsolete, so it isn't necessary to implement this step.
- The Windows Service Identity is used under which the Report Server Windows service runs. The Report Server Windows service performs initialization, reversible encryption, database maintenance tasks, and all scheduling and delivery. The service runs in the background. It performs end-to-end processing for reports that run on a schedule (specifically, it creates report snapshots and runs subscription reports).
Because it performs all encryption operations, the Report Server Windows service must be running whenever you specify or use encrypted values. Specifying stored credentials, running a report that uses stored credentials, and publishing a report to a report server (data source information is encrypted) are all operations that require the Report Server Windows service.
- The Web Service Identity creates or uses a application pool under which the identity runs in IIS.
Now all steps are completed and Reporting Services is initialized you can start completing the last step to complete SharePoint Integration. Click on the Database Setup section and Change the Server Mode from Native to SharePoint Integration. After hitting the change button fill out the following screen and make sure the Create the report server database in SharePoint Integrated mode is selected.
Type in new Database Name (e.g ReportServerWSS). Note. Don't start to create a database which starts with WSS because in some cases the connection will fail. After creating the database the SharePoint integration is completed.
Although it isn't hard to install and configure the add-in, it will give you a lot of steps to fulfill. First of all you have to make a decision about your server topology. Secondly you must setup Reporting Services to use you're data tier (remote or local). Third upgrade your database, so all content will be added to SharePoint and the rendering and caching stays in SSRS. In my next blog I'll explain how Activating SharePoint for Reporting Services works.