Create a Reporting Services Report Using PowerPivot Data
Looking for help with Power Pivot in Excel 2013? Go to Power Pivot Help on Office.com.
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 include the data visualization features you want to use.
This topic contains the following sections:
Prerequisites
Choose an Approach
Connect to PowerPivot Data Using Power View
Connect to PowerPivot Data in Report Builder
Connect to PowerPivot Data 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, Report Designer, or Power View. For more information, see PowerPivot Overview for IT Administrators.
To support connections from Power View, you must have SQL Server 2012 Reporting Services and the Reporting Services add-in for SharePoint 2010.
To support connections from Report Builder, you can use either SQL Server 2008 R2 or SQL Server 2012 Reporting Services. In addition, you must have 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 2012 Feature Pack page on the Microsoft web site.
To support connections from Report Designer, you can use either SQL Server 2008 R2 or SQL Server 2012 Reporting Services. In addition, you must have the OLE DB Provider for Analysis Services on your workstation computer. You can download and install it from the SQL Server 2012 Feature Pack page if you are using Report Designer on a computer that does not have this version of the provider.
Choose an Approach
Reporting Services authoring tools are installed using SQL Server installation media.
Application |
Approach |
Link |
|---|---|---|
Power View |
Start Power View from a BI semantic connection (.bism) file or Reporting Services shared data source (.rsds) that you create in SharePoint. Alternatively, if you have a PowerPivot workbook in PowerPivot Gallery, you can click a workbook’s Create Power View Report button to launch Power View using the data from that workbook. |
How do I… |
Report Builder |
Start Report Builder from PowerPivot Gallery, another library, or Report Builder that you open from a SQL Server report server. |
How do I… |
Report Designer |
Start SQL Server Data Tools (SSDT), create a report project, and connect to a published PowerPivot workbook on a SharePoint site. |
How do I… |
Note
A report server can be operating in native mode or in SharePoint mode. If the report server is configured for SharePoint 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.
Connect to PowerPivot data Using Power View
Before you get started, become familiar with the properties that improve the user experience of building a report that uses your PowerPivot workbook as a data model. For more information, see To prepare a model for reporting in this topic.
To view a demonstration of how Power View works, watch the Abundantly Crescent: Demos Galore video on the Microsoft Channel 9 web site. And for step-by-step instructions using sample data, see Tutorial: Create Charts, Tiles, and Other Visualization in Power View.
To create a report using PowerPivot data and Power View, you must have a BI semantic model connection (.bism) file or a Reporting Services shared data source (.rsds) file that points to a published PowerPivot workbook in a SharePoint library. In Power View, the .bism or .rsds file provides the data connection used by the report. Once you have a .bism or .rsds file in SharePoint, you can launch Power View from that item.
Click the following links for instructions on how to create a .bism connection or .rsds file in SharePoint:
Alternatively, you can also start a report from a PowerPivot workbook in PowerPivot Gallery by clicking the Create Report button:
.gif "Create Report button in PowerPivot Gallery")
To prepare a model for report authoring
We recommend that you review and use the report properties that help optimize a PowerPivot workbook for Power View. Setting properties in the workbook allow you to establish default behaviors when working with the data in Power View.
There is no Power View report preview feature in PowerPivot for Excel. You must publish the workbook to a SharePoint site for which Power View is installed, and then use Power View to test your changes.
A workbook should contain measures if you want to use a matrix or chart layout to explore summarized data along different dimensions. The measures must be explicit measures. If you created implicit measures using drag-and-drop in Excel, your measures will not be visible in Power View. For more information, see Measures in PowerPivot.
For workbooks that include image data, set properties to ensure that the images display correctly. There are two ways to specify images: a URL to an image file, or a binary image in the workbook.
To display image files provided by URL addresses, open the table and then select the column that contains a URL address and then click Image URL in the Advanced tab. By default, URL addresses are interpreted as text. Setting the Image URL property causes the report to retrieve the image file provided by the URL rather than return the URL address as text in a report. For more information about how to set this property, see PowerPivot Window: Advanced Tab.
To display binary images, select the table that contains the image data, and then click Table Behaviors in the Advanced tab. Set the Row Identifier property for this table. You can set other properties as well, but only the Row Identifier property is required for enabling binary images in a report. For more information, see Configure Table Behavior Properties for Power View Reports.
Hide columns to shorten and simplify the report field list in Power View. If your table includes columns that are unlikely to be used in a report, hide them from the field list so that the list contains just those fields that are interesting to report authors. For more information, see Hide Columns from Reporting Applications.
Check data types on the columns to ensure they are correct for report users. The data type on the column determines the data type used in the report. For more information, see Set the Data Type of a Column.
Review data formats for numeric data. A default format string is provided for all numeric data, but you can choose a different format string, affecting the text representation of the data in reports. For more information, see Date Format Dialog Box.
Define a field set for each table, as appropriate. Field sets determine whether one or more fields are automatically added to the report view whenever you click on a table in Power View. For more information, see Configure Default Field Set for Power View Reports.
Specify other table behaviors that determine how Power View responds to design decisions you make in the report. For tables that meet the requirements, you can prevent aggregations that result in unwanted subtotals in matrix layouts, or choose default labels and images that are used in specific layouts. For more information, see Configure Table Behavior Properties for Power View Reports.
Note
If you change table properties, but do not see the results you expect after saving the workbook to SharePoint, try deleting the original document in SharePoint and then republish the workbook.
For more information about properties that affect client applications, see Power View Reporting Properties.
Connect to PowerPivot data 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.
To start Report Builder from PowerPivot Gallery
The following steps assume that Reporting Services is installed and configured for SharePoint integration in the same SharePoint environment as PowerPivot Gallery.
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:
.gif "GMNI_btn_NewDocReportGallery")
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 a supported version. If Reporting Services is a newer version, a button that launches Power View rather than Report Builder is in the button bar. If this is what you see in your PowerPivot Gallery, you can launch Report Builder by clicking New Document in the library and selecting Report Builder Report as the document type. Your SharePoint administrator must have configured the gallery to use the Report Builder content type. If you cannot launch Report Builder, check with your administrator.
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:
Switch PowerPivot Gallery to All Documents view. To do this, click Library in the server ribbon and change the Current View to All Documents.
Select the empty report that contains the PowerPivot workbook data source connection.
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.
In the library, in Library Tools, click the down arrow next to New Document.
Click Report Builder Report. You must choose a report. You cannot create a report model or a report data source based on PowerPivot data.
In Getting Started, in New Report, select the type of report you want to create.
In Choose a dataset, select Create a dataset. Click Next.
In Choose a connection to a data source, click New.
In Select connection type, choose Microsoft SQL Server Analysis Services.
In Data Source Properties, click Build to specify connection information.
In Server name, enter a URL to the PowerPivot workbook (for example, http://Contoso-srv/Shared%20Documents/ContosoSales.xlsx).
Leave User name and Password blank. The connection will be made using the Windows account that you used to log on.
Click Test Connection to verify that you have access to the file, and then click OK.
In Data Source Properties, click Credentials.
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.
Click Next to continue to the Design a query page.
In Design a query, drag attributes and measures to the workspace to build your query.
Finish creating your report using the remaining pages in the wizard.
Click Run to view the report.
Save the report to SharePoint, choosing a server and site that support PowerPivot data access.
Note
Choosing PowerPivot Gallery as a location for the report works best if the PowerPivot data source is also in the same gallery. If the PowerPivot 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 PowerPivot data in Report Designer
Report Designer is hosted in SQL Server Data Tools (SSDT), 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.
In All Programs, click SQL Server Data Tools.
In the File menu, click New, and select Project. Choose Report Server Project.
In Solution Explorer, right-click Reports and select Add New Report.
In Select the Data Source, in Type, select Microsoft SQL Server Analysis Services.
Click Edit.
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.
Click Credentials.
Click Use Windows Authentication (Integrated Security), and then click OK.
Click Next.
In Design the Query, click Query Builder to select the measures and dimensions you want to use in your report.
Publish or save a report to PowerPivot Gallery
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.
See Also
Tasks
Import Data from a Reporting Services Report
Concepts
Use PowerPivot Workbooks on SharePoint