J
John Brock
If I create a pivot table using a database which contains a particular
value, let's say "3", and then I change all the 3s to something
else, the pivot table remembers the missing value. I.e., if I
check the "Show items with no data" checkbox for that field, the
pivot table will contain a column called "3" even though there are
no 3s any more. What's more, that column will continue to appear
even after I refresh the pivot table. In fact, the only way I can
make that column go away permanently is by calling PivotItems("3").Delete()
from VBA. (Is there a way to do this manually?)
However if I try to add a PivotItem to the table using VBA I can
do it, but the added column disappears the minute I do a refresh.
The best I can figure is that adding a PivotItem to the table
doesn't touch the cache, while adding data to the database does,
even after that data has been deleted.
So is there any way I can tell the pivot cache (not the pivot
table!) to add a new item? Deleting the item from the table seems
to affect the cache, but there doesn't seem to be any obvious way
to add items.
(I suppose I could add rows containing the desired values to the
database, refresh the pivot table, and then remove the added rows,
but that seems really hackish, and I'd much rather do it directly).
value, let's say "3", and then I change all the 3s to something
else, the pivot table remembers the missing value. I.e., if I
check the "Show items with no data" checkbox for that field, the
pivot table will contain a column called "3" even though there are
no 3s any more. What's more, that column will continue to appear
even after I refresh the pivot table. In fact, the only way I can
make that column go away permanently is by calling PivotItems("3").Delete()
from VBA. (Is there a way to do this manually?)
However if I try to add a PivotItem to the table using VBA I can
do it, but the added column disappears the minute I do a refresh.
The best I can figure is that adding a PivotItem to the table
doesn't touch the cache, while adding data to the database does,
even after that data has been deleted.
So is there any way I can tell the pivot cache (not the pivot
table!) to add a new item? Deleting the item from the table seems
to affect the cache, but there doesn't seem to be any obvious way
to add items.
(I suppose I could add rows containing the desired values to the
database, refresh the pivot table, and then remove the added rows,
but that seems really hackish, and I'd much rather do it directly).