Keeping filters in a pivot table in absence of data

Question

Imagine I have a quality control in a factory retrieving the state of each product StateId 1 - Good 2 - Acceptable 3 - Subproduct 4 - waste

My table 'StateIdHistory' gives me the state of each ProductId at each time. I am creating a pivot table in which I want to filter out the StateId=3 in a specific datarange. so I filter my report selecting "3".

Imagine I want to use this same model to other database in which there isn't any StateId=3. My data model will be destroyed. Can I somehow fix the filter to be equal to 3 in spite of having or not data? VBA? If 3 still does not exist it would retrieve me 0 or blank cells...

Thanks in advance!!

Answer 1

If I understand correctly, you have four options for StateId:

1 - Good
2 - Acceptable
3 - Subproduct
4 - waste

And then you've made a pivot table, and put StateId into the Report Filter section, and filtered it on only "3 - Subproduct"

So at the top of your pivot table in the filtering section, it shows:

| StateId | 3 - Subproduct |

If you refresh your pivot table, it will keep filtering on 3-Subproduct as you've asked it to... ...until your data has no entries for 3-subproduct. Then, instead of keeping filtering on it, it automatically changes to:

| StateId | (All) |

What you wanted (I think), was for it to stay filtering on 3-Subproduct, and just show nothing in the pivot table. But what it ever-so-helpfully does instead is reset the filter to (All) because there are no 3-Subproduct entries to show. Which then means later on, if you add some 3-Subproduct entries, and refresh the pivot table again, it stays filtering on (All), and doesn't remember you actually wanted 3-Subproduct.

If that's your problem then it's the same as the one I've had, and I've finally realised today that the answer is actually very simple:

• Right-click on the filter option and go to Field Settings
• Choose Layout & Print tab
• Tick the box called Show Items with no data

Then it remembers you've picked 3-subproduct even when there's no data for 3-subproduct in there, and just returns a blank pivot table instead of reverting to (All).