Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Consuming SSRS data in Excel 2007

with 8 comments

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:
    image
    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.
    ExcelXML
  • 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:
    image

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.

Written by Chris Webb

July 31, 2009 at 6:46 pm

Posted in Reporting Services

8 Responses

Subscribe to comments with RSS.

  1. This is exactly what I was looking for. I pulled the XML table into the Excel workbook, and then I used it as a data source for pivot tables and charts. It worked exactly how I wanted it to.

    Thank you!

    Jer

    Jer

    March 8, 2011 at 8:07 pm

  2. Great article!!!! Does someones know how to order the columns? The ordering does not match the ordering in my report. So I manually have to map each column to get same order in Excel. Otherwise it works like a charm!

    Richard

    January 12, 2012 at 10:23 pm

  3. I meant ordering in Excel map does not match ordering in SSRS report table. And its not alphbetical either…

    Richard

    January 12, 2012 at 10:25 pm

    • Sorry, I have no idea I’m afraid…

      Chris Webb

      January 12, 2012 at 10:38 pm

    • Richard,

      I had the same issue. The only way I could get it to work was to change the RDL so that the textboxes are output as Elements instead of Attributes. Each textbox has a “DataElementStyle” property. When I changed my details row textboxes from Auto to Element, the order in the XML map was correct.

      Geoff

      June 5, 2012 at 6:04 pm

  4. Hi Chris,

    I have a problem creating the XML Map in Excel 2010, with the following error:

    Cannot load the specified XML or schema source.

    Whitespace is not allowed at this location.

    I use the following URL:

    http://ROBERT_PC/ReportServer_MSSQLSERVER11%2fReport+Project1%2fXML_Report&rs:Command=Render&rs:Format=XML

    Do you have any ideas?

    Cheers,
    Robert

    Robert Bang

    August 29, 2012 at 4:47 pm

  5. [...] possible to consume data from an SSRS report in a much more elegant way than the method I described here, because you’d be able to pass parameters from Excel to SSRS (hmmm, maybe this needs its own blog [...]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,190 other followers

%d bloggers like this: