Tuesday, August 17, 2010

Configure Excel Services with SharePoint 2010

Recently I needed to get Excel Services to work on SharePoint 2010 for demo purposes on a VM of mine. I didn't want any delegation and wanted all the users to basically see the same pivot tables and be able to play around with some of the cool Excel 2010 functionalities like slicers. Turns out this is easier said than done. The new SharePoint 2010 architecture gives you 3 authentication options as a service
None : It uses the Secure Store Service Unattended account
Windows Authentication: Requires Kerberos Delegation to be in place
SSS : Requires you to specify the ID of the Secure Service Store you configured for Excel

For this simple tutorial I will configure SharePoint 2010 to work with Excel Services using None Authentication.

  1. Go to SharePoint Central Administration --> Applications Management --> Manage Service Applications
  2. First we will configure the secure store service so click on the Secure Store Service and press Manage
  3. Create a New Target Application and name it "ExcelServicesSSS" with a Target Application Type: Group. Follow through the wizard with the default settings until it creates the Secure Store
  4. Right Click on the Secure Store Service called "ExcelServicesSSS" and set credentials. Use the Windows account you want Excel to use when connecting to Analysis Services.
  5. Go Back to Applications Management --> Manage Service Applications --> Excel Service Application --> Global Settings and set the Application ID to "ExcelServicesSSS" 

  1. Go into an excel pivot table and in the connection properties go to Definition --> Authentication Settings --> None

Then go into your SharePoint Website and upload this excel sheet in a document library, then use Excel Web Access Webpart to connect to it and thats it :)

Excel services works

No comments:

Post a Comment