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.
- 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.
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.
If you don't mind you the custom code to refresh the data connection, please have a look in to the code posted here
Let me know
Best Regards, G Vijai Kumar | My Sharepoint Blog