• Article

Create a Reporting Services Report with PowerPivot Data

You can create a Reporting Services report that is based on a PowerPivot workbook that has been published to SharePoint. Reporting Services offers rich data visualization through charts and maps, additional rendering formats such as PDF or Word, and comprehensive subscription and delivery options. Using Reporting Services to visualize, render, or distribute PowerPivot data gives you powerful alternatives for making business intelligence data available to other people in your organization.

Important

This topic explains how to connect to PowerPivot data from reporting tools. After you set up a connection, you should refer to Reporting Services documentation for instructions on how to design reports that are based on multidimensional data.

This topic contains the following sections:

Prerequisites

Choose an approach

Connect to a PowerPivot data source in Report Builder

Connect to a PowerPivot data source in Report Designer

Publish or save a report to PowerPivot Gallery

Prerequisites

Your PowerPivot workbook must be published to a SharePoint site that is enabled with PowerPivot server software. PowerPivot server software is required to load the data and make it available to Report Builder or Report Designer. For more information, see PowerPivot Overview for IT Administrators.

Your report server must be SQL Server 2008 R2 Reporting Services.

Note

The report server can be operating in native mode or in SharePoint integrated mode. If the report server is configured for SharePoint integration in a SharePoint 2010 farm that also has PowerPivot for SharePoint, you can start reports from PowerPivot Gallery. Starting reports from PowerPivot Gallery simplifies development because the data source connection is created for you.

To support connections from Report Builder, you must have SQL Server 2008 R2 ADOMD.NET on your workstation computer. This client library is installed with PowerPivot for Excel, but if you are using a computer that does not have this application, you must download and install ADOMD.NET from the SQL Server 2008 Feature Pack page on the Microsoft web site.

To support connections from Report Designer, you must have SQL Server 2008 R2 OLE DB Provider for Analysis Services on your workstation computer. You can also download and install it from the SQL Server 2008 Feature Pack page if you are using Report Designer on a computer that does not have this version of the provider.

Choose an approach

You can use either Report Builder or Report Designer that you install from SQL Server 2008 R2 installation media.

Application

Approach

Link

Report Builder

Start Report Builder from PowerPivot Gallery, another library, or Report Builder that you open from a SQL Server 2008 R2 report server.

How do I…

Report Designer

Start the Business Intelligence Development Studio, create a report project, and connect to a published PowerPivot workbook on a SharePoint site.

How do I…

Connect to a PowerPivot data source in Report Builder

To create a report, you use Report Builder to start a new report based on a PowerPivot workbook that you published to PowerPivot Gallery or another library. In order for data to be available to the report, your SharePoint environment must have at least one PowerPivot server to load and calculate the data.

The following steps assume that Reporting Services is installed and configured for SharePoint integration in the same SharePoint environment as PowerPivot Gallery.

  1. In PowerPivot Gallery, scroll to the PowerPivot workbook that you want to use as a data source, and then click the down arrow underneath the Created linked document button: New document button

  2. Click Create Report Builder Report.

    Note

    If you do not see this option, Reporting Services is either not enabled for this site or it is not the SQL Server 2008 R2 version.

On first use, Report Builder will be downloaded from the report server. After it is downloaded, a new empty report will be created using the PowerPivot workbook as a data source and saved to PowerPivot Gallery. A data source connection to the workbook is created for you.

Although the data source connection is defined automatically, you must specify the datasets and the report layout yourself. As a next step, create the datasets to specify which tables and columns you want to work with, and then use the Insert tab to assemble your report layout.

If Report Builder fails to launch in a separate browser window, it might be blocked by browser security settings. To workaround this issue, you can manually open the report in Report Builder:

  1. Switch PowerPivot Gallery to All Documents view. To do this, click Library in the server ribbon and change the Current View to All Documents.

  2. Select the empty report that contains the PowerPivot workbook data source connection.

  3. Click the down arrow to the right, and then select Edit in Report Builder.

To start Report Builder from any library

The following steps assume that a SharePoint site administrator enabled report server content types for the library.

  1. In the library, in Library Tools, click the down arrow next to New Document.

  2. Click Report Builder Report. You must choose a report. You cannot create a report model or a report data source based on PowerPivot data.

  3. In Getting Started, in New Report, select the type of report you want to create.

  4. In Choose a dataset, select Create a dataset. Click Next.

  5. In Choose a connection to a data source, click New.

  6. In Select connection type, choose Microsoft SQL Server Analysis Services.

  7. In Data Source Properties, click Build to specify connection information.

  8. In Server name, enter a URL to the PowerPivot workbook (for example, http://Contoso-srv/Shared%20Documents/ContosoSales.xlsx).

  9. Leave User name and Password blank. The connection will be made using the Windows account that you used to log on.

  10. Click Test Connection to verify that you have access to the file, and then click OK.

  11. In Data Source Properties, click Credentials.

  12. Select Use current Windows user, and then click OK.

    Important

    Be sure to choose this option. If you skip this step, you will get an HTTP 401 error later when you try to run the report.

  13. Click Next to continue to the Design a query page.

  14. In Design a query, drag attributes and measures to the workspace to build your query.

  15. Finish creating your report using the remaining pages in the wizard.

  16. Click Run to view the report.

  17. Save the report to SharePoint, choosing a server and site that support PowerPivot data access.

Note

Choosing PowerPivot Gallery as a location is useful if the PowerPivot workbook that you used as a data source is already in the same gallery. If the workbook is in a different location, preview images in PowerPivot Gallery will be turned off for the report. For more information, see Publish or save a report to PowerPivot Gallery in this topic.

Connect to a PowerPivot data source in Report Designer

Report Designer is hosted in Business Intelligence Development Studio, an authoring tool that is installed with SQL Server. The following steps assume that you installed the Analysis Services OLE DB provider that supports connections to PowerPivot data. For more information, see Prerequisites in this topic.

  1. In All Programs, in SQL Server 2008 R2, click SQL Server Business Intelligence Development Studio.

  2. In the File menu, click New, and select Project. Choose Report Server Project.

  3. In Solution Explorer, right-click Reports and select Add New Report.

  4. In Select the Data Source, in Type, select Microsoft SQL Server Analysis Services.

  5. Click Edit.

  6. In Server Name, type the URL to the PowerPivot workbook (for example, http://contoso-srv/shared%20documents/ContosoSales.xlsx).

    Note

    Do not click Test Connection. It will not work until you complete the next two steps.

  7. Click Credentials.

  8. Click Use Windows Authentication (Integrated Security), and then click OK.

  9. Click Next.

  10. In Design the Query, click Query Builder to select the measures and dimensions you want to use in your report.

If you plan to publish your report to PowerPivot Gallery to use its preview features, you might need to change your report or the location of its PowerPivot workbook data source in order for preview images to appear.

For Reporting Services reports that you publish to PowerPivot Gallery, preview images are generated only if the following conditions are met:

  • The report and the PowerPivot workbook that provides the data must be stored together in the same PowerPivot Gallery.

  • The report contains only PowerPivot data. Preview images cannot be created for other data in the workbook. The data source must be a PowerPivot workbook.

Preview is not supported in other libraries. If you use other libraries besides PowerPivot Gallery, or if preview images are not important, you can ignore the requirements for the type and location of report data sources for your workbook. Both the report and the PowerPivot workbook can be accessed from any server location.