Using source data from an SSRS report in PowerPivot for Excel

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


 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.

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.

An SSRS report deployed on SSRS server

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.

Select the SSRS report file deployed on the SSRS Report server

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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Siddharth Mehta 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

Comments For This Article

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.

Wednesday, September 11, 2013 - 4:40:52 PM - Todd Back To Top (26746)

I found this article not useful as the step 2 provides zero details on how you actually accomplish it.  There's no sample string or dialogue box showing how a specific report was selected.

It's importantant as technicians to remember the perspective of people not as familiar with the topic as we are.

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 - 7:12:01 AM - Dattatrey Sindol (Datta) Back To Top (23915)

Congrats Siddharth on this milestone!


Best Regards,


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!

Sunday, May 12, 2013 - 11:07:25 PM - Rick Dobson Back To Top (23910)

Congrats on your 50th tip for  I know first-hand how hard it is to write a good tip, and I genuinely admire your dedication to helping the SQL Server community.

Best regards,

Rick Dobson

Saturday, May 11, 2013 - 5:25:21 AM - Atif Back To Top (23898)

Siddharth, Congrats on this score of quality content. I appreciate your contribution for the SQL Server community and thanks for sharing the solution of various problems. I hope you will be consistently sharing with us your experiences on this portal.

Best of luck

Atif Shehzad

Friday, May 10, 2013 - 4:41:06 PM - Scott Back To Top (23890)

Yippee Tip number fifty.  Great JOB!!!!!


Scott Murray

Friday, May 10, 2013 - 4:34:30 PM - Greg Robidoux Back To Top (23889)

Siddharth congrats on your 50th tip!


Friday, May 10, 2013 - 4:21:36 PM - Jeremy Kadlec Back To Top (23888)


Congrats on your 50th tip!  We appreciate all of the hard work you have done to help educate the community on the Microsoft BI Stack!

Best wishes and much success!

Thank you,
Jeremy Kadlec Community Leader

get free sql tips
agree to terms