Chapter 3. Reporting Services Setup and Configuration

There are two options for installing Reporting Services: native mode and SharePoint Integrated mode. Native mode includes the Report Manager web application that can be used to manage all aspects of managing, deploying, and executing reports. Reporting Services can be installed in native mode completely independent of SharePoint. With SharePoint Integrated mode, the Report Manager is disabled and all report management and execution functions are provided in SharePoint. The considerations and steps for installing and configuring Reporting Services in SharePoint Integrated mode are the topic of this chapter.

Reporting Services uses a SQL Server database to store reports, subscriptions, and so on. This database is named ReportServer by default. The contents of the database are different depending on how it is created; i.e. whether it is created for Reporting Services running in native mode or SharePoint Integrated mode. The creation of the appropriate database can be handled by running the Reporting Services Configuration Manager or during the installation of Reporting Services and the database engine.

After installing or configuring Reporting Services to run in SharePoint Integrated mode, the Reporting Services Add-In for SharePoint must be installed and configured. The Add-In is a SharePoint feature that is activated at the site collection level and provides the infrastructure for storing, running and managing reports within SharePoint.

There are a number of advantages to installing Reporting Services in SharePoint Integrated mode versus native mode. First and foremost is the fact that the integration of Reporting Services with SharePoint is seamless. Reports are stored in SharePoint document libraries, allowing users to interact with them from the familiar SharePoint web interface. Report security is handled in SharePoint rather than in the Report Manager, which implements its own security scheme.

Installing Reporting Services

Reporting Services is one of several features that can be installed from SQL Server setup along with the relational database engine. There are two steps during the setup that are important to highlight to allow the setup to do the majority of the work in getting Reporting Services running in SharePoint Integrated mode. First, as shown in Figure 3.1, choose the SQL Server Feature Installation option. The SQL Server PowerPivot for SharePoint option is not relevant to this discussion. The All Features with Defaults option is normally a good choice, except that it installs and configures Reporting Services in native mode, which is of no benefit.

Figure 3.1. Configure service application associations.

The second step to highlight is Reporting Services configuration, as shown in Figure 3.2.

Figure 3.2. Reporting Services configuration.

Choose Install the SharePoint Integrated Mode Default Configuration. This creates the Reporting Services database in SharePoint Integrated mode. There are some scenarios where the integrated mode default configuration option may not be available; for example you have to install both the database engine and Reporting Services. If the option is not available then the alternative is to choose Install, but do not configure the Report Server and then use the Reporting Services Configuration Manager to create a new Reporting Services database in SharePoint Integrated mode.

Installing the Reporting Services Add-In for SharePoint

In addition to getting Reporting Services set up and configured for SharePoint Integrated mode, the Reporting Services Add-In for SharePoint needs to be installed and configured. The add-in is a SharePoint Feature that provides the infrastructure to seamlessly integrate Reporting Services within SharePoint. Beginning with SharePoint 2010, the Reporting Services Add-In for SharePoint is included in the software prerequisites for SharePoint, as shown in Figure 3.3.

Figure 3.3. Microsoft SharePoint 2010 Products Preparation tool.

You can install any of the necessary software prerequisites by selecting Install Software Prerequisites from the SharePoint 2010 setup. The Reporting Services Add-In for SharePoint is also available as a separate download from the Microsoft website. (Just search for SQL Server 2008 R2 Feature Pack). Note that the version of the Reporting Services Add-In for SharePoint is based on both the version of SQL Server and the version of SharePoint. There are versions of the add-in for SQL Server 2005, SQL Server 2008, and SQL Server 2008 R2, and versions for SharePoint 2007 and SharePoint 2010.

After installing the Reporting Services Add-In for SharePoint, you see the Report Server Integration Feature in the Site Collection Features gallery.

Configuring the Reporting Services Add-In for SharePoint Integration

After getting Reporting Services set up for SharePoint Integration and installing the Reporting Services Add-In for SharePoint, the next step is to configure the Reporting Services SharePoint Integration in SharePoint Central Administration. To do so follow these steps:

1. Launch SharePoint Central Administration from the Start menu. (It should be pinned to the Start menu; if not, select it from the All Programs, Microsoft SharePoint 2010 program group.) The SharePoint Central Administration home page is displayed, as shown in Figure 3.4.

   Figure 3.4. SharePoint Central Administration.

   

2. Click General Application Settings to display that page, as shown in Figure 3.5.

   Figure 3.5. SharePoint Central Administration General Application Settings.

   

