How to: Install PowerPivot for SharePoint and Reporting Services

Reporting Services and PowerPivot for SharePoint are two feature components of SQL Server that are installed separately but can be used together in a SharePoint 2010 environment. By deploying both feature components in the same farm, you enable the following scenarios:

  • Ability to view and manage both workbooks and reports from the same SharePoint sites and libraries, using the same SharePoint permissions and policies to control access to your business documents.

  • Ability to create Report Builder reports from PowerPivot workbooks that are stored within the same SharePoint library. Configuring Reporting Services and PowerPivot in the same farm enables Report Builder startup from PowerPivot Gallery. You can use actions in the PowerPivot Gallery library to start a Report Builder report that uses a PowerPivot workbook as its data source.

  • Use PowerPivot Gallery library to add document preview for workbooks and other documents. Preview is supported for PowerPivot workbooks and for Reporting Services reports that contain PowerPivot data.

PowerPivot for SharePoint and Reporting Services are separate features that you install and configure independently of each other. Because the deployment steps vary for each server product, you can benefit from reviewing the installation guidelines in this topic that explain how to install the software in the fewest possible steps.

This topic provides separate installation procedures for two different scenarios: installing Reporting Services on a server that is already running PowerPivot for SharePoint, and installing multiple SQL Server feature components on the same physical computer. These scenarios are mutually exclusive; choose the scenario that is the best fit for your topology.

This topic contains the following sections:

Prerequisites

Overview

Installation Scenario 1: Adding Reporting Services to an existing PowerPivot for SharePoint server

Installation Scenario 2: Installing all server products on a new server or farm

Verify PowerPivot and Reporting Services Integration

Prerequisites

You must be a farm administrator to add server features to a SharePoint farm.

You must be a local administrator to run SQL Server Setup.

The SharePoint server or farm must be Microsoft SharePoint Server 2010. Although Reporting Services can run on a broader range of SharePoint servers, PowerPivot for SharePoint requires Microsoft SharePoint Server 2010 in an edition that includes Excel Services.

Reporting Services must be version SQL Server 2008 R2 if you want to export report data feeds to PowerPivot workbooks or create reports based on PowerPivot data.

Note

Although you can use a native mode report server to export data feeds or create reports, configuring the report server for SharePoint integration allows you to store and access reports from the same sites and libraries as PowerPivot workbooks, which provides a common document access and management system for all your business documents.

Overview

To install and use Reporting Services and PowerPivot for SharePoint in the same farm, you must run SQL Server Setup twice, once to install PowerPivot for SharePoint, and again to install Reporting Services. You must run setup twice because PowerPivot for SharePoint is installed through setup roles, and these roles do not include Reporting Services.

This topic provides guidelines for two installation scenarios:

  • Adding Reporting Services to a SharePoint farm that already includes PowerPivot for SharePoint.

  • Installing all server products. This section provides an installation order that enables you to deploy the servers in the fewest possible steps.

There is no required installation order for installing either PowerPivot for SharePoint or Reporting Services. However, it is strongly recommended that you verify you have a working installation of one feature before installing the next feature.

Installation Scenario 1: Adding Reporting Services to an existing PowerPivot for SharePoint server

If you already installed PowerPivot for SharePoint, follow these steps to add Reporting Services to your farm:

  1. Step 1: Install Reporting Services. You only need one report server. It must be installed on a SharePoint application server or Web front-end that is already joined to a farm. The report server must have a database that supports SharePoint integration mode. When you create the database in the Reporting Services Configuration Manager, make sure to choose this mode. For information about how to create the database, see How to: Create a Report Server Database for SharePoint Integrated Mode (Reporting Services Configuration).

  2. Step 2: Configure the Reporting Services Add-in. You must configure the Reporting Services add-in on each Web front-end server. If the add-in was not installed by the SharePoint 2010 Preparation tool, you must install it first.

  3. Step 3: Activate the Report Server Feature in SharePoint Central Administration. Depending on how your farm is configured, you might need to activate the Report Server feature manually.

  4. Step 4: Configure Report Server Integration in SharePoint Central Administration. This step specifies integration settings that determine the connection between SharePoint and the report server.

Note

PowerPivot Gallery is a SharePoint library that can be found on SharePoint sites that support PowerPivot data access. Report Builder is automatically enabled for that library. This means that you can start Report Builder reports that are based on PowerPivot workbooks with no additional configuration required. If you want to use Report Builder in other libraries, you must first configure access. For more information, see How to: Configure Report Builder Access.

Note

To learn more about other report server configuration tasks, see Configuring Reporting Services for SharePoint 2010 Integration.

Installation Scenario 2: Installing all server products on a new server or farm

If you are starting with a clean operating system, and your objective is to configure a server that has SharePoint 2010, PowerPivot for SharePoint, and Reporting Services in the same farm or on the same server, you can use the instructions in this section as your installation roadmap.

