How to use SSRS reports as Data Source in Excel | SansSQL

Tuesday, August 30, 2016

How to use SSRS reports as Data Source in Excel

As we all know SSRS is a server-based reporting platform that allows to create and manage a wide variety of reports, and deliver them in a range of formats, but many of them are still addicted to the excel for reporting as it is more convenient and easy to use.

In this post, let us learn how to use SSRS report as Data Source for Excel Reporting.
First of all, we need to enable Data Analysis in Excel and then continue importing data from SSRS into Excel.

Go to "Data" tab and Click "manage Data Model".

On the pop-up click "Enable" to enable the data analysis add-ins.

In the Power Pivot for excel pop-up, click on "Get External Data" and choose "From Other Sources".

Choose "Report" in the "Connect to a Data Source" pop up.

Next, Choose the Report Path which you want to use as the source.

In the Name box, type in the ReportServer URL and choose "Open".

Then Navigate to the rdl file.

Choose the rdl file and click "Open".

Once the rdl file is chosen, you can preview the report and click next.

Then choose the data table/view under the report and click "Finish".

Click Close on the successful completion of the import.

This is now import the data into the excel for further reporting.

Now go back to the excel and choose "Pivot Table" from the "Insert" tab.

And choose the Report connection that we previously imported.

Then choose where to create the new Pivot table.

Now you can create your own reports out of this data using the excel which is more convenient for you.


Unknown said...

The information in this blog is extremely useful for the people.
right here

Serg K. said...

Thanks, that was really useful

Sarah Jacb said...

It was thinking about whether I could utilize this review on my other site, I will connect it back to your site though.Great Thanks. contaminated land remediation

MT Polis said...

You have brought up a very excellent details , thankyou for the post. “Beginnings are apt to be shadowy and so it is the beginnings of the great mother life, the sea.” by Rachel Carson. help for excel spreadsheet

Fabian Lopez said...

is the data refreshed from the data source or just one-time import? So if the data is updated on the source every night, is the excel reading from there (refreshing) or just for the imported data?

eco said...

Oh my goodness! an incredible article dude. spreadsheet expert near me

Post a Comment