Add an Excel Pivot Table and Pivot Chart to your SharePoint Site
Creating Pivot Tables and Pivot Charts are a powerful way to display line of business data on your site.
Prerequisites:
MOSS 2007 Enterprise Edition
Microsoft Office 2007 Enterprise Edition
Directions:
Create the Data Connection Library
1. From your Site, on the Site Action bar, select Create.
2. Within the Libraries section, select Data Connection Library.
3. From the New Data Connection page, provide a Name and Description and leaving the defaults as remaining settings.
Configure the Excel Services Trusted Data Connection Library
4. Proceed to Shared Services, and select Trusted Data Connection Libraries (in the Excel Services Settings section).
5. Select to Add Trusted Data Connection Library
6. Add the URL path to the Data Connection Library created earlier
Create a Document Library
7. From your Site, on the Site Action bar, select Create.
8. Within the Libraries section, select Document Library.
9. From the New Document Library page, provide a Name and Description and leaving the defaults as remaining settings.
Configure the Excel Services Trusted file location
10. Proceed to Shared Services, and select Trusted file location (in the Excel Services Settings section).
11. Select to Add Trusted File Location
12. From the Edit Trusted File Location page, update the following fields from their default settings:
Address Add the URL Path of the Document Library created in the previous section
Trust Children Set to Checked
Allow External Data Select Trusted Data Connection Libraries only option
Warn on Refresh Set Refresh Warning Enabled to Unchecked
Allow User-Defined Functions Set User-Defined Functions Allowed setting to Checked
Add Office Data Connection (ODC ) File to Data Connection Library
13. From the Data Connection Library created on your Site, select to Upload Document from the Action bar
14. Select to browse or enter the path to the desired Office Data Connection file which will define the data source to populate the Pivot Table and Pivot Chart
Create Excel Services Spreadsheet
15. Launch Microsoft Office Excel 2007 and create a new spreadsheet.
16. From the Data Tab, select Existing Connections from the Get External Data section of the Office Ribbon.
17. From the Existing Connections dialog, select the Browse for more… button.
18. Locate and supply the path to the Data Connection Library and the appropriate Office Data Connection file.
19. From the Import Data dialog, select the PivotTable Report option.
20. From the PivotTable Field List, select the desired fields and required for the PivotTable.
21. While the PivotTable is selected within Microsoft Excel, note the PivotTable Name within the PivotTable section on the Options Tab within the Office Ribbon, this is be required later within SharePoint.
22. While the PivotTable is selected within Microsoft Excel, select the PivotChart from the Tools section within the Office Ribbon
23. From the Insert Chart dialog, select the desired Chart Type to be added.
24. While the PivotChart is selected within Microsoft Excel, note the PivotChart Name within the Properties section on the Layout Tab within the Office Ribbon, this is be required later within SharePoint.
Publish Excel Services Spreadsheet
25. Select from the main menu, select the Office Button, then the Publish selection.
26. Locate or enter the path of the Document Library created earlier and provide a meaningful name for the Excel document.
Add the Excel Web Access Web Part to the page for use with a Pivot Table
27. From the Site Action bar, select Edit Page.
28. From any Web Part Zone, select to Add a Web Part.
29. From the Add Web Parts dialog, select to add the Excel Web Access web part (in the Business Data section).
30. From the web part’s Edit menu, select the Modify Shared Web Part selection.
31. From the Web Part settings pane, modify the following settings for the Excel Web Access Web Part:
Workbook Select the path of the Workbook created earlier
Named Item Enter the name of the Named Item noted in Step 21
Add the Excel Web Access Web Part to the page for use with a Pivot Chart
32. From the Site Action bar, select Edit Page.
33. From any Web Part Zone, select to Add a Web Part.
34. From the Add Web Parts dialog, select to add the Excel Web Access web part (in the Business Data section).
35. From the web part’s Edit menu, select the Modify Shared Web Part selection.
36. From the Web Part settings pane, modify the following settings for the Excel Web Access Web Part
Workbook Select the path of the Workbook created earlier
Named Item Enter the name of the Named Item noted in Step 24
37. From here, it is typical to make changes to the either the Excel Web Access Web Part or the Excel spreadsheet for the best aesthetic results depending on needs and requirements.
Creating Pivot Tables and Pivot Charts are a powerful way to display line of business data on your site.
Prerequisites:
MOSS 2007 Enterprise Edition
Microsoft Office 2007 Enterprise Edition
Directions:
Create the Data Connection Library
1. From your Site, on the Site Action bar, select Create.
2. Within the Libraries section, select Data Connection Library.
3. From the New Data Connection page, provide a Name and Description and leaving the defaults as remaining settings.
Configure the Excel Services Trusted Data Connection Library
4. Proceed to Shared Services, and select Trusted Data Connection Libraries (in the Excel Services Settings section).
5. Select to Add Trusted Data Connection Library
6. Add the URL path to the Data Connection Library created earlier
Create a Document Library
7. From your Site, on the Site Action bar, select Create.
8. Within the Libraries section, select Document Library.
9. From the New Document Library page, provide a Name and Description and leaving the defaults as remaining settings.
Configure the Excel Services Trusted file location
10. Proceed to Shared Services, and select Trusted file location (in the Excel Services Settings section).
11. Select to Add Trusted File Location
12. From the Edit Trusted File Location page, update the following fields from their default settings:
Address Add the URL Path of the Document Library created in the previous section
Trust Children Set to Checked
Allow External Data Select Trusted Data Connection Libraries only option
Warn on Refresh Set Refresh Warning Enabled to Unchecked
Allow User-Defined Functions Set User-Defined Functions Allowed setting to Checked
Add Office Data Connection (ODC ) File to Data Connection Library
13. From the Data Connection Library created on your Site, select to Upload Document from the Action bar
14. Select to browse or enter the path to the desired Office Data Connection file which will define the data source to populate the Pivot Table and Pivot Chart
Create Excel Services Spreadsheet
15. Launch Microsoft Office Excel 2007 and create a new spreadsheet.
16. From the Data Tab, select Existing Connections from the Get External Data section of the Office Ribbon.
17. From the Existing Connections dialog, select the Browse for more… button.
18. Locate and supply the path to the Data Connection Library and the appropriate Office Data Connection file.
19. From the Import Data dialog, select the PivotTable Report option.
20. From the PivotTable Field List, select the desired fields and required for the PivotTable.
21. While the PivotTable is selected within Microsoft Excel, note the PivotTable Name within the PivotTable section on the Options Tab within the Office Ribbon, this is be required later within SharePoint.
22. While the PivotTable is selected within Microsoft Excel, select the PivotChart from the Tools section within the Office Ribbon
23. From the Insert Chart dialog, select the desired Chart Type to be added.
24. While the PivotChart is selected within Microsoft Excel, note the PivotChart Name within the Properties section on the Layout Tab within the Office Ribbon, this is be required later within SharePoint.
Publish Excel Services Spreadsheet
25. Select from the main menu, select the Office Button, then the Publish selection.
26. Locate or enter the path of the Document Library created earlier and provide a meaningful name for the Excel document.
Add the Excel Web Access Web Part to the page for use with a Pivot Table
27. From the Site Action bar, select Edit Page.
28. From any Web Part Zone, select to Add a Web Part.
29. From the Add Web Parts dialog, select to add the Excel Web Access web part (in the Business Data section).
30. From the web part’s Edit menu, select the Modify Shared Web Part selection.
31. From the Web Part settings pane, modify the following settings for the Excel Web Access Web Part:
Workbook Select the path of the Workbook created earlier
Named Item Enter the name of the Named Item noted in Step 21
Add the Excel Web Access Web Part to the page for use with a Pivot Chart
32. From the Site Action bar, select Edit Page.
33. From any Web Part Zone, select to Add a Web Part.
34. From the Add Web Parts dialog, select to add the Excel Web Access web part (in the Business Data section).
35. From the web part’s Edit menu, select the Modify Shared Web Part selection.
36. From the Web Part settings pane, modify the following settings for the Excel Web Access Web Part
Workbook Select the path of the Workbook created earlier
Named Item Enter the name of the Named Item noted in Step 24
37. From here, it is typical to make changes to the either the Excel Web Access Web Part or the Excel spreadsheet for the best aesthetic results depending on needs and requirements.