Deploying SQL Server features in the same farm will require that you install and configure each one separately. If your goal is to deploy a broad range of server products, the following guidelines can help you achieve that goal in fewer steps.

  • Install Reporting Services, the SQL Server Database Engine, and any shared SQL Server features. For example, you might want to include management tools to add local server administration support to your deployment or Business Intelligence Development Studio to add Report Designer to your installation.

  • Install and configure SharePoint 2010. When asked to specify the database server, use the Database Engine instance you just installed.

  • Install the Reporting Services Add-in for SharePoint and configure Reporting Services for SharePoint integration.

  • Install and configure PowerPivot for SharePoint.

Each feature component has its own set of configuration tasks for integrating the feature with SharePoint. Make sure that integration is configured correctly before adding feature components to your deployment.

Install SQL Server 2008 R2 Database Engine and Reporting Services

  1. Insert the installation media or open a folder that contains the setup files for SQL Server 2008 R2.

  2. Run SQL Server Setup with administrator permissions. Right-click Setup.exe and select Run as Administrator.

  3. Click Installation on the navigation pane to the left.

  4. Click New installation or add features to an existing installation.

  5. In Setup Support Rules, click OK if no problems were detected. Otherwise, follow the instructions on the page to correct the problems and then restart setup.

  6. In Product Key, specify the Evaluation edition or enter a product key for a licensed copy of the Enterprise, Developer, or Data Center edition. Click Next.

  7. Accept the Microsoft Software License Terms of agreement, and then click Next.

  8. In Setup Support Files, click Install to install setup support files.

  9. In Setup Support Rules, click Next.

  10. In Setup Role, select SQL Server Feature Installation.

  11. In the Feature Selection page, select Database Engine Services and Reporting Services. In Shared Components, select Business Intelligence Development Studio and Management Tools. Click Next.

  12. In Installation Rules, click Next.

  13. In Instance Configuration, choose Default instance if it is available, otherwise you can specify a named instance. Do not specify PowerPivot. It will block server installation in a later step. Click Next.

  14. In Disk Space Requirements, verify that you have sufficient disk capacity to install the feature, and then click Next.

  15. In Server Configuration, specify the account names for SQL Server Agent, SQL Server Database Engine, and SQL Server Reporting Services. For more information, see Setting Up Windows Service Accounts. Click Next.

  16. In Database Engine Configuration, click Add Current User. Click Next.

  17. In Reporting Services Configuration, select Install the SharePoint integrated mode default configuration. If this option is not available, select Install, but do not configure the report server to set up the server later. Click Next.

  18. Click Next for each of the remaining pages until you get to the Ready to Install page.

  19. Click Install.

Install and Configure SharePoint 2010

Use the following SharePoint installation instructions to install and configure SharePoint:

Deploy a single server with SQL Server

Note

Because you installed the Database Engine, you can use it as the database server for your new farm.

Install the Reporting Services Add-in and Configure Reporting Services for SharePoint Integration

Use the following instructions to integrate the Reporting Services installation with SharePoint 2010.

  1. Step1: Install the Reporting Services Add-in

  2. Step 2: Activate the Report Server Feature in SharePoint Central Administration

  3. Step 3: Configure Report Server Integration in SharePoint Central Administration

Note

When you install PowerPivot for SharePoint and Reporting Services in the same farm, you gain the ability to start Report Builder from PowerPivot Gallery. If you want to use Report Builder in other libraries, you must first configure access. For more information about how to enable Report Builder for other libraries, see How to: Configure Report Builder Access.

Note

To learn more about other report server configuration tasks, see Configuring Reporting Services for SharePoint 2010 Integration.

Install and Configure PowerPivot for SharePoint

Use the following instructions to install and configure PowerPivot on a SharePoint server that is already configured and joined to a farm:

Install PowerPivot for SharePoint on an Existing SharePoint Server

Verify PowerPivot and Reporting Services Integration

The following instructions assume that you are starting Report Builder from PowerPivot Gallery.

  1. Create a PowerPivot Gallery library (For information about how to create a PowerPivot Gallery library, see How to: Create and Customize PowerPivot Gallery).

  2. Use the SharePoint upload document action to save a PowerPivot workbook to the library. Use a sample workbook to perform this step (For information, see Roadmap to Creating PowerPivot Workbooks in Excel).

  3. Click the New Report button in the upper-right corner of PowerPivot Gallery.

  4. Click Create Report Builder Report to start Report Builder, using the published workbook as the basis of your report. After Report Builder starts, use the wizard to create a simple report.

    If Report Builder does not start, click the report document placeholder that was created and saved to PowerPivot Gallery when you clicked Create Report Builder Report. In Actions, click Open in Report Builder, and then click Run.

  5. Save the report you just created back to PowerPivot Gallery.

  6. On a workstation that has Excel 2010 and PowerPivot for Excel installed, open a browser, and then open the report from your SharePoint site.

  7. Click the Export as Data Feed button in the report toolbar, and then click Open to load the feed in PowerPivot for Excel (For more information, see Generating Data Feeds from Reports).

  8. One or more data feeds that contain report data is imported as separate tables in the PowerPivot window. You can now work with the imported data as a data source for a new Excel workbook.