Pivot Table and Chart



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.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download