Excel pivot table refresh says Invalid Pivot Table report

[RRinTetons]

I created an Excel workbook that queries a SQL Server database that returns around 500,000 to 600,000 rows. He then refreshes a Pivot Table references query data. You can successfully refresh the Pivot Table immediately after the refresh. When he saves the workbook and reopens it, though, attempting to work in the pivot table returns the error:

This Pivot Table report is invalid. Try refreshing the data.

He can dutifully refresh the data, but it doesn't make the problem go away. It seems that if he re-creates the Pivot Table from scratch, it works for one more work session, the problem recurs the
he opens the workbook. It's a fairly complex pivot table, so rebuilding it all the time isn't making him happy.

A few Google searches turn up information on some other Pivot table problems with field names, but not for the whole Pivot table report being invalid.

Any information on that error?

-
Richard Ray
Developer, Data Analyst
Jackson Hole, WY

 

[combo]

What excel version? When the pivot table was created, did you created data in data model?
If you use excel 2016+, have you considered power query connection (if you don't need data model, but even in this case you can have a series of query outputs in data model)?
Is it an issue described here?


combo

 

[RRinTetons]

Excel from current Office 365. No Power BI tools in use. Data not added to Data Model. I’ll take a look at the link you suggested.

-
Richard Ray
Developer, Data Analyst
Jackson Hole, WY