Excel 2007 Web Data Addin, and some thoughts on SSRS and Excel

Following on from my blog entry on Kapow the other week (and Jamie’s post on the same subject), I’ve just stumbled on something called the Excel 2007 Web Data Addin, something that Microsoft Research put together. It’s basically an addin that improves on Excel’s built-in functionality for importing data from web pages; it falls a long, long way short of what can be done with Kapow – and it’s a bit buggy – but it’s still interesting. There’s not much to it, or indeed much information out there about it, but here’s the link to download it and a pair of blog entries announcing its release from 2007:
http://research.microsoft.com/en-us/downloads/db5286b6-0bb0-4668-9ebc-c3e9b43a0683/default.aspx
http://blogs.msdn.com/xaw/
http://blogs.msdn.com/excel/archive/2007/10/16/excel-2007-web-data-add-in.aspx

There’s also a short demo video:

I wonder if this kind of functionality will be built into Excel 2010? It would be useful from a Gemini point of view if it was.

Another thought I had when looking at this was that SSRS reports would be the obvious source of data for this kind of functionality (although the addin refuses to work with Report Viewer, I guess it would work if the report was addressed directly via its URL). It should be pretty straightforward to suck data out of an HTML report with a tool like this, and indeed we’ve been told that SSRS 2008 R2 will be able to expose report data as a feed, but thinking about this it struck me that that’s not how I’d really want to work with SSRS data at all.

Rather than Excel linking to a table in an SSRS report, or SSRS rendering a report to Excel, or even what the OfficeWriter functionality we may get in Excel 2010 does, what I’d really want is an Excel addin that works in the same way as Report Viewer: as a sophisticated client to SSRS, pulling data into a worksheet rather than having data pushed to it. I’d want to be able to connect to a report from a worksheet, then be able to enter parameters from within the worksheet (either using dropdown boxes, like filters in a pivot table, or by entering values directly into cells, with me being able to choose which cells held the parameter values), and then when I clicked Refresh have the data from the report brought straight into one or more Excel tables or graphs, with pagination working too. All in all it would work in a similar way to the Excel Cube functions and make it much easier to build applications in Excel based on SSRS data. SSRS would be reduced to the role of running queries, handling some calculations, and doing caching; the actual layout of the report would be controlled from within Excel. Maybe it’s something that could be built by the community, or by MS as a sample app? It wouldn’t be much work to develop, I think, although the problem would be that you’d want to be able to use the RPL rendering format that Report Viewer users (mentioned by Robert Bruckner here) and that’s not publicly documented.