As you are well aware, SharePoint and SQL work very closely together and with each iteration of SharePoint and SQL, that integration gets tighter and tighter. One of the main attractions to integrating SharePoint with SQL Server Reporting Services, commonly referred to as SSRS is to allow users to create, modify and publish reports from within SharePoint itself. Reporting Services (SSRS) provides some predefined content types that are used to manage various files, including the shared Report data source (.rsds) files, the Report Builder model (.smdl), and the Report Builder report definition (.rdl) files.
In my last article, I provided you with a step by step guid on configuring SQL PowerPivot for SharePoint 210. In this article I will be providing you with a step by step guide in installing and configuring Reporting Services in SharePoint integrated mode.
Installing SQL Reporting Services for SharePoint 2010
We begin by installing SQL Reporting Services for SharePoint on our Application Server or on a dedicated Reporting Services Server with SharePoint running.
Launch the SQL Server Installation Center and Select Installation and then click on New Installation or add features to an existing installation.
Click Install to install the Setup Support Files.
The setup support rules are then run. At this stage you should rectify any errors if present.
In the Installation Type page, select New installation or add shared features
Enter the product key at the Product Key screen and click Next.
Accept the License Terms and then click Next.
Select SQL Server Feature Installation
Select Reporting Services and Management Tools only.
Setup will run the Installation Rules. Address any failures and then click Next
Type a Named Instance
Check your Disk Space Requirements
In the Server Configuration page, enter a domain user account.
In the Reporting Services Configuration Page, the only option was to Install, but do not configure the report server.
The Installation Configuration Rules are run. Address any issues and click Next.
Click Install to proceed with the Installation.
Once the installation has completed, Navigate to Start > All Programs > Microsoft SQL Server 2008 R2 > Configuration Tools and launch the Reporting Services Configuration Manager.
Select the Report Server Instance and click Connect
On the left Task bar, select Database and click on Change Database
The Report Server Database Configuration Wizard is displayed and select Create a new report server database.
Enter a valid database name where you want to install the Report Server database. I will be hosting this on a remote SQL server which also houses my SharePoint Configuration and Content Databases.
Enter the Database Name and Select your Report Server Mode as SharePoint Integrated Mode.
Under Authentication Type, select Windows Credentials and enter a domain user account.
Review the Summary and Click Next
Click Finish once the configuration Wizard completes.
Navigate to Web Service URL and change any values. I have changed my TCP port to 1111. Click Apply.
You should notice that the new URL was successfully reserved.
Copy the Report Server Web Service URL to clipboard. This will be needed a little later.
Integrate Reporting Services with SharePoint
Launch SharePoint Central Administration and navigate to General Application Settings > Reporting Services > Reporting Services Integration
Enter the details below
After clicking OK you will return to the Reporting Services Integration Summary Page with hopefully 3 green ticks with successful status’.
We now need to confirm that the that the Report Server Name and server instance is correctly listed in SharePoint. We do so by navigating to SharePoint Central Administration > General Application Settings > Reporting Services > Add a Report Server to the Integration.
To verify that we have correctly configured and integrated Reporting Services with SharePoint 2010, we navigate to our SharePoint Site, Site Settings > Site Collection Features and Activate the Reporting Server Integration Feature.
After the activating the Report Server Integration Feature, we can navigate to our SharePoint Site and add the SQL Server Reporting Services Report Web Part, located under SQL Server Reporting.
You can now upload your reports which will render directly in SharePoint.