Microsoft Office, from version 2007 and beyond, supports several ways of inserting images into documents. This functionality can be very helpful to us when we want to display data from a SQL Server Reporting Services (SSRS) report within an Excel file (although the same would be true for Word or PowerPoint).
Option #1: Report Data as an Image in Excel
Suppose that you, like one of our customers, want to include a small graph of enrollment change over time at the bottom of several spreadsheets. You want the data to be up-to-date as soon as a spreadsheet is opened. One way to accomplish this is to import the enrollment graph as an image rendered by the SSRS server. The options for embedding images in Office 2010 and up are:
Insert--this creates a static copy of an image (if the image is report data, that data will not be refreshed when the file is opened)
Link--this creates a connection to the source of the image, but does not embed the image in the file (if the image is report data, that data will be refreshed when the file is opened)
Insert and Link--this creates a connection to the source of the image, and embeds a copy of the image in the file (if the image is report data, that data will be refreshed when the file is opened)
So, to embed our enrollment graph in a spreadsheet, we can follow these steps:
Use SSRS URL Access (more at http://msdn.microsoft.com/en-us/library/ms153586.aspx) to render the report as an image like this:
In Excel use the "Link" or "Insert and Link" options mentioned above to create a connection to the report (rendered using the technique above) in the same way you would for any image file. In Office 2013, the Excel interface for inserting the image looks like this:
The next time the Excel file is opened, Excel will look to the URL specified as the "link" above and render the current state of the data--if the report has changed, the new data will be shown.
(For Winona State specific uses of this technique, see this blog post: https://mywsu.winona.edu/departments/ir/techblog/Lists/Posts/Post.aspx?ID=56)
Option #2: Consuming a Report as XML
We also have the ability to connect to SQL Server Reporting Services reports with parameters that cause a report to be rendered in XML. Since Excel is quite good at using XML as a datasource, XML-formatted reports can be easily consumed by Excel for use in data tables, pivot tables, charting, etc.
Option #3: Consuming a Report at CSV
This method can actually be used for XML as well, but it is a little cleaner in CSV.
In Excel 2016 open the Data Tab. Select "Get Text/CSV" button in the "Get & Transform Data" section. This will open a dialog asking for a CSV file location. Provide it the URL for a SSRS report similar to:
https://reports.winona.edu/ReportServer?%2FFacilities%20Reports%2FEMS%20Reports%2FEMS%20and%20ISRS%20Reconciliation&rs%3ACommand=Render&rs%3AFormat=CSV&rc%3AItemPath=table1
Excel 2016 with use the Power Query framework to import the data into Excel. It will even determine data types during import.
You can edit the query using the usual Power Query editor. (On the Data tab click "Queries and Connections" if that pane is not visible on the right in Excel. Sometimes I need to close Excel and re-open it between importing the data and editing the queries.) You may need to add a step by clicking "Remove Rows" and then "Remove Blank Rows".
The Excel document will pull from data from the SSRS report whenever you click the "Refresh All" button on the Data tab.
What doesn't work
At this time, "From Web" and using the ATOM/OData feed does not seem to be compatible with SSRS reports.