Sharepoint 2010 SSRS Implementation

The procedure contains 8 parts:

1.      SQL2008 R2 SSRS for 2010 Add-in is already installed. (As a pre-requisite), if not , manually install the Add-in to the Application Server and manually activate the features. (Backup the web.config file for your Central Administration before installation).

2.      Install the Add-in to all the WEB FRONT ENDS. Manually deploy installation without activating the features.

3.      Join the Reporting Server to the SharePoint Farm and deploy the Add-in as explained on step2.

 

A.    SSRS Configuration {SQL Server}

B.     Web application {SharePoint}

C.     Site collection {SharePoint}

D.    SSRS integration {SharePoint}

E.     Content types {SharePoint}

 

A. SSRS Configuration {SQL Server}

1. Log on to your SharePoint 2010 box.
2. Fire up the SQL Server Reporting Services Configuration Manager.
3. Connect to the Report Server instance. In my case this is the default SQL Server 2008 R2 instance (MSSQLSERVER)
4. The first thing you will notice is the Report Server Status. Check the Report Server Mode. It should say Native (we start from native mode in this scenario).
5. Click Database in the left pane. Click Change Database. Select create a new report server database.
   Enter the proper credentials.
6. Enter a database name and select SharePoint Integrated mode.
7. Enter the credentials and do not forget the <domain name>\<username> (.\<username> will do as well).
8. Choose Report Manager URL in the left pane and click the apply button. This will configure the Report Manager virtual directory.
9. Backup the encryption keys.

B. Web application {SharePoint}

1. Open the SharePoint Central Administration website.
2. Choose Application Management > Manage web applications.
3. Click the New button in the ribbon. This will bring up the Create new web application popup.
4. Keep the default values to keep things simple except for:
1. Choose a name for the new IIS Website. I will choose SSRSDemo in this example.
2. Choose a name for the application pool. I will again choose SSRSDemo.
3. Make sure you connect to the right database instance. In my example I will connect to the named SQL Server 2008 Express Edition instance named SHAREPOINT.
4. Choose a database name or keep the default value with the GUID suffix. I will call the database WSS_Content_SSRSDemo.
5. Hold your horses before you hit the OK button when this information pops up:

5. Click Create Site Collection page to move on to C. Site collection {SharePoint}

 

C. Site collection {SharePoint}

1. Enter a title for the site collection. SSRSDemo for example.
2. Choose a template. I will choose Business Intelligence Center because I want to store my SSRS reports in a BI related environment.
3. Enter a username for Primary Collection Administrator. Make sure you enter the full domain name. e.g.: <domain name>\<username> (.\username won't work here).

D. SSRS integration {SharePoint}

 

1. Go to the SharePoint Central Administration website > General Application Settings > Reporting Services > Reporting Services Integration.
2. Enter the Report Server Web Service URL which you can find the SQL Server Reporting Services Configuration Manager.
3. Choose the Authentication Mode. Windows Authentication. Entering.\<username> will do.  ( we used TRUSTED ACCOUNT)
4. Go to the SharePoint Central Administration website > General Application Settings > Reporting Services > Add a Report Server to the Integration.
5. The server name should already be provided and enter the name of the SQL Server instance which hosts the report server database.
6. Enter the credentials.

 

E. Upload a report {SharePoint}

1. Open your new top level web site we created in C. Site collection {SharePoint}
2. In case you forgot the URL of the web site, go to SharePoint Central Administration > Web Application and look for the URL.
3. Go to All site Content > Documents.
4. In the ribbon, go to Library Tools > Library.
5. Click Library Settings.
6. Click Advanced Settings.
7. In Content Types, check Yes for Allow Management of content types and click OK.
8. In the columns section, click Add from existing site columns.
9. In the Select site columns from drop-down box, select Report Server Content Types.
10. Select all available site content types and click the Add button followed by OK.
11. Go back to All site content > Documents.
12. Click the Add Document link.
13. Enter a title for the report.

There is also the option to create a report yourself if you don't have the reports from the sample reports. It only takes a few seconds to create a report using the Report Builder (see screenshot below).

 

 

Click the report you have uploaded to view the result.

 

Report Builder program will download automatically if not installed and it will integrate with the browser and SharePoint.