Supporters of End User
Web

Create a Pivot Table from a SharePoint List and Display it in SharePoint

Item is currently unrated. Press SHIFT+ENTER to rate this item.1 star selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+ESCAPE to leave rating submit mode.2 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.3 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.4 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.5 stars selected. Press SHIFT+ENTER to submit. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.
Categories: 

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.

  1. 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.
  2. 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:
  3. Pivot Table
  4. 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.
  5. From the actions button select to edit the list in datasheet as seen below:
  6. Pivot Table
  7. 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:
  8. Pivot Table
  9. From the tool panel, click the Create Excel Pivot Table Report link:
  10. Pivot Table
  11. You may be prompted with a warning that looks similar to this, click the Enable button:
  12. Pivot Table
  13. 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:
  14. Pivot Table
  15. 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:
  16. Pivot Table
  17. 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:
  18. Pivot Table
  19. 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:
  20. Pivot Table
  21. 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:
  22. Pivot Table
  23. 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:
  24. Pivot Table
  25. 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:
  26. Pivot Table
  27. Browse to the location where you saved the file and select it then press Ok:
  28. Pivot Table
  29. 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.
  30. Publish the page if necessary and you should now see your pivot table:
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. 

John RossAuthor: 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.

Comments

Frank Familiari

Create a Pivot Table from a SharePoint List and Display it in SharePoint

Thanks, this is a good idea.

We are getting Data refresh failed error when trrying to update.

Posted 03-Apr-2009 by Frank Familiari
Greg Maass

Create a Pivot Table from a SharePoint List and Display it in SharePoint

Yes, data refresh does not seem to work against a sharepoint list. So if the only option is to have a static pivot table/chart that can't be refreshed unless the owner opens it, refreshes it, and saves it, this doesn't seem very useful.

Posted 03-Apr-2009 by Greg Maass
John Ross

Create a Pivot Table from a SharePoint List and Display it in SharePoint

Your options for refreshing from a SP List are very limited. However, there are other options for refreshing with other data connection types. That would make for a good followup post to this article.

-John

Posted 03-Apr-2009 by John Ross
Greg Maass

Create a Pivot Table from a SharePoint List and Display it in SharePoint

It is frustrating, because it is pretty straightforward to use an odc data connection file against sql server data and have it refresh every time the excel webpart is opened. Seems reasonable to expect this same functionality against a sharepoint list (which is essentially sql server data anyways...)

Posted 03-Apr-2009 by Greg Maass
fabiods

Create a Pivot Table from a SharePoint List and Display it in SharePoint

Great !
NOW PLEASE SHOW THE DIFFICULT PART;

a EDITABLE / UPDATABLE PIVOT TABLE !

It is wuite annoying that microsoft is reluctant to give this functionally, even killing the OWC11 PIVOT TABLE to be editable from the web.

Thanks any way,

Fabio

Posted 08-Apr-2009 by fabiods
Frank Familiari

Create a Pivot Table from a SharePoint List and Display it in SharePoint

I still think this is a great idea and method.

Thanks John!

Posted 22-Apr-2009 by Frank Familiari
John

Create a Pivot Table from a SharePoint List and Display it in SharePoint

I have a question for you 17 step, the screenshot only show the pivot table, can you show the pivot table field list on excel service? I can't access my pivot table field list on it.

Thanks!

Posted 17-Dec-2009 by John
Oswald

Create a Pivot Table from a SharePoint List and Display it in SharePoint

It doesn't work for me. Getting the following error message.

The workbook that you selected cannot be loaded because it contains the following features that are not supported by Excel Services:

SharePoint lists

Contact the workbook author.

Does anyone know any workaround to this issue?

Posted 12-Jan-2010 by Oswald
ceejaybee

Create a Pivot Table from a SharePoint List and Display it in SharePoint

Does anyone know how to do this with Pivot tables created in Access? I don't see the comparable options in Access for step 10, i.e. there are no Access Services.

btw, the reason I'm using Access is that Excel pivot tables display only numeric data, I am displaying text.

Thanks!

Posted 16-Feb-2010 by ceejaybee
gargi

Create a Pivot Table from a SharePoint List and Display it in SharePoint

Hi,

I am trying to create a single list from a 5 other lists which have similar data content. I need to use this final combined list in order to create reports on the same sharepoint site. Does anyone know a quick way to do this?

Thank you,

Gargi

Posted 12-May-2010 by gargi
Shakti Srivastava

Create a Pivot Table from a SharePoint List and Display it in SharePoint

Hi

It didn’t work for me. I got the following error message.:
The workbook that you selected cannot be loaded because it contains the following features that are not supported by Excel Services:

SharePoint lists

Contact the workbook author.

Can you please tell what can be the issue...?And i read in this MSDN blog...http://social.msdn.microsoft.com/Forums/en-US/sharepointexcel/thread/d3873d30-ef96-4bd2-857c-7dc518d3df5d .....that Excel Services do not support loading data from SharePoint List ...then how is that yours is working...I am confused...can you plz clarify...?

-Shakti

Posted 16-Aug-2010 by Shakti Srivastava
chintan

Get refesh

check my blog for perfect solution http://sharepointside.blogspot.com/2011/12/refresh-pivot-table-or-chart-with-excel.html

Posted 07-Dec-2011 by chintan
i:0e.t|liveid|000340019bd65452@live.com

Where can I find the referenced article?

Near the top of this article it is written:
"[...] If you haven’t already setup a trusted file location take a look at this post for more information"
 
However, the link no longer points to a post. Does someone have an updated URL to the post in question?
 
Is anyone here daily building pivot charts on other kinds of data? We are trying to build pivot charts using the Office PivotChart web part, but need to display data from 2 or more tables joined together.
 
Can you recommend techniques to do this?

Natasha Felshman

Referenced article

Hi,
 
I've updated the link for the referenced article.
 
Thanks,
Natasha

Posted 27-Jan-2012 by Natasha Felshman
lcadman

2010

For 2010 SharePoint, it is the same story with not being able to fresh if the data source was from a list, correct? Thanks

Posted 15-Feb-2012 by lcadman
qnoyz

Datasheet tool panel could not be expanded ?

Hi... John, I am using MOSS 2007 Enterprise, and I have installed msoffice 2010 within my PC, so far i have no problem to follow ur steps until step 5, Once the list is shown as a datasheet I saw a small arrow at the right as seen the image below. But when I Clicked on the arrow of the tool panel, it could not be expanded, WHY ??? Something wrong with my data or have i missed another configurations ?

Posted 30-Mar-2012 by qnoyz
James

From the tool panel, click the Create Excel Pivot Table Report link:

I am missing the 'Create Excel Pivot Table Report' link in the Tool panel. Any idea why?

Posted 11-Feb-2013 by James
learn.sharepoint.com

My blog will show you how to use live sharepoint data with excel services

My blog will show you how to use live sharepoint data with excel services http://learn.sharepoint.com/Blog

Posted 26-Aug-2013 by learn.sharepoint.com

Notify me of comments to this article

E-mail:
   

Add Comment

Title:

 
Comment:
Email:

   


Name:

 
Url: