Refreshable Report Data in Excel

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:

https://SSRS_Server.domain.edu/ReportServer/Pages/ReportViewer.aspx?MyReport&RS:Command=Render&RS:Format=Image&RC:OutputFormat=PNG&Param=1

• • 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: