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.
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.
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.
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
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.
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.
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!
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.
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.
Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.