SharePoint vs. Excel – Round 2

In December 2009, I pitted Excel vs. Custom Lists and Excel lost that battle. Recently, Excel asked for a rematch. When Marc Anderson was teaching my team about developing in SharePoint’s Middle Tier, he asked if he could work with some of our data and I pointed him to a survey we had run in 2010. Marc showed us a few techniques, but everybody agreed that analyzing a survey was a task probably best handled by Excel. We moved our attention to a different source for the more advanced examples.

After Marc left, I wanted to begin my own middle-tier journey, and the survey was a good-sized set of test data and the Excel-based reports served both as specifications and test results. I wired up a few Data View Web Parts in what I labeled a “Summary View” page. The survey had been for an off-site customer meeting; I was showing: the number of attendees; the number bringing guests; the number of hotel rooms needed, the number of people coming to dinner, breakfast, and various events and breakout sessions. I showed this fledgling analysis page to a few people; everybody had the same reaction – they wanted one for the 2011 survey. Why? What made the promise of a set of browser-based analysis pages more appealing than the reports we had in Excel?

Live Results – The SharePoint to Excel connection is easy, but somebody has to make it. Somebody has to download the results, refresh all the Pivot Tables and then print the pages for others to view. If you’re thinking that we could let everyone access the spreadsheet, read the next paragraph. SharePoint Data Views are live, they change when the survey changes and they are available 24/7. In addition, the summary page DVWPs link to deeper views of the same data. For example, the Web Part that summarizes the main dinner and reception shows the total number of attendees and guests coming for dinner. If you click on the title, we bring up a page that shows the name of each attendee, that of his or her guest and any food allergies or restrictions either may have.

No Errors – Excel is a great product and a fantastic analysis platform, but unless you go to a lot of trouble, it’s fairly fragile. It is remarkably easy for someone to forget to press Refresh All, to unclick a filter in a Pivot Table or worse, overwrite a formula. In prior years, when any of these things happened, we had to find the Excel expert who had written the spreadsheet. By comparison, the XSL behind the DVWPs is protected by SharePoint permissions; they can only be modified by people who know what they are doing (hey, I had two days’ worth of training).

Easy Access – We run this survey off of our Internet-facing server so our customers can get to it. The analysis spreadsheet was on our file system, behind our firewall. This meant that you could check those results in the office or over VPN, on a laptop with Excel installed. Of course this assumes that someone did the download, refresh and save operation first. Now, if you can access the survey, you can view the results. The people who run this event like the fact that they can check the survey results from anywhere, at any time, and the page even looks good on an iPhone. There’s an additional benefit to this last fact. During the event, our people have to know who is supposed to be at certain places, at certain times. Who is supposed to be at dinner, in a breakout session, on a bus to an event, etc? Now, any one of our employees can check any of this information from the event venue without carrying a bunch of paper around. Those last minute changes, the ones that were recorded after we boarded the planes; they will be accurately reflected on the result pages.

So far, I have completed the Summary View, and several detail views. I have figured out how to dissect all the bits and pieces of data held inside a survey response and how to spit them back out to a web page in an acceptable manner. Also, as of yesterday afternoon, the most important user of this information, the person responsible for the event, told me that he “really likes the fact that he can access these results whenever he wants to.” He realizes that the Excel Pivot Table reports looked better, but he wouldn’t go back if it meant giving up these new features – it doesn’t get any better than that. Of course, if Excel wants to go another round, I’ll bring Marc back and we’ll make those pages look amazing!