How to use SSRS reports as Data Source in Excel

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.