Consuming SSRS data in Excel 2007

In a recent post I sketched out an idea I had about consuming data from SSRS data within Excel: rather than have SSRS push data to a new Excel spreadsheet by rendering a report to Excel, what I wanted to do was have an Excel spreadsheet that pulled data from an SSRS report. Why do this? Well, we all know that users always want their data in Excel rather than in any other format. If we take an SSRS report and render it to Excel, though, we have two problems:

1. Getting an SSRS report to render to Excel in exactly the way you want, at best, involves a lot of trial-and-error. Even then when you render to Excel there’s a whole load of Excel functionality that SSRS doesn’t support – for example, you can’t get native SSRS to render a workbook containing a macro.
2. Ever time we run the report, we are creating a new workbook. So once we’ve got the workbook there isn’t much point in doing any extra work in it, for example adding new formulas or charts, because it’s going to be superseded by a newer workbook the next time the report is run.

A tool like Softartisans Officewriter (which MS licensed a long time ago and possibly will appear with SSRS 2008 R2) will solve the first problem, because it allows you to upload an Excel workbook to SSRS which has data injected into it when the report is rendered, but not the second.

However, it is possible to pull data into Excel from SSRS and avoid these problems. Excel allows you to import data from an XML document into a worksheet; since you can get an SSRS report to render to an XML document, all you need to do is hook Excel directly up to the XML file generated by SSRS. Here’s how:

• The key to getting this to work is URL access to SSRS reports. Excel needs to know where the XML file it’s importing is – and you can give it the URL of an SSRS report, and in that URL specify that the report should be rendered to XML. Let’s take the Sales Order Detail report from the Adventure Works sample reports as an example:
  
  On my machine, the URL for rendering this report into XML is as follows:
  http://myserver/ReportServer?%2fAdventureWorks+2008+Sample+Reports%2fSales+Order+Detail+2008&rs:Command=Render&rs:Format=XML
  Paste this into your browser and you’ll automatically get an XML file downloaded; you can find more details on URL addressability of SSRS reports here.
• Now, open Excel 2007, click on the big round Office button in the top left corner, click the Excel Options button and on the Popular tab check the box “Show Developer tab in the Ribbon”. This will ensure you can see the functionality within Excel we’re going to be working with.
• Open the Developer tab and click on the Source button to open up the Source pane, then the XML Maps button in the Source pane, then Add on the XML Maps dialog, and then enter the URL of the SSRS report in the File Name box on the Select XML Source dialog and click Open.
  
• The XML Source dialog will now be populated. Click on a cell in the worksheet, and then right-click on a node in the XML Source pane and choose Map Element to map an element into a cell; click the Refresh Data button in the ribbon to actually bring the data into the worksheet. Here’s what the data from the report above looks like when mapped into Excel:
  

The point is that every time you hit the Refresh Data button in Excel the report is rendered, so you’re able to build your worksheet around live data from SSRS. You can of course pull data directly from data sources like SQL Server in Excel, but the benefit of doing it this way is that you can take advantage of SSRS features like caching and snapshots, and of course as an end user you may not have direct access to the source database anyway.

There are some obvious drawbacks to this approach:

• It’s a bit too technical to set up for end users, except perhaps for the most technical of Excel power-users.
• There isn’t an easy way to pass parameters to reports. You can of course pass parameters through the URL, but it would be great if it could be done from a dropdown box in the worksheet. I think with a little bit of coding you could create an Excel addin that would do this though.
  UPDATE: actually, I think some of the techniques discussed in this post on the Excel blog could be useful here
• Rendering to XML isn’t the ideal format for this task – although I’m not sure there is an ideal format (the RPL format used by Report Viewer might be a good candidate but it’s not documented). Books Online has details of how reports behave when rendered to XML; one obvious drawback is that there’s no pagination of data, so if you have a lot of data in your report spread across multiple pages, you’ll get all the data from every page in Excel.

That said, I think this this approach might be useful when you have a large number of existing Excel reports that currently have data copied-and-pasted into them manually and which can’t (for whatever reason) be turned into full SSRS reports.