Excel Services as a Reporting Solution

I recently had a requirement to implement a quarterly report for project’s to use in their SharePoint project workspace.  The quarterly report was a multi-tabbed Excel workbook that needed manual data entry from several data sources.  The new implementation was to automatically retrieve the data while still allowing some manual data entry for the portions that did not come from a pre-existing data store.  Some caveats were that we wanted to make sure there was not a client install.  Also, to edit the workbook, it would need to be opened on the client desktop and saved back to the server.

 

So, at first we tried to put ODC connections to the database into SharePoint and then reference the ODC in the Excel workbook.  Then, we would write queries in Excel against the datasource.  We encountered some issues here.

1. You can not use DataTables in Excel Workbooks in Excel Services.  You must use a pivot table instead.
2. The client machine will need to have access to the SharePoint farm or the ODC won’t be accessible (our solution was Intranet only)
3. We had web context based parameters that were being stored in a hidden sheet on the workbook.  We could probably have configured the queries in the workbook when provisioned to our sites, but we felt having configuration in the workbook

Ultimately, it was determined that since users needed to print workbooks from their local Excel, this was not viable as a solution.  Next, we looked at UDFs.

Let me first say that UDFs I like much better than ODC.  I think it just feels better writing a function that accesses targeted data rather than filtering large datasets in an ODC call.  UDFs are easy to write.  I won’t spend time here explaining how to implement.  My lessons learned were that you could quickly create a UDF method in a DLL, and on the development server add that UDF to a workbook.  You could then call the method in that workbook as you would call a normal Excel function.  In SharePoint, you need only allow the DLL’s UDFs in the SSP to be trusted.

Very quickly, I had reduced the workbook to one configuration data point that I could write into the workbook based on the project workspace the Excel needed to reference.  Then my UDFs would be in the workbook as follows =myUDF(OtherWorkSheetConfigCell!A1).  Worked like a charm in Excel and in Excel Services.

The deal killer was that on the client machine, the DLL does not exist.  No client side install is approved so unless having #NAME in a workbook was acceptable this would not work either.  So, the approach was abandoned after a week of PoC effort in favor of ASP.NET custom app hosted in SharePoint. 

I think that we may have been able to save the UDF workbook and in the save process replace the UDF functions with their resultant values, but we had expired our PoC time and instead when with the ASP.NET solution.

In conclusion, I was an Excel Services lightweight and am by no means an expert after this experience.  I will say that the effort was fustrating since there was little client side support.  Since Excel Services is a server based solution, I had hoped that UDFs would persist on the client machine once the Excel Services web view is left.  I did consider that users may need to have Excel Enterprise or Professional Plus, which was also a deal breaker in my scenario.  So far, our best use of Excel Services is to render a workbook in a browser.  I know there are many good uses of Excel Services, but most requirements I see require minimal client side interaction.  In these cases, the solution seems to break down.

I may revisit this at a later date to see if I was perhaps missing some nuances of the tool or a code based workaround to these problems.