Using source data from an SSRS report in PowerPivot for Excel

By: Siddharth Mehta | Comments (13) | Related: > Microsoft Excel Integration

Problem

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.

Solution

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.

Step 1

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.

Step 2

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.

In the PowerPivot interface, from the home menu select the option - From Other Sources

Step 3

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.

Step 4

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.

the next step is to create a pivot table or chart that sources data

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.

Next Steps

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

Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Wednesday, December 11, 2013 - 2:24:59 AM - AJIL	Back To Top (27757)
Hi Siddharth, Thanks!! Great tip on what i looked for. But incase of Two tables to my powerpivor datamodel should it be two rdls to be linked. i.e two connections to two seperate rdls, else any other ways ? I need two tables from ssrs report to my PP data model. Thanks in advance

Tuesday, October 15, 2013 - 2:49:54 PM - Shelly	Back To Top (27160)
Thank you, but I access reports that have user-defined values for report parameters. I keep getting an error that I must provide a parameter value. I don't know how to do that. Thanks for any help you can provide.

Saturday, May 25, 2013 - 10:43:48 AM - Gosta M	Back To Top (25139)
The example is also very vallid for Power View in Excel 2013. You van plot the count of emplyeeNames on a map

Friday, May 24, 2013 - 9:25:00 AM - Charles Allen	Back To Top (25125)
Great tip. I just did a webinar on PowerPivot on Thursday and I think articles like this will only enhance the use of PowerPivot.

Friday, May 17, 2013 - 8:53:34 AM - Neelam	Back To Top (24012)
Congrates Siddharth!!!

Monday, May 13, 2013 - 12:36:36 AM - Arshad	Back To Top (23911)
Congratulations Siddharth and thanks for sharing your knowledge\experience and helping community with your quality contents or tips. Hope to see more coming from you!