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).

Answer 2

The answer is to create a separate table for the state dimension:

This should be connected to your StateIDHistory and the state in your filter should then be from the state table. not only will this show all options regardless of whether they have data but it's also good practice for more complicated models.

Answer 3

One quick comment to add to the excellent answer by PsyMann above; you must have the PivotTable set to retain data for deleted items or the option to "Show items with no data" will be greyed out.

Do this by right-clicking on the table, picking PivotTable Options, Data, and ensuring that "Retain items deleted from the data source" is set to either "Automatic" or "Maximum"

The frustrating thing is that you can't make this field dependent so this then means that all fields will retain obsolete data, and these obsolete items will now show up in Data Slicers etc.

Answer 4

I faced the same issue in a exception report I sent.. it worked fine where filter returned some data, where there was no data the filter got removed and it showed all data.

I moved the filter column from Pivot Filters section to Rows section and then used the Label Filter option for the column to specify the filter and then I made the filter column hidden. This worked correctly.

There are limitations to this approach as you cannot select multiple values to filter, Label Filter has limited options. My filter was based on a yes/no type column I had added in base data to identify exception records so this approach worked fine for me.