I have a pivot that's linked to an access db.  It is filtered by label in the first column.  The problem is when I refresh the data, it hides all of the rows but the header.  If I go and redo the label filter, the data reappears.  Not sure what to do to fix that.  Tried VBA, but it slowed down what was generally a fast workbook.

Is the pivot directly linked to the Access DB or do you have a "staging table" inside the excel file?

Where exactly is the filter applied ("basic table" or in the pivot)?

The pivot is directly linked.  The filter is a label filter applied to the individual pivot table.  When I reopen the file, none of the data shows up in the pivot, but if I reapply the filter, it shows.  It's a pain because I have several pivots from the same data source, and they are all acting the same.  I had the same problem on another workbook that I had made from linking to an Access DB, and it behaved the same way.  I ended up using VBA to reapply the filter each time, but that just seems archaic.

That's true and VBA doesn't seem to be the right way.

What happens if you refresh the Pivots (data->refresh)? Do you still have to re-apply the filters? Do you auto-update the pivots upon opening the workbook (there is a tickbox in the pivot settings for that)?

Even if I refresh the pivots, I still have to reapply the filters.  The thing is, it only happens when I have a label filter.  If I have a value filter, I don't come across this issue.

Strange.

"Filtered data displays only the subset of data that meet the criteria that you specify and hides data that you do not want displayed. Unlike filtering a cell range or table, you do not need to reapply a filter. Filters are automatically reapplied every time the PivotTable is refreshed or updated."

https://support.office.com/en-au/article/Filter-data-in-a-PivotTable-or-PivotChart-report-ca3ae673-95ac-46ab-b31a-7e3c5265c422

Do you mind posting the label filter?

Does it only not work for Access-DB sources? Try to copy the data into excel and make the label filter? Does the problem re-appear or does it only happen between Excel<->Access?

• Edited by Bjoern25 Friday, February 27, 2015 9:03 AM
• Proposed as answer by George.Zhao CHNMicrosoft contingent staff, Moderator Monday, March 02, 2015 6:01 AM
• Unproposed as answer by George.Zhao CHNMicrosoft contingent staff, Moderator Monday, March 02, 2015 6:01 AM

Strange.

"Filtered data displays only the subset of data that meet the criteria that you specify and hides data that you do not want displayed. Unlike filtering a cell range or table, you do not need to reapply a filter. Filters are automatically reapplied every time the PivotTable is refreshed or updated."

https://support.office.com/en-au/article/Filter-data-in-a-PivotTable-or-PivotChart-report-ca3ae673-95ac-46ab-b31a-7e3c5265c422

Do you mind posting the label filter?

Does it only not work for Access-DB sources? Try to copy the data into excel and make the label filter? Does the problem re-appear or does it only happen between Excel<->Access?

• Edited by Bjoern25 Friday, February 27, 2015 9:03 AM

Here it is.  It does it even if I have the source in the same workbook.  I've tried recreating the pivot and source table from scratch, and get the same result.