Creating Report Datasets from Microsoft PowerPivot Data
You can use SQL Server Analysis Services data processing extension to retrieve data from a PowerPivot workbook that is published in a SharePoint PowerPivot Gallery.
Prerequisites
The PowerPivot data source must be published in a PowerPivot Gallery on a SharePoint site.
To support connections from Report Builder to a PowerPivot workbook, 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 R2 Feature Pack page on the Microsoft web site.
Data Source Type
Use report data source type Microsoft SQL Server Analysis Services.
Connection String
The connection string is the URL to PowerPivot workbook published on SharePoint in the PowerPivot Gallery or other library, for example, http://contoso-srv/subsite/shared%20documents/ContosoSales.xlsx.
Credentials
Specify the credentials that you need to access the PowerPivot workbook and SharePoint site, for example, Windows Authentication (Integrated Security).
Queries
After you connect to the PowerPivot data source, use the MDX graphical query to build a query by browsing and selecting from the underlying data structures. After you build a query, run the query to see sample data in the results pane. The query results are automatically retrieved as a flattened rowset, ready to be used as report data.
The query designer analyzes the query to determine the dataset fields. You can also manually edit the dataset field collection in the Report Data pane. For more information, see How to: Add, Edit, or Delete a Field in the Report Data Pane.
The text-based query designer is read-only for this data source. You cannot import .mdx files and use them directly in a query. You can import queries from existing reports that use Analysis Services data sources.
Filters
In the Filters pane, specify dimensions and members to filter out or to include in the query results.
Parameters
In the Filters pane, select the Parameters option for a filter to automatically create a report parameter with available values that correspond to the filter selections.
Remarks
If you open Report Builder from the PowerPivot workbook in a PowerPivot Gallery, the PivotTables, PivotCharts, slicers, and other layout and analytical features from the PowerPivot workbook are not re-created in the report. Instead, the blank report includes a preconfigured data source that points to the data in the PowerPivot workbook. Designing reports based on a PowerPivot workbook can be labor-intensive and time-consuming depending on the number of slicers, filters, and tables or charts that you want to re-create in the report. A better approach is to envision the presentation of the data that you want in a report independently from the PowerPivot design.
The data in a PowerPivot workbook is highly compressed; data retrieved from the PowerPivot workbook for a report is not compressed. You must select only the data that is needed in the report by using the graphical query designer. In the query designer, specify filters and parameters to limit the data before it is retrieved for the report.
Unlike connecting to an Analysis Services cube, a PowerPivot model has no hierarchies. To provide similar functionality to related slicers in the workbook, you must create cascading parameters in the report. For more information, see How to: Add Cascading Parameters to a Report (Reporting Services).
In some cases, you might need to adjust expressions to accommodate the underlying data values from the PowerPivot model. You might need to modify expressions to convert data to the right data type or to add or remove an aggregate function. For example, to convert data type from String to Integer, use =CInt. Always verify that the report displays the expected values from the data in the PowerPivot model before you publish the report.
Preview images of a report in a PowerPivot Gallery 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 from a PowerPivot data source.
See Also
Reference
Create a Reporting Services Report with PowerPivot Data