3. Click Reporting Services Integration and fill in the form, as shown in Figure 3.6

   Figure 3.6. Reporting Services Integration in SharePoint Central Administration.

   

The following are the main points about the entries on the form:

• You can find the Report Server Web Service URL by opening the Reporting Services Configuration Manager and navigating to the Web Service URL page, as shown in Figure 3.7.

Figure 3.7. Reporting Services Configuration Web Service URL page.

• Choose Windows Authentication for Authentication Mode if Kerberos is configured; otherwise, choose Trusted Account.

• Enter a domain account for the credentials; this account must be in the Administrators group on the server where Reporting Services is installed.

• Specify to activate the Reporting Services feature for all existing site collections or specific ones as appropriate. As a general rule, it is a good idea to choose all existing site collections.

After you enter the required information and click OK, the Reporting Services Integration Summary is displayed, as shown in Figure 3.8.

Figure 3.8. Reporting Services Integration Summary.

At this point, the configuration of Reporting Services in SharePoint Integrated mode is complete.

Adding Content Types to a Document Library

A Content Type in SharePoint is a collection of settings that describe a piece of content such as a document, task, contact, report, and so on. If you set up Reporting Services in SharePoint Integrated mode, you can then store reports, report models, and shared data sources in SharePoint document libraries. For a given document library, the appropriate content types for these artifacts must be added and the New Document menu customized to specify the types of documents that can be created when the New Document button is clicked. Follow these steps to add the appropriate Reporting Services content types to a document library and customize the New Document menu:

1. Navigate to (or create) a document library and click the Library tab, as shown in Figure 3.9. (The Library tab is underneath Library Tools.)

   Figure 3.9. Document library.

   

2. Click Library Settings on the ribbon to display the Library Settings page, as shown in Figure 3.10.

   Figure 3.10. Document library settings page.

   

3. Click Advanced Settings to display the Advanced Settings page, as shown in Figure 3.11. (Only the relevant portion of the page is shown.)

   Figure 3.11. Document library advanced settings page.

   

4. Click Yes to allow management of content types (that is, to allow multiple content types in this document library).
5. Click OK to return to the Library Settings page. Navigate to the Content Types, as shown in Figure 3.12.

   Figure 3.12. Document library content types.

   

6. Click Add from Existing Content Types to display the Select Site Content Types from, as shown in Figure 3.13.

   Figure 3.13. Add content types.

   

7. Select the Report Builder Model, Report Builder Report, and Report Data Source, as shown in Figure 3.13, and then click Add.
8. Click OK to return to the library settings page.
9. Navigate again to the Content Types and click Change New Button Order and Default Content Type, as shown in Figure 3.14.

   Figure 3.14. The new button order and default content type.

   

10. The Content Type Order form is displayed, as shown in Figure 3.15. Edit the Content Type Order, as shown in Figure 3.15.

    Figure 3.15. Content type order.

    

11. Click OK to return to the library settings page, and then navigate back to the home page for the document library (refer to Figure 3.9, earlier in this chapter).
12. Click the Documents tab underneath Library Tools.
13. Click New Document to display the drop-down menu, as shown in Figure 3.16.

    Figure 3.16. New Document drop-down menu.

    

The drop-down menu shows Report Builder Report, Report Data Source, and Report Builder Model choices. Selecting Report Builder Report launches Report Builder, a user-friendly tool used to design a report. This completes the setup and configuration of Reporting Services in SharePoint Integrated mode. The steps required to create a data source and a report are covered in the End-to-End Solutions chapter later in the book.

Summary

The setup and configuration of Reporting Services in SharePoint Integrated mode is a somewhat tedious process, but the advantages are many, and the integration is seamless. Business users get the benefit of being able to do everything in SharePoint without having to resort to the Report Manager web application. Report security leverages SharePoint, which also reduces complexity.

Best Practices

The following are best practices from this chapter:

• Choose the SQL Server Feature Installation option on the Setup Role step in SQL Server setup. This creates the Reporting Services database in SharePoint Integrated mode and makes it ready for the SharePoint configuration.

• Run Install Software Prerequisites for SharePoint to install the Reporting Services add-in for SharePoint (rather than downloading it).

• Add the Report Builder Report, Report Data Source, and Report Builder Model content types to any document library that will store reports. Customize the New Document menu to show these content types as choices.

• Choose Windows Authentication for Authentication Mode if Kerberos is configured, otherwise choose Trusted Account.

• Use a domain account that is a member of the Administrators group on the server where Reporting Services is installed.

• Activate the Reporting Services Feature for all existing site collections. If you choose not to do this, you can selectively activate the feature in the appropriate site collections.