Excel Solutions in SharePoint

In my last post, I talked about the situational solutions that users themselves has created in Excel - and how these isolated workbook islands counters knowledge management and the ability to leverage the information and people ecosystem of your company. To subject the Excel workbook sprawl to shared knowledge management, you must provide Excel-style services as components of your Enterprise 2.0 (E2) platform.

The nice thing about Excel workbooks is that it gives the users great flexibility and information processing capabilities in a tool that is quite simple to learn and use. The flexibility of Excel must be preserved if you are to gain the promised E2 benefits: innovation by harvesting your company's collective intelligence through sharing and collaboration.

This post is about the state of most existing Excel 2003 and 2007 solutions I've seen, and how to incorporate them into the SharePoint 2007 platform. I will also talk about the future of Office automation (VBA, macros) and Excel Services/Excel Web Access in the upcoming SharePoint 2010 (SP2010).

Excel has good integration with SharePoint lists, and storing data in SharePoint lists has the benefit of shared data entry and collaboration on the list and collateral information. In Office 2003 a linked SharePoint list can be edited in both Excel and SharePoint and the changes then synchronized back and forth. The list can even be edited offline. This two-way link synchronization mechanism was removed in Excel 2007 - data can still be linked and the link updated, but there is no ootb way to edit that data in Excel 2007 and push it back into Excel. A list sync add-in exists, but this will require installation on client PCs, possibly an issue in most large companies.
Still, data can still be edited in SharePoint lists and linked into Excel workbooks to use e.g. the impressive charting capabilities of Excel. The simplest way to do this is to switch the list into datasheet view and open the task pane using the action menu (click to enlarge figure). Export the data as a linked list to Excel, create the chart or pivot table from the linked list, enable auto-update of the linked list, then save the workbook back into a document library. Your users can then open the workbook to do reporting based on the live SharePoint list data.

So, what if you want to have those charts and pivot tables as parts of your SharePoint sites? Wouldn't it be nice to have a unified, shared Excel-style workspace?

Excel 2003 users can try this: Publish a graph/chart created in MS Excel onto your SharePoint Site. Note that Excel 2003 uses the Office Web Components (OWC) that are deprecated. Excel 2007 users can also publish charts as web-pages as shown here. In both cases, the Page Viewer web-part is used to show the published workbook elements.

If you have Excel 2007 and MOSS enterprise edition, then the workbook can be published to Excel Services and instead be made directly available as part of SharePoint through Excel Web Access!?? You think, but there are pitfalls...

Excel Services (MOSS enterprise edition only) is a natural choice for enabling shared access to workbook solutions, while allowing the users to create the workbooks themselves. This combines the flexibility of Excel while providing shared management and publishing of the solutions. There are, however, some limitations like no data editing, no VBA macros or add-ins and, the most painful in MOSS, SharePoint Excel Services cannot consume live data stored in linked SharePoint lists! The latter limitation is a very common topic on the Excel Services forum. According to information on the forum, this problem is very likely to be fixed in SP2010. In the meantime, have a look at consuming SharePoint lists using Excel UDF.

The same problem applies to SQL Server Reporting Services (SSRS), there is no simple ootb way to report on data stored in SharePoint lists. Again, this problem is very likely to be fixed in SP2010. In the meantime, have a look at consuming SharePoint lists using SSRS. Another issue with using SSRS is that there is no self-service provisiong of new report types, not exactly enabling E2 freeform, emergent social collaboration on solving business problems.

Also keep an eye on the coming Office 14 web applications and the integration with SP2010. This might give the best of both Excel and SharePoint Excel Services, with a more seamless edit-publish mechanism than today. Maybe even working editable shared web-enabled workbooks will become a reality.

So if you somehow enable your Excel Services workbook to consume data from SharePoint lists (I won't even mention the unsupported database view approach), then you will most likely want to provide a richer user experience by allowing filtering of the information shown in the Excel Web Access web-parts (EWA). And provided that there are more EWA web-parts on your page, you may also want to connect them, e.g. when clicking a cell in one EWA web-part triggers an event that affects other EWA web-parts.

The EWA web-parts are filter connections consumers. The MOSS filter web-parts can be used to build a powerful filtering UI that connects to Excel Services parameters that do the actual data filtering. Note that the Filter Web Parts are in the MOSS enterprise edition only, so if you're on standard MOSS then you're left with the rather crude HTML Form Web Part or 3rd party filtering web-parts.

You can also use the ExcelServicesAjax.js script in a Content Editor Web Part to connect EWA web-parts using JavaScript and to use AJAX to access the Excel Services API. Refer to Shahar Prish's blog or his book Professional Excel Services for more details. To avoid scripting connections, whole worksheets can be published instead of multiple single elements. The latter requires less of non-technical users.

If Excel Services is not an option for you, then have a look at Dundas Charts for SharePoint or FusionCharts. Dundas can consume data from a variety for data sources, and Dundas has ootb support for SharePoint lists. This gives the users the E2 flexibility to create mashups by connecting data to charts themselves, within the collaboration platform. The Fusion Flash components also include maps, but there seems to be no ootb "for SharePoint" integration.

The chart creation user experience with Dundas is quite good when all that is needed is configuration, but there is a disconnect to SharePoint terms that will put non-technical users off (e.g. Dundas use "int32" instead of "number", and what happened to "choice"?). In addition, any customization needed beyond configuration, will require VB.NET/C# knowledge, again a duh! for user self-service solutions.

These charting web-parts are also subject to the same filtering and connections aspects as the EWA web-parts. Dundas uses parameter filtering and supports standard web-part connections. Note the lack of wildcards or (all) or (blank) choices in the filter parameters - this makes Excel style chart filters on text values impossible to recreate.

All in all, if you have data in SharePoint lists, then using SharePoint linked workbook lists in the Excel client may be the best approach while waiting for SP2010. If your data is in a store supported by Excel Services, then I would recommend using Excel Services as this combines user flexibility, self-service, sharing and knowledge management.

I had intended to write about what to do with Excel VBA macros and linked workbooks, and the future of VBA with relation to Excel Services, OOXML, VSTA/VSTO/.NET and Office 14-16 in this post, but due to the length that will be the topic of another post.