You can use a Reporting Services report that has been published to a SharePoint site or a report server as a data source in a Power Pivot workbook. The following procedure describes how to create the connection to the report and import the data to your workbook.
In this article
Prerequisites
You must use a report definition (.rdl) file as a data source. Importing from a report model is not supported.
You must have permission to open the report under your Windows user account, and you must know the address of the report or the report server that hosts it. You can check your permissions by trying to open the report in a Web browser first. If the report opens, it confirms that you have sufficient permissions and the correct URL.
Reporting Services must be version SQL Server 2008 R2 or later. Those versions include the data feed export feature that streams report data in the XML data feed format. You can identify the report server by the Export as Data Feed option that appears in the report toolbar when you open the report in browser: 
Choose an import approach
Report data is added once during import. A copy of the data is placed into the Power Pivot workbook. To pick up the latest changes to the underlying report data, you can either refresh the data from Power Pivot in Excel, or configure a data refresh schedule for the workbook after it is published to SharePoint.
You can use any of the following approaches to add Reporting Services report data to a Power Pivot workbook.
|
Application |
Approach |
Link |
|
Power Pivot |
Click From Report to specify an address to the report. You can also click From Other Sources, and then click Reports, to specify a n address to a report. |
|
|
Power Pivot |
Click From Data Feeds to specify a service document (.atomsvc) that contains connection information. |
|
|
Reporting Services |
Click the Export to Data Feed button on the report toolbar to immediately export the data to Power Pivot in Excel if it is installed on your computer, or save the export file as an Atom service document (.atomsvc) file for future use. |
Import report data using an address of a published report
-
In the Power Pivot window, in the Home tab, click From Report. The Table Import wizard opens.
-
Click Browse and select a report server.
If you regularly use reports on a report server, the server might be listed in Recent Sites and Servers. Otherwise, in Name, type an address to a report server and click Open to browse the folders on the report server site. An example address for a report server might be http://<computername>/reportserver.
-
Select the report and click Open. Alternatively, you can paste a link to the report, including the full path and report name, in the Name text box. The Table Import wizard connects to the report and renders it in the preview area.
If the report uses parameters, you must specify a parameter or you cannot create the report connection. When you do so, only the rows related to the parameter value are imported in the data feed.
-
Choose a parameter using the list box or combo box provided in the report.
-
Click View Report to update the data.
Note: Viewing the report saves the parameters that you selected together with the data feed definition.
Optionally, click Advanced to set provider-specific properties for the report.
-
-
Click Test Connection to make sure the report is available as a data feed. Alternatively, you can also click Advanced to confirm that the Inline Service Document property contains embedded XML that specifies the data feed connection.
-
Click Next to continue with the import.
-
In the Select Tables and Views page of the wizard, select the check box next to the report parts that you want to import as data.
Some reports can contain multiple parts, including tables, lists, or graphs.
-
In the Friendly name box, type the name of the table where you want the data feed to be saved in your Power Pivot workbook.
The name of the Reporting Service control is used by default if no name has been assigned: for example, Tablix1, Tablix2. We recommend that you change this name during import so that you can more easily identify the origin of the imported data feed.
-
Click Preview and Filter to review the data and change column selections. You cannot restrict the rows that are imported in the report data feed, but you can remove columns by clearing the check boxes. Click OK.
-
In the Select Tables and Views page, click Finish.
-
When all rows have been imported, click Close.
Import report data using a URL to a data service document
An alternative to specifying a report address is to use a data service document (.atomsvc) file that already has the report feed information you want to use. A data service document specifies a URL to the report. When you import the data service document, a report feed is generated from the report and added to the Power Pivot workbook.
-
In the Power Pivot window, in the Home tab, click From Data Feeds. The Table Import wizard opens.
-
In the Connect to a Data Feed page, type a friendly name to use when referring to the data source.
This name is used only within the Power Pivot workbook to refer to the data source. Later in the wizard, you will set the name of the table where the data is stored.
-
Type a path to the data service document (.atomsvc) file that specifies the report feed. You can specify an address to the document if it is stored on server, or you can open it from a folder on your computer. Alternatively, you can click Browse to navigate to a server that has the data service document you want to use.
-
Click Test connection to make sure a feed can be created using the information in the data service document.
-
Click Next.
-
In the Select Tables and Views page of the wizard, select the check box next to the report parts that you want to import as data.
Some reports can contain multiple parts, including tables, lists, or graphs.
-
In the Friendly name box, type the name of the table where you want the data feed to be saved in your Power Pivot workbook.
The name of the Reporting Service control is used by default if no name has been assigned: for example, Tablix1, Tablix2. We recommend that you change this name during import so that you more easily can identify the origin of the imported data feed.
-
Click Preview and Filter to review the data and change column selections. You cannot restrict the rows that are imported in the report data feed, but you can remove columns by clearing the check boxes. Click OK.
-
In the Select Tables and Views page, click Finish.
-
When all rows have been imported, click Close.
Export a report as a data feed
-
Open a report from Report Manager, SharePoint, or a report server.
-
On the report toolbar, click the Export as Data Feed button:
If Excel is installed on your computer, you will be prompted to open or save the file.
-
Click Open to immediately view the imported data in the Power Pivot window in Excel.
If the button is not visible, the report is not running on a supported version of Reporting Services. Consider moving or copying the report to a report server that is a supported release.
Note: Reporting Services includes an Atom rendering extension that generates the feeds from report definition files. That extension, rather than Power Pivot server software, creates report feeds and data service documents used to export report data to Power Pivot workbooks. For more information using feeds in Power Pivot, see Power Pivot Data Feeds on MSDN.
Save an Atom service document (.atomsvc) file for future import operations
If you do not have an application on your computer that can open a report feed, save the document for future use on a computer that has Power Pivot in Excel. The document that you save specifies an address to the report. It does not contain data from the report.
-
Open a report from Report Manager, SharePoint, or a report server.
-
On the report toolbar, click the Export as Data Feed button:
-
Click Save to store the .atomsvc file on your computer. The file specifies the report server and location of the report file.
To use the .atomsvc file later, you can open it in Power Pivot in Excel to import the report feed. For more information about how to specify a data service document for report feeds, see Import report data using a URL to a data service document in this topic.
You can also publish this file to a data feed library on SharePoint to make it available to anyone who wants to use report feeds in other workbooks or reports. For more information about data feed libraries, see Power Pivot Data Feeds on MSDN.
