Sunday, August 22, 2010

Unsupported External List Refresh in Excel Services

Excel Services is a great tool allowing interactive data analysis process by predefined models or pivot tables created in Excel and posted to SharePoint MOSS or 2010.

However, when it comes to external lists it is not possible to refresh the external data source directly from the browser. The visitor needs to do it in Excel which makes the user experience much less attractive for most users.

For example, you can easily export a SharePoint list to Excel as a pivot table. When you refresh the pivot table in the web interface of Excel Services it shows the latest data within the table in Excel even though the list in SharePoint might have been changed since the last synchronization with Excel.

Since Excel Services strips VBA functionality from the Excel model, it is also impossible to run the refresh via a macro button.

One would suspect that this has been designed on purpose to boost the purchase of MS Office latest versions but this limitation is very annoying and doesn't help Microsoft with their attempt to increase the usage of its BI solutions.

As a result, we are planning on developing a solution that will run a background refresh defined in the Scheduler. This will let people get the up-to-date external data based on the time intervals of the auto-refresh process. We haven't decided yet if it would be free though.

Saturday, August 21, 2010

Save as Template doesn't work for large SharePoint Lists or Libraries

In MOSS, WSS 3.0 and SP 2010 there are limitations on the file size when it comes to saving the entity as a template including its data.

The work around is to use STSADM to extend this limitation. If you want to extend the size to 30MB for example, use the following command:

stsadm -o setproperty -propertyname max-template-document-size -propertyvalue 30000000