Today's author, Pej Javaheri, a Product Manager on the Excel Services team, talks about one of the more common configuration questions we hear about from customers. One of the challenges in sharing Excel workbooks is ensuring that the right people see the right data. With Excel Services, this has become a lot easier ensuring user's only see the information they are authorized to view and not sharing information that is privileged or out of their scope. With a few simple configuration steps, workbook authors can quickly setup their reports to only display the information that is authorized by user's security privileges. This document describes how to setup SharePoint SharedServices for Excel Services, and how to setup your ODC connection to refresh workbooks on open. Ensure the server has the right permissions to access data connections, and run Excel workbooks on the server. To do this, go to the Shared Administration page of your Shared Services. First, modify the Trusted File Locations to include the locations that you will allow Excel workbooks to be accessed to display on Excel Services. In the Address field, enter the location of where the workbooks are stored. Most likely this will be in a SharePoint Document library. If you want to enable the whole SharePoint site, enter the root address of the SharePoint site, such as http://SharePoint . Under Location , ensure you select Children Trusted if you will be storing workbooks in folders from this location, especially if you are going to enable the whole site. Under External Data, select either "Trusted data connection libraries only", or "Trusted data connection libraries and embedded" to allow Excel Services to refresh data from workbooks that access data through ODC files. If you will be calling User Defined Functions (UDF), then check the box under User-Defined Functions. You may also want to disable the Warn on Refresh option under External Data Click OK to return to the Excel Services Trusted File Locations screen and apply all the changes otherwise a warning dialog box will require users to okay the action before refreshing the workbook in Excel Services. You should have something like this, which identifies the location of the workbooks that will be accessed by Excel Services. You can add as many locations as required for each shared service environment you setup. Next, we need to identify where the data connection libraries are stored for Excel Services to access and refresh data. Go back to the SharedServices administration page, and click Excel Services Trusted Data Connection Libraries, and then click Add Trusted Data Connection Library. Enter the location of the document library that the ODC files are saved in. In this example, I have added the default location under the Reports document library Creating the ODC file in Excel In Excel, select From Other Sources under the Data tab. Select the data source type and then enter the required credentials in the Data Connection Wizard. In the File Name field, select Browse and then type the location of the Trusted Data Connection Library (alternatively, you can save the ODC file locally, and the upload it to the Data Connection Library via SharePoint). If the connection requires specific authentication requirements, or single-sign on select the Authentication Settings and set the appropriate item. Now that you have the connection created, you need to modify the properties of the connection so that it is set to refresh the workbook when it is opened every time. Still in the Data tab, select Properties and then check Refresh data when opening the file. Create your Excel workbook like normal, and then publish to Excel Services. If data is coming from a relational source, publish it in a Pivot Table. And there you have it. Whenever users open the workbook, the data is automatically refreshed using their credentials to access the data source. Read More...