none
Pivot Table in Excel Does Not Update in EWA webpart with User Define Function (UDF)

    Question

  • Hi Folks,

    My Requirement: Updating PivotTable/Chart whenever Sharepoint List data get change.

    My Approach: Applying UDF's in Excel Workbook.

    Issue: I am able to get records from my List in my workbook when i run UDF in workbook. Then I Inserted PivotTable based on data fetched from List. I published workbook in trusted location. I enable UDF in trusted location. I can also see PivotTable in my Excel Web Access web part. But, not getting updates in PivotTable/Chart when List data change.

    Question: I am confuse that who will run UDF when my workbook seats in trusted location? UDF will run automatically and fetch the data from my Sharepoint List. If so, then my PivotTable should get update?

    Guys, Please advice. Appreciate your help.

    - Chintan

     



    • Edited by Mike Walsh FIN Tuesday, May 10, 2011 1:33 PM Do not push for support. If you are in such a hurry, ring MS Customer Support Services and pay for support.
    Tuesday, May 10, 2011 12:44 PM

All replies

  • Finally, I was able to get the updates in workbook (in browser) :)

    this time i tried with publishing whole workbook instead of items (PivotTAble/Chart) in workbook.

    To my previous thread question: I got to know that UDF will run automatically once published.

     

    However, there are couple of issues:

    • 1.       PivotTable (generated based on records fetched by UDF) can’t calculate data correctly. Example: It can’t sum the data (Screen-1 here) based on data fetched by UDF (Screen2 here) though column is of Numeric type.
    • 2.       UDF works with data range and display Sharepoint List data only in that range. It seems user manually need to select data range every time whenever Sharepoint List grow. See screen-2 where I have selected row 1-10. Only within this range data get fetch from Sharepoint List. If Sharepoint List has more rows then those rows will get trim in workbook.
    • 3.       We are getting updates, but sometime updates happen in few seconds and sometime in 5min. I am in progress searching factor affecting this duration.

     

    Appreciate, if any one can help me to SUM up numeric column.

    Thanks,

    Chintan



    Thursday, May 12, 2011 6:47 AM
  • Hi,

    If you don't mind you the custom code to refresh the data connection, please have a look in to the code posted here

    http://social.technet.microsoft.com/Forums/en-US/sharepointexcel/thread/ccb1e50b-2bc5-4969-b6a1-8de1f3ae9c78

    Let me know


    Best Regards, G Vijai Kumar | My Sharepoint Blog
    Wednesday, June 08, 2011 9:35 AM
  • Thanks Vijay, I got solution over here which i posted to my blog http://sharepointside.blogspot.com/2011/12/refresh-pivot-table-or-chart-with-excel.html
    Thanks, Chintan
    Wednesday, December 07, 2011 10:22 AM