Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using source data from an SSRS report in PowerPivot for Excel


By:   |   Last Updated: 2013-05-10   |   Comments (13)   |   Related Tips: More > 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.

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.


Last Updated: 2013-05-10


get scripts

next tip button



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

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, December 11, 2013 - 2:24:59 AM - AJIL Back To Top

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

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

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

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

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

Congrates Siddharth!!!


Monday, May 13, 2013 - 7:12:01 AM - Dattatrey Sindol (Datta) Back To Top

Congrats Siddharth on this milestone!

 

Best Regards,

Datta


Monday, May 13, 2013 - 12:36:36 AM - Arshad Back To Top

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

Congrats on your 50th tip for MSSQLTips.com.  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

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

http://www.atifshehzad.com


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

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

 

Scott Murray


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

Siddharth congrats on your 50th tip!

Greg


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

Siddharth,

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
MSSQLTips.com Community Leader


Learn more about SQL Server tools