Pivot Tables are a powerful capability of Excel, however MOSS now provides the capability to create a pivot table from data in a SharePoint list. This example will show you how to use a SharePoint list to create a pivot table and then display it back in SharePoint using the Excel Web Access web part.
- The first step is to make sure that you’ve specified that you have a trusted file location specified in your SSP that allows remote connections. If you haven’t already setup a trusted file location take a look at this post for more information.
- You’ll need to verify that your trusted file location is configured to allow external data connections. Open up the trusted file location you’ve created and edit the settings and confirm that the following value is set:
- The next step is to create a list in SharePoint that contains some data to be used for the pivot table. In this case, I’ve created a custom list with 3 site columns: City (text), Units Sold (Number), Profit (Currency). Then fill in the list with some sample data.
- From the actions button select to edit the list in datasheet as seen below:
- Once the list is shown as a datasheet you should see a small arrow at the right as seen the image below. Click on the arrow to expand the tool panel:
- From the tool panel, click the Create Excel Pivot Table Report link:
- You may be prompted with a warning that looks similar to this, click the Enable button:
- The pivot table will open in Excel, but you’ll need to select the fields at the right that you want to include in the pivot table. In this example we’ve chosen our 3 fields from the SharePoint list:
- It is also helpful to give the pivot table a meaningful name. We’ll be using this name later when we display the information with the Excel Web Access web part:
- The next step is to publish the pivot table into a document library in SharePoint. To do this, click on the Office icon in the upper left corner and select Publish > Excel Services:
- Choose a document library where you want to save your pivot table. You’ll need to browse to the appropriate location by clicking on My Network Places and then browsing to the server and then to the folder where you want to save the pivot table. In this case I’m saving it to a document library called Sales Figures. Be sure to pick an intuitive name for the file, but before clicking save press the Excel Services Options button:
- A new window will open, and for this example we’ve chose to show Items in the Workbook and have selected Q1Sales, which is the name for our pivot table. If you happened to have more than one pivot table this dialog would allow you to select only the items that you wanted to show:
- Now the table has been saved to SharePoint. The final stage of the process is to show the pivot table in a web part. For this step you could create a new web part page, or simply use any page that has a web part zone. Place the page into edit mode and add the Excel Web Access web part:
- After the web part has been added to the page, modify the web part and under the Workbook Display section, press the “…” button to browse for the file you just saved to SharePoint:
- Browse to the location where you saved the file and select it then press Ok:
- In the Named Item field for the web part specify the name you gave to the pivot table in step 9. In our case, it was Q1Sales. Once you’ve completed this, press the Ok button at the bottom of the web part tool pane.
- Publish the page if necessary and you should now see your pivot table:
It should be noted that this data will need to be refreshed manually which can be done by clicking on the update button in the image below.
Author: John Ross
John Ross is a trainer and consultant for SharePoint911. He is the coauthor of SPG301: SharePoint Planning and Governance with the Ted Pattison Group. John is also a coauthor on the SharePoint book MOSS Explained: An Information Worker’s Deep Dive into Microsoft Office SharePoint Server and was a contributing author to the book Professional SharePoint 2007 Design. John lives in Orlando, FL.
Be sure to check out our upcoming training for Business Users and Technical Leads June 1-4th in Cincinnati, OH.