Using source data from an SSRS report in PowerPivot for Excel
Operational reports developed in SSRS can return huge amounts of data and they have already gone through the development and testing effort to make sure the results are correct. Such a report might not have the data in the form to analyze in a customizable manner and many users may want to analyze similar data in Excel. In this tip we would look using an existing SSRS report to provide the data for PowerPivot in Excel.
PowerPivot for Excel is an Excel add-in that enables users to implement PowerPivot functionality on top of Excel 2010 or higher versions. In this tip we look at how to use a SSRS report for the source data for PowerPivot. This article assumes that PowerPivot and Excel 2010 / 2013 are installed.
To use the source data from an SSRS report, we can use any SSRS report deployed on an SSRS server. I have a sample SSRS report available as shown below.
Open Excel 2010 / 2013 and from the "PowerPivot" menu in the office ribbon select "Manage" which will open the PowerPivot interface.
In the PowerPivot interface, from the home menu select "From Other Sources" which will bring up a list of data sources supported by PowerPivot as shown below. From this list select "Report" as shown is the below screenshot. This option allows sourcing data directly from the SSRS report.
Select the SSRS report file deployed on the SSRS Report server. Complete the wizard and the report will get executed and data from the SSRS report will be added to the PowerPivot data model as shown in the below screenshot.
After the data model has been created from the SSRS report, you can then create a pivot table or pivot chart that sources data from this model as shown below.
Setting up the connection with the SSRS report and importing the data into the PowerPivot model is a one-time activity. Whenever there is a change in the report data, this can be synchronized in PowerPivot using the Refresh button available in the PowerPivot interface and the data in the pivot table or pivot chart will be refreshed. In this way one can create advanced analysis directly from a SSRS report using PowerPivot.
- Read more PowerPivot tips
- Download and install PowerPivot from here.
- Import data from two to three different SSRS reports and perform analysis.
About the author
View all my tips