Sign in
 
HomeOnline20132010Other VersionsRelated ProductsLibraryForumsGallery
TechCenter >
> 
> 
Excel 2010 Pivot Table from SharePoint List

Proposed Answer Excel 2010 Pivot Table from SharePoint List

  • Tuesday, February 22, 2011 3:45 PM
     
     
    Sign In to Vote
    1

    I have a pivot table that I created from a Sharepoint list that works fine in Excel 2007.  However, when I create the same pivot table for Excel 2010, I am finding that any calculated fields from the Sharepoint list (simply Month or Year calculations) are showing up as blank.  In other words the Month field is available in the pivot table but all of the data is blank for that field.  This works fine for Excel 2007.  I am running Sharepoint 2007.  Any ideas would be appreciated.

     

    Tim

     

All Replies

  • Tuesday, February 22, 2011 4:34 PM
     
     
    Sign In to Vote
    0

    I did some more testing and found more information.

    The calculation I was using in the calculated column was =TEXT([Date Quoted],"mmmmm") in order to display the month as a word (ie: January, February, etc) in the list on Sharepoint.  This method shows up as a blank in Excel 2010, but correctly in Excel 2007.  I changed the calculation to =Month([Date Quoted]).  Now it displays as a number (1,2,etc) in the Sharepoint list, and shows up correctly in both Excel 2010 and Excel 2007.

    Now my question is simply how can I get the Month Name to display in the sharepoint list and in Excel 2010.  It seems that the Text method does not work when trying to display the data in an Excel 2010 pivot table.  BTW, the fields come thru fine if I do a direct Export to Excel from the sharepoint list.

    Any help would be appreciated.

    Tim

     

     
  • Sunday, May 15, 2011 5:26 AM
     
     
    Sign In to Vote
    0

    Hi Tim,

    Did you figure out a solution for this by any chance?

    Thanks,

    Jon

     
  • Thursday, August 04, 2011 4:11 AM
     
     
    Sign In to Vote
    0

    Same problem, any help would be appreciated.

     
  • Wednesday, March 28, 2012 11:38 AM
     
     
    Sign In to Vote
    0

    Same issue with exactly the same kind of calculated fields.

    If in Excel you expose the data thanks to a table object (Query Table) all data are properly retrieved.

    The issue is only with Pivot object.

     
  • Tuesday, June 19, 2012 4:59 PM
     
     Proposed Answer
    Sign In to Vote
    0

    We were just facing the same issue, I saw your post and found a work around.  If you export it as a table and then use the functionality in excel to convert to a pivot table it will work.  For example:

    1.) Go to the list
    2.) Select datasheet view
    3.) Export to excel
    4.) Select to open the owssvr.iqy
    5.) Select to enable the source
    6.) Select to view data as a table
    7.) Select OK
    8.) Under table tools design tab, select summarize with pivot table
    9.) Select your fields that you want in the pivot table and the month will still appear

     
  • Friday, June 22, 2012 5:55 PM
     
     
    Sign In to Vote
    0

    Christin, you have just made my day!  I've struggled for a week to get this functionality in a Pivot Table from Sharepoint data connection.  It worked like a charm for me.

    I needed a field that would adjust a [Date Closed] column, by 10 months, in order to align quarters with our Fiscal year.  When I used the Date/Time format of the field, in the Pivot table, if I tried to group the dates, Excel gave an error "Cannot group that selection".  The workaround.  I also created a number and a text field formula with the same information, not an exact date, but instead a grouping, and I still received this same error, or I got nothing at all.  When I used your workaround, all of the columns, calculated, grouped, and filtered without any errors.

    Thank you again!