Import Data from a Reporting Services Report
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 PowerPivot workbook. The following procedure describes how to create the connection to the report and import the data to your workbook.
This topic contains the following sections:
Prerequisites
Choose an import approach
Import report data using a URL to a published report
Import report data using a URL to a data service document
Export data feeds from a report
Save an Atom service document (.atomsvc) file for future import operations
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. This version includes the data feed export feature that streams report data in the XML data feed format. You can identify this version of the report server by the Export as Data Feed option that appears in the report toolbar when you open the report in browser: .gif "Icon for a data service document (.atomsvc) file")
Choose an import approach
Report data is added once during import. A copy of the data is placed into the PowerPivot workbook. To pick up the latest changes to the underlying report data, you can either refresh the data from PowerPivot for Excel, or configure a data refresh schedule for the workbook after it is published to SharePoint. For more information, see Different Ways to Update Data in PowerPivot.
You can use any of the following approaches to add Reporting Services report data to a PowerPivot workbook.
Application |
Approach |
Link |
|---|---|---|
PowerPivot for Excel |
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. |
How do I… |
PowerPivot for Excel |
Click From Data Feeds to specify a service document (.atomsvc) that contains connection information. |
How do I… |
Reporting Services |
Click the Export to Data Feed button on the report toolbar to immediately export the data to PowerPivot for Excel if it is installed on your computer, or save the export file as an Atom service document (.atomsvc) file for future use. |
How do I… |
Import report data using an address of a published report
In the PowerPivot 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 PowerPivot 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 PowerPivot workbook.
In the PowerPivot 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 PowerPivot 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 PowerPivot 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 2010 and PowerPivot for Excel are installed on your computer, you will be prompted to open or save the file.
Click Open to immediately view the imported data in the PowerPivot window in Excel.
If the button is not visible, the report is not running on a SQL Server 2008 R2 version of Reporting Services. Consider moving or copying the report to a report server that is the SQL Server 2008 R2 release.
Note
Reporting Services includes an Atom rendering extension that generates the feeds from report definition files. That extension, rather than PowerPivot server software, creates report feeds and data service documents used to export report data to PowerPivot workbooks. For more information about how feeds are generated from Reporting Services, see Generating Data Feeds from Reports on the Microsoft Web site.
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 PowerPivot for 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 PowerPivot for 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 Manage PowerPivot Data Feeds on the Microsoft web site.
See Also