Sharepoint 2010 Excel Calculation Services ~ <a href="http://mstechsharing.blogspot.com/2013/04/home

Overview

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).