Pivottable doesn't contain all the data auto of query

[RalfPeeters]

Hi all,

I made an Excelfile (xlsm) with several connections to a part of a database and set some filters in query. With the filtered query data I made some pivot tables. While validating the file I found out that some data which are available in query, aren't available in the pivot table. When I make a new Pivot using the same connection the data are there. So it seems to be a refresh or cache problem.

I've read some posts on this issue but can't solve the problem.

Made macro's to refresh all data on open, clearing the Pivot cache, clearing and setting pivotfilters.
Settings in the pivot table are set on "refresh data when opening the file" and the connection refresh is set on "Refresh data when opening the file", "Refresh this connection on Refresh All" and background refresh is disabled.

Anyone a suggestion how to solve this?

Greetz, Ralf

 

[GaryDrummSr]

Iv'e faced something similar, and found that my source data was not formatted correctly
Once I identified and remedied that. it worked fine.

Hope that helps

 

[RalfPeeters]

Iv'e faced something similar, and found that my source data was not formatted correctly
Once I identified and remedied that. it worked fine.

Hope that helps

Sadly not... Anyone else?

 

[Fazza]

Hello, Ralf

One thought, if there are any filters on the pivot table/s this could be a problem.

Next thought, is give the queries each a simple/basic (and not dynamic/volative) defined name. Use these names as the source for the pivot table/s. I've found this to be robust (and the range the names apply to in my experience always adjust automatically when the query refreshes). I'm assuming that the current pivot tables have not picked up all data rows (after query refresh): you can check if this is the case or not by looking at the source data of one of the pivot tables that doesn't have all the data it should.

HTH. Regards, Fazza

 

[GaryDrummSr]

Thanks Fazza, Some good tips I hadn't thought of.
Gary