EXCEL SERVICE CONFIGURATIONS
Excel Service will help to use Excel Workbook (Client) functionality in a browser. However, Excel Service can’t leverage all Excel Workbook functionality on Browser. Below documents will point to all configurations settings to take advantage of Excel Service and how to get dynamic updates in Excel Web Access Web Part from Sharepoint List as soon as Sharepoint List updates. I am dealing with SharePoint 2007 (MOSS) and Excel Workbook 2007.
Note:
1. Keyword Pivot Chart/Table is abbreviated as Chart.
2. Excel Workbook is abbreviated as Workbook. Workbook indicates workbook in Trusted Location which consists of Pivot Chart/Table to be loaded on Web part.
3. DataBase is abbreviated as DB.
1. Keyword Pivot Chart/Table is abbreviated as Chart.
2. Excel Workbook is abbreviated as Workbook. Workbook indicates workbook in Trusted Location which consists of Pivot Chart/Table to be loaded on Web part.
3. DataBase is abbreviated as DB.
Below are couple of items where we need to make setting:
v SSP (Sharepoint Service Provider) on Central Admin.
1. Trusted File Location:
Create a location on your site by clicking on Add Trusted File Location. Trusted location where all Excel Workbook will be uploaded. Excel Service will only load Workbook which is in Trusted Location. As of now, I have created trusted location in my Tool site’s Shared Document.
Location Type: Set Location Type as Window Sharepoint Service, if you are uploading Workbook in Sharepoint. If your workbook is on network then you can choose UNC option. If your workbook is on Web then you can choose HTTP option.
Trust Children:
Check Trust Children box, if your workbook is uploaded to any sub-folder under URL given in Trusted Location, Excel Service will considers sub-folder as a Trusted Location to load workbook. In our case, we are uploading Workbook under Excel Service folder under Shared Document.
Session Management:
Keep “-1” value in Session Timeout, Short Session Timeout and Maximum Request Duration for never ending session (Below screen)
Workbook Properties:
Keep default values in Maximum Workbook Size and Maximum Chart Size.
Calculation Behavior:
Volatile Function Cache Lifetime: Set it to “-1”
Workbook Calculation Mode: Set it to Automatic
External Data:
Allow External Data:
Set it to Trusted data connection libraries and embedded as we will configured connection in Data Connection Libraries in Sharepoint and data connection will also seats inside Workbook. Choose None if your Workbook has no connection to connect to Data Source.
Warn On Refresh: Check it if you want warning on page (where Web part is loaded) before Excel Service update your Chart. As of now, un-check it.
Stopping open enabled: Check it.
Automatic refresh (periodic / on-open): Set it to “-1” if you don’t want automatic update. You need user to get the updates whenever they need. Keep Manual Refresh to “0”.
Maximum Concurrent Queries Per Session: Keep default value to “5”
User-defined functions allowed: Uncheck it, as we are not dealing with .Net assemblies.
1. Trusted Data Connection:
We need to create Trusted Data Connection in SSP which actually act as an interface to get the updates from Database. Excel Service will get the updates via this Trusted Data Connection only. Trusted Data Connection Libraries is merely a folder where data connection file is uploaded. This data connection file consist of every stuffs require by SQL Server Database like Connection String, Command Text, SQL View name etc.
Let’s create Trusted Data Connection Library on Sharepoint:
a. Navigate to your site.
b. Click on Document link on left panel.
c. Click on Create.
d. Click on Data Connection Library under Libraries section. Data Connection Library is specific meant for storing Data Connection files only.
e. Specify useful name. In my case, I have created Data Connection Library under Tool site and gave name as DCL.
f. Click Ok
Let’s configured Trusted Data Connection on SSP:
g. Click on SSP
h. Click on Trusted Data Connection Libraries.
i. Click on Add Trusted Data Connection Libraries to add trusted data connection.
j. Specify DCL path. Note: You can trim “Forms/AllItems.aspx” in the path. In my case, path will be “http://mysite/sites/Tool/DCL”
2. Trusted Data Provider:
Microsoft has already specified many standard Providers. We can either use it or create new one based on our requirement. In my case, I have used “SQLOLEDB.1” in my connection string. We will see it shortly.
v SQL View
1. Creating SQL View to query to Sharepoint List (to prepare Chart in Workbook):
§ Mapping field name in DB table with Sharepoint List column.
SQL Server is the backend Database for Sharepoint. So, every data in Sharepoint resides in SQL Server DB. However, SQL Server doesn’t maintain separate table for each Sharepoint List/Library/Site. But, it maintain single table called as “AllUserData” as a repository for Sharepoint data. So, we will target “AllUserData” table to generate SQL View.
Before jumping to AllUserData, We need to get the columns name in SQL Table mapped with Sharepoint List column. Yes, you read correctly. Each column in a list is mapped to inbuilt columns in Sharepoint. E.g. First Name column in a XXX list is mapped with "nvarchar1" column in DB and Last Name column in "nvarchar2" column & Amount in "float1" etc.
SQL DB maintain schema for all list. This Schema will maintain all information on mapping. We can get Schema from tp_Fields column in AllLists table. However, we are interested in Schema only for our required List. So, use tp_Titles column in AllLists table. Use below query
SQL DB maintain schema for all list. This Schema will maintain all information on mapping. We can get Schema from tp_Fields column in AllLists table. However, we are interested in Schema only for our required List. So, use tp_Titles column in AllLists table. Use below query
Select tp_Titles, tp_Fields From AllLists
where tp_Titles = 'XXX'
Note: If you don't know your list name then you can use Wildcards e.g. tp_Titles like '%XXX%'.
where tp_Titles = 'XXX'
Note: If you don't know your list name then you can use Wildcards e.g. tp_Titles like '%XXX%'.
Search your list name from tp_Titles and copy/paste data from Schema column to text file. Now, search for your Sharepoint List column name in text file and get the mapped column name in Database.
e.g.
<Field Type="DateTime" DisplayName="Date Identified" Required="TRUE" format="DateOnly" ID="{ec34b242-f0fd-448e-8507-08e387230da4}" SourceID="{2e4d8f89-a5bb-480d-9047-0d7a02749e07}" StaticName="Date_x0020_Identified" Name="Date_x0020_Identified" ColName="datetime3" RowOrdinal="0" CalType="0" Version="1"><Default>[today]</Default></Field>
Every column starts/ends in Field tag. So, search your column name with CTRL-F and get the mapped name. So, in above case, I am interested in “Date Identified” columns name. So, it is mapped in ColName field. So, column name is mapped in datetime3 in Database. Database store the value in same column type based on column type in Sharepoint List. In our case “Date Identified” is a datetime type, so it is mapped under datetime data type in Database. While, “Single Line of Text” column type will store data in nvarcharN column in Database. N could be integer value upto 64.
In our case, we will create below view.
§ Creating SQL View:
select nvarchar40 as "Column1", nvarchar30 as "Column2"
from AllUserData
where tp_DirName='sites/XXX' and tp_DeleteTransactionId = 0x & Is_current=1
Note: tp_DeleteTransactionId indicate removing deleted records from your result set. Because, sharepoint also maintain Recycle bin's data in "AlluserData". While, Is_Current indicates rows with current version.
Give senseful name to your view and save it.Note: tp_DeleteTransactionId indicate removing deleted records from your result set. Because, sharepoint also maintain Recycle bin's data in "AlluserData". While, Is_Current indicates rows with current version.
v Data Connection in Excel Workbook
We need to create connection from workbook to Database to fetch Sharepoint List column value. So, we will target SQL View (created above) for the connection.
Follow below steps to create and configure Data Connection:
1. Click on Menu Data -> From Other Sources -> From SQL Server.
2. Provide your Database Server name. In my case, its “162.70.211.11”.
3. Click on option: Use the following Username and Password.
4. Click Next button.
5. Select your Database name in “Select the database that contain the data you want” dropdown.
6. Select your SQL View (which we created above)
7. Click Next.
8. Give your Filename.
9. Click on “Save password in file” checkbox.
10. Specify your friendly name. In my case, both Filename and Friendly name were same.
11. Check “Always attempt to use this file to refresh the data”.
12. Important: Click on “Authentication Settings…” button. Choose None as we are using SQL Authentication to get the data. Please note, Clicking on None is important, as we are connection to Database via SQL Authentication and not by Windows or SSO authentication. So, select None.
13. Click Finish to complete data connection configuration.
14. You will receive below Import Data dialog box.
15. Click on Properties button to Export this settings to Sharepoint Data Connection Library. This data connection library we can also reuse.
16. Check Enable Background Refresh check box in Usage tab.
17. Enter “1” minute in Refresh Every…Minutes textbox. 18. Your settings should look like below screen:
21. You should get complete connection string in Connection String text box which include Username, Password, Provider etc.
22. Check on Save Password check box.
23. Command Type should be Table.
24. You should get SQL View name (which we created in Database) in Command Text.
27. That’s it. You are done.
28. Now, you can create your Chart based on your requirement.
29. Click on Existing Connections in Data menu in your Excel Workbook.
30. Click on Connection name which we created above.
31. Click on PivotChart and Pivot Table Report option in Import Data dialog box.
32. Click Ok.
33. After preparing Pivot Chart and Table, you need to publish Workbook onto Sharepoint Trusted Location.
34. Click on Workbook Ribbon (on top-left corner) -> Publish.
35. In Save As dialog box, click on Excel Service Options button.
36. Note: Open in Excel Service check box should be checked, so after publish your Pivot Table and Chart is display in a browser. This is useful feature by Excel, as we can get errors immediately (if any) in browser, rather than loading the Workbook in Web Part and then getting the result.
37. In Show tab, click on Items in the Workbook.
38. Check Pivot Chart and Table you want to publish in Web part.
39. Click Ok
40. Specify URL which point to Trusted Location on your site. In my case, path was: “http://..../sites/Tool/SharedDocuments/Excel Service/”
41. Specify Workbook name and Click Save button to publish the workbook.
19. Click on Definition tab.
20. Check “Always use connection file” checkbox.21. You should get complete connection string in Connection String text box which include Username, Password, Provider etc.
22. Check on Save Password check box.
23. Command Type should be Table.
24. You should get SQL View name (which we created in Database) in Command Text.
25. Now, click on Export Connection File to export this setting in Data Connection Library.
26. Your Connection Properties dialog box should look like below:27. That’s it. You are done.
28. Now, you can create your Chart based on your requirement.
29. Click on Existing Connections in Data menu in your Excel Workbook.
30. Click on Connection name which we created above.
31. Click on PivotChart and Pivot Table Report option in Import Data dialog box.
32. Click Ok.
33. After preparing Pivot Chart and Table, you need to publish Workbook onto Sharepoint Trusted Location.
34. Click on Workbook Ribbon (on top-left corner) -> Publish.
35. In Save As dialog box, click on Excel Service Options button.
36. Note: Open in Excel Service check box should be checked, so after publish your Pivot Table and Chart is display in a browser. This is useful feature by Excel, as we can get errors immediately (if any) in browser, rather than loading the Workbook in Web Part and then getting the result.
37. In Show tab, click on Items in the Workbook.
38. Check Pivot Chart and Table you want to publish in Web part.
39. Click Ok
40. Specify URL which point to Trusted Location on your site. In my case, path was: “http://..../sites/Tool/SharedDocuments/Excel Service/”
41. Specify Workbook name and Click Save button to publish the workbook.
Data Connection File in Sharepoint:
When we exported Data connection (in Step-15) from Workbook, it got copied in Sharepoint Data Connection Library (which we created in “Trusted Data Connection” in SSP section).
1. Move yourself to Data Connection Library page.
2. Click on Data Connection file drop down.
3. Click on Approve/reject item – See below screen.
Note: This step is very important, as change in web part won’t appears without approving this connection file.
Note: This step is very important, as change in web part won’t appears without approving this connection file.
4. Click on Approved/reject option.
5. Click Ok.
Excel Web Access Web Part
Add an Excel Web Access web part in your page to load Pivot Table and Chart. You can add this web part following below steps:
1. Navigate on the page where you want to load Excel Web Access Web Part.
2. Click on Site Actions -> Edit this page.
3. Click on Add Web Part in Add Web Parts dialog box.
4. Move to All Web Parts section.
5. Check Excel Web Access check box. See below screen.
6. Click on “Click here to open the tool pane” to open dialog box for web part configuration. Here, we will specify workbook location with few settings.
7. Specify workbook which you published in Workbook text box.
8. Specify Chart or Pivot Table name in Named item text box.
Note: Please specify precise name of Chart/Pivot Table from the work book or else Web Part may not display Chart.
Note: Please specify precise name of Chart/Pivot Table from the work book or else Web Part may not display Chart.
9. Click Ok.
10. Now, you should see your Pivot Chart/Table.
11. To get the refresh immediately, user need to click on Menu Update -> Refresh All Connections.
