Excel-based Reporting Services, anyone?

Perhaps, you’ve heard that Excel 12 will support publishing and executing Excel spreadsheets on the server through a server-based technology (currently dubbed Excel Services). My first reaction when I’ve heard about this was “who on earth would ever want to do this?” However, after reading the David Gainer’s excellent blog (will anyone compile these great notes into a book?) I got my “Eureka” and I have to report a mind shift. David is a Group Program Manager for Microsoft Excel. It is always great to see Microsoft engineers actively sharing first-hand knowledge so we can get the news straight from the horse’s mouth.

Here is what I think about Excel Services based on the David’s notes (I still have to try out my hypothesis). I find the Excel Services model very similar to the Reporting Services architecture. The Excel user authors the spreadsheet using Excel. Then, she can publish the spreadsheet manually or programmatically to the server (through Web service interfaces). The Excel team took the easy way out and decided to leverage SharePoint as a server repository which in this case fulfills the role of the RS report catalog. A published spreadsheet can be viewed in the browser. A special renderer is used on the server side to render the spreadsheet in HTML (IE 6+ only), just like a RS report can be rendered in HTML. Although the spreadsheet is rendered read-only, some interactivity is supported, i.e. the end user can change the PivotTable filter. Behind the scenes, when the user interacts with the spreadsheet, Excel calls down to a Web service to refresh the data (assuming data is retrieved from a data source).

Similar to RS, the user can pass parameters to the published spreadsheet to control the results of the calculations. Finally, a spreadsheet (or portion of it) can be secured using the SharePoint Windows-based security. So, in a nutshell, we are going to get Excel-based Reporting Services.

Is this a good thing? I think so, at least for a couple of reasons:

1. You can integrate server-based spreadsheets into SharePoint-based dashboards.
2. More interestingly, you could publish PivotTable spreadsheets that source data from SSAS 2005 cubes and disseminate them across the enterprise.

The second scenario needs more clarification. The Excel author could manually or programmatically create a PivotTable report connected to an SSAS cube. Then, she could publish the spreadsheet to the server (think of it as a canned report). Once this is done, the end user could request the spreadsheet in the browser. The end-user will be able to interact with the report or open it locally in Excel 12 to gain access to all PivotTable features (subject to security restrictions). Unfortunately, Excel Services will not support full interactivity. For example, the users cannot add or remove PivotTable fields (what a bummer?). In addition, not all SSAS features will be supported, e.g. actions and server drillthrough are not supported.

Despite its limitations, I believe Excel Services will enable new integration scenarios especially in the area of financial reporting. This comes to no surprise considering the fact that one of the most common requirements for financial reporting I hear nowadays is “make it work like Excel” 🙂