Thursday, April 4, 2013

Sharepoint 2010 Excel Calculation Services


Excel Services in Microsoft SharePoint Server 2010 is a shared service that you can use to publish Microsoft Excel 2010 workbooks on SharePoint Server. The published workbooks can be managed and secured according to your organizational needs and shared among SharePoint Server 2010 users, who can render the workbooks in a browser. Excel Services was introduced in Microsoft Office SharePoint Server 2007 and is available only in the Enterprise edition of SharePoint Server 2010.

How to do

1.     Go to central admin >> Application management >> Manage services application on Service Applications section >> New  >> Excel Service Application
2.     Input your service’s name and application pool
3.     you click “manage” on the Excel Service you’ll see the configuration:
Here’s where you configure things like:
Global Settings is to define:
Security, load balancing, session and memory utilization, workbook cache and access to external data
Trusted File Locations is where you tell sharepoint where “safe” excel files are allowed and you have settings like:
Locations, Session management, workbook properties, calculation behavior, external data, user defined functions
Trusted Data Providers:
Trusted Data Connection Libraries
User Defined Functions
Excel files can use a shared data connection so you define the connection once, and let excel files reuse that connection from multiple users! So if you need to update that connection, you How to do in one place.
Excel Services does not support Macros, so if you need that kind of functionality, you need to program against Excel Services API and create a User Defined Function and register that assembly in Excel Services as safe.
Excel Services also supports load balancing, so you can distribute the load between multiple Excel Service Applications in the farm.
4.     Go to central admin >> Application Management >> click to “Configure service application associations”
5.     Click to “custom” link of web application
6.     Choose Excel service Application
7.     In this demo, I’ll show you how to use excel services, shared data connection libraries and the unattended service account using Secure Store Service:
I have a database in SQL server that contains some demo data that i want do display in Excel:
8.     Start Excel Service and click on Connections:
9.     Select Add
10.  Choose browse for more:
11.   Now click on New Source:
12.  Choose SQL Server:
13.  Type the server name:
14.  Choose the Database that contains the table:
15.  Click Finish
16.  In the ribbon, click insert and choose PivotTable:
17.  Choose “use external connection”:
18.  Choose the connection we created:
19.  Click OK
20.  We need a better grouping so click on the first row and choose group >> Group Field on the ribbon:
21.  Hold down control and choose Quarter, Month and Year:
22.  Now we have a lot nicer grouping and by choosing expand/collapse>entire field you’ll get this:
23.  Expand group
24.  Now let’s go ahead and upload the file to a library in SP
25.  Name the file
26.  The file is now in SP:
27.  You get an error
28.  Make sure Excel service application is started
29.  Make sure Excel calculation service is started
30.  Clicking on the file will open the file in Excel Services in the browser:
31.  To try the Excel Web Access Web Part, go ahead and create a new web part page:
32.  Choose a layout for the web part page:
33.  Click add a web part and choose excel:
34.  Click to this link
35.  Enter the URL of the excel file:
36.  Now you’ll see the excel file in the web part:
37.  The connection information is now stored locally on the client computer, but it’s much easier to maintain if the file was stored in SP. There's a library called “Data Connection Library” in SharePoint that can be used for this:

38.  You can see .odc file at: Go to excel > Data tab > Connections > Properties > Definition Tab > Connection File
39.  Let’s upload the connection file(.odc) to the connection library:
40.  Choose Office Data Connection File as type:

41.  Also make sure that the library is added as a trusted connection library in Central Admin:

42.  Let us now take a look at the “Unattended Service Account” in SharePoint and how to use the Secure Store Service so that excel files in SP can automatically refresh data using one single account (good for scenarios where you don’t validate users and when non-windows users connect to SP).
43.  Click New Keys:
44.  Fill in the properties:
45.  Next
46.  Fill User domain
47.  set credentials for the target application id:
48.  configure the credentials:
49.  Now, go back to the Excel Service Application and under “global settings” add the unattended account:
50.  Let us now update the excel file to use the shared connection file:
51.  Browse to the data connection library in SP:
52.  Save the updated workbook in SharePoint:
53.  Let us update the data in SQL >> Current value:
54.  New Value changed:
55.  Check data again
56.  The excel file has this value before the update:
57.  And after update choose to update all connection from the data menu:
58.  OK
59.  The Updated data:


Post a Comment