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.

SharePoint: Excel Long-Tail Knowledge Management

In these uncertain times, push systems for knowledge sharing and management are giving less results than before due to the limited planning visibility into the future - it is just becoming too hard to provide the users with the tools they need before they need it. The knowledge workers have turned to pull systems to create situational software solutions to assist in solving their business problems. Excel has always been business user's favorite tool for capturing situational solutions and managing their personal knowledge. Excel has become the most successful long-tail client in the enterprise.

For businesses to be successful, they must now cater for rapid innovation through collaborative knowledge sharing pull systems, known as Enterprise 2.0 systems (E2). Thus, having knowledge locked into personal Excel worksheets on peoples disks pose a threat to learning and innovation across a company. The same can be said about any knowledge that is not shared and findable, typically locked down by ultra traditional policies such as "need to know basis" applying by default strict access control.

Companies want to unlock all this intrinsic knowledge by using collaboration platforms, and of these SharePoint is what most organizations decide to implement (adoption rate between 55% and 80%).

So business users now face a challenge on how to move their Excel solutions into the E2 sharing and collaboration platforms. Most of them even want to enable shared information entry into worksheets, so they welcome SharePoint as the data capture tool. But how to provide the rich knowledge visualization of Excel on the web? Enter MOSS Excel Services. I recommend reading the Introduction to Excel Services and Excel Web Access article for a good introduction to ES/EWA. Also read Business Intelligence with SharePoint and Excel on TechNet Magazine for an analysis of some usage scenarios.

It might all seem simple at this point, but there are some limitations with Excel Services and multiple other options for re-implementing Excel client-side solutions functionally on the SharePoint platform. Add to the picture VBA macro infested Excel workbooks, and there is no simple migration path. In addition, the choices you make can exclude the business "super users" from being able to create situational solutions - hindering the Web 2.0 (W2) style flexibility that you aimed to provide in the first place.

A SharePoint roadmap for Excel long-tail workbooks is needed, and that is the scope of my next post.