Setup SSRS in SharePoint Integrated Mode

                Setup SSRS in SharePoint Integrated Mode

This post is about configuring SQL Server Reporting Services in SharePoint Integrated mode the quick and clean way with no fuzz in 40 steps.

I'm not discussing the default values, I will only talk about the values which need to be changed or selected in order to get SSRS set up in the least amount of steps.

 

The scenario:

• SharePoint 2010 Enterprise Edition + named instance SQL Server 2008 Express Edition which is deployed during the SharePoint setup. Name of the SQL Server instance: SHAREPOINT.
• Separate default SQL Server 2008 R2 instance (Enterprise Edition).
• SSRS currently installed in native mode on the SQL Server 2008 R2 default instance.
• AdventureWorksDW2008R2 hosted on the SQL Server 2008 R2 default instance.

The procedure contains 5 parts:

A. SSRS Configuration {SQL Server}

B. Web application {SharePoint}

C. Site collection {SharePoint}

D. SSRS integration {SharePoint}

E. Content types {SharePoint}

 

 

Let's start!

 

 

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:

The Microsoft SharePoint Foundation Web application has been created. 

If this is the first time that you have used this application pool with a SharePoint Web application, you must wait until the Internet Information Services (IIS) Web site has been created on all servers. By default, no new SharePoint site collections are created with the Web application. If you have just created a Forms Based Authentication (FBA) Web application, then before creating a new site collection, you will need to perform some additional configuration steps. 

Learn about how to configure a Web application for FBA. 

Once you are finished, to create a new site collection, go to the Create Site Collection page. 

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. I will choose Windows Authentication. Entering .\<username> will do.
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. Browse for a Report provided by the AdventureWorksDW200R2 samples from CodePlex.
14. 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).
 

 

 

 

You have now finished the complete setup. Click the report you have uploaded to view the result.