RSMUS.comImagine a scenario where an organization leverages Microsoft Excel to generate and share reports and charts with their stakeholders and management teams. Currently, the organization distributes the Excel spreadsheet with the reports and charts embedded in the email. There are well known issues with the email distribution as the tables and diagrams are expected to be dynamic and nearly real-time with the ever changing business data. What if the organization can put this spreadsheet with the required graphic representation behind a SharePoint site where the data is refreshed automatically based on pre-configured time intervals? This would eliminate e-mails. This blog post will describe the functionality and technical lessons RSM has learned in regards to Excel services within SharePoint.
Excel services is a service application available that allows users to utilize Excel features in SharePoint with ease. A simple action of adding a workbook to a document library allows users to open and interact with the document within the browser. This also enables the document to be shared within the organization, with the chances effective across the company within minutes.
While the above mentioned functionality is applicable to any Excel file, a different dynamic can be achieved by getting the data from a list within the SharePoint site. This can be configured by setting up the Excel file to retrieve the data from OData data feed and providing the link to the list. The data can be retrieved as a list or in the form of a pivot table, which can further be used for creating reports and charts. If the data in question is expected to change over time, it can be set to refresh automatically. This can be achieved by leveraging an unattended service account in the data connection settings
Once the workbook is complete, an Excel web access web part can be used to display the whole workbook or the view can be regulated with the help of named items. Named items can be particularly useful while displaying charts, or portions of reports. These capabilities can be especially significant while creating a dashboard that leverages data from a SharePoint list. Various charts, reports and pivot tables can be created and displayed with up-to-date information. As new items are added or edited in the list, the data will automatically reach the workbook, the charts will be re-calculated and will be shown within minutes on the dashboard page.
Some of the technical areas to take care of while utilizing the dynamic data refreshing abilities are listed below.
- The SharePoint site needs to be marked as a trusted location within the workbook and similarly the workbook location needs to be marked as a trusted location in SharePoint.
- The Excel workbook contains data connection properties that need to be set to refresh automatically. The user has the ability to set it to refresh each time the workbook is opened along with setting a time, in minutes, upon which the data will be refreshed without needing an input from the user.
- Within the data properties, the unattended service account would need to be added to the authentication settings. The account uses an Active Directory account for data access and has the credentials stored in the target application in SharePoint Secure Store.
- If the workbook contains a pivot table, the refresh properties of the pivot table need to be configured. Without this property, the data might be up-to-date in the workbook but not showing in the pivot table.
- The central administration connection timeout settings would also need to be configured so that after a certain period of time, the connection is refreshed.
The value of Excel services may not be instantaneously obvious but given the variety of functionality and the ease of data management provided it is only beneficial to build on it and leverage it in SharePoint. To find out more about this or other ways that RSM can assist you with your SharePoint needs, contact RSM’s technology consulting professionals at 800.274.3978 or email us.
To find out more about this or other ways that RSM can assist you with your SharePoint needs, contact RSM’s technology consulting professionals at 800.274.3978 or email us
