Hello,
I've been searching for a solution to this issue for the past year, but haven't quite found one. I have a local Pivot table with pivot charts in Excel 2013 that reference the columns of a large (A:CD, 140,000+ rows), sensitive data set within an Excel workbook on our corporate SharePoint site. Rows are added to the source file daily by a support team so I am not supposed to modify it. When I first created my local pivot "dashboard" I quickly observed that refreshing the pivot either through toggling filters or manually refreshing would cause the local file to lag in "reading data" mode for hours until I would just give up. However I found an easy work around: if I simply had the source file open, I could do a manual refresh with success in less than 2 seconds, then toggle dashboard filters. Toggling filters without first manually refreshing the pivot with the source file open actually caused the local file to crash within a few seconds (with reading data progress bar reaching close to 100%).
This open source file method had become my standard practice up until I had to transfer my hard drive to another laptop yesterday. My local file appears to be in the same place as it was before but now refreshing with the source file open no longer works - the reading data notification lags for at least 2 hours with minimal progress.
Understanding that my source file is not in an SQL server like I've seen in many other posts, I believe using a SharePoint Excel workbook may be at the root of this issue. But still, I have made a few observations based on previous posts I've seen...
-Even when the manual refresh was working with source file open, my local file never showed the source file in the data connections
-The "change data source" Pivot analysis option calls my source file like a website ("https://ishare...). The file location is still valid
-I've tried reselecting the data source range for my pivot table and this seems to work, but I have slicer report connections that need to be disabled first which is very tedious.
-I have given my local file to others with the same source file access and they've never been able to get the pivots to refresh even with the source file open.
Any insights related to this issue ate greatly appreciated.
I've been searching for a solution to this issue for the past year, but haven't quite found one. I have a local Pivot table with pivot charts in Excel 2013 that reference the columns of a large (A:CD, 140,000+ rows), sensitive data set within an Excel workbook on our corporate SharePoint site. Rows are added to the source file daily by a support team so I am not supposed to modify it. When I first created my local pivot "dashboard" I quickly observed that refreshing the pivot either through toggling filters or manually refreshing would cause the local file to lag in "reading data" mode for hours until I would just give up. However I found an easy work around: if I simply had the source file open, I could do a manual refresh with success in less than 2 seconds, then toggle dashboard filters. Toggling filters without first manually refreshing the pivot with the source file open actually caused the local file to crash within a few seconds (with reading data progress bar reaching close to 100%).
This open source file method had become my standard practice up until I had to transfer my hard drive to another laptop yesterday. My local file appears to be in the same place as it was before but now refreshing with the source file open no longer works - the reading data notification lags for at least 2 hours with minimal progress.
Understanding that my source file is not in an SQL server like I've seen in many other posts, I believe using a SharePoint Excel workbook may be at the root of this issue. But still, I have made a few observations based on previous posts I've seen...
-Even when the manual refresh was working with source file open, my local file never showed the source file in the data connections
-The "change data source" Pivot analysis option calls my source file like a website ("https://ishare...). The file location is still valid
-I've tried reselecting the data source range for my pivot table and this seems to work, but I have slicer report connections that need to be disabled first which is very tedious.
-I have given my local file to others with the same source file access and they've never been able to get the pivots to refresh even with the source file open.
Any insights related to this issue ate greatly appreciated.
