By: Neil Barkhina

One of the really cool features of Excel Services is the ability to use it for reporting and dashboards, without divulging its underlying formulas and data connections to the end users. However to get this working, you need to understand a little about what permission levels and configuration settings to use. Luckily SharePoint gives you a nice little head start with a Group called “Excel Services Viewers”

• Intro
• Show permission level screen and different options
• Show Document library ribbon options when you have view only
• Show the report after it loaded
  • Show the backstage in excel services
  • Show downloading the document, doesn’t have any formulas
• What happens if the report has data already embedded?
  • In that case show the reload workbook on open option in excel

The key to this group is that it is set to “View Only” which is a special permission level which only allows users to “View Items”.

That is different that the ability to “Open Items” which allows you to download a copy of the file without all its contents intact. “View Items” will load the file in SharePoint using server side handlers like either Excel Services or Office Web Apps. By setting this, you can test it now in a document library.

When you to the ribbon you will see that the option to “Download”  is hidden so the only way to view the document, or in this case report, is to click on the document itself which then goes ahead and launches Excel Services.

The interesting thing here is if you go to the Excel Services options and click Download, the functionality works. However what you will actually download is a stripped down version of the Excel Sheet without any underlying formulas or data:

One last thing you should always set is the option to “Refresh data when opening the file”:

This has a dual function in this case. One is that it will automatically show fresh data from the data source, whatever that may be like SQL Server or a Cube. The second and more critical thing it does however is from a security standpoint. When you set up Excel Services with pivot tables or charts, it usually downloads a copy of that data at that time locally right into the spreadsheet. If for whatever reason the connection to the backend doesn’t work then Excel will load that cached data to the user which may divulge more information than you would want, depending on the type of report you have. So you should always set this setting. That way if there is a break in connection, the user won’t see cached data but rather an error message below:

Also of note this is driven by the setting in Excel Services to “Stop When Refresh on Open Fails”:

As described by MSDN, it does the following:

In the Stop When Refresh on Open Fails area, you can specify if Excel Calculation Services stops opening a workbook if the workbook contains a Refresh on Open data connection that fails. By selecting Stopping open enabled, you ensure that cached values are not displayed if a refresh operation fails when the workbook is opened. When Refresh on Open is successful, cached values are purged. By clearing the Stopping open enabled check box, you risk displaying cached values if Refresh on Open fails.