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).
--
John Brock
(E-Mail Removed)

Debra Dalgleish has some instructions here:
http://contextures.com/xlPivot04.html

John Brock wrote:
>
> 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).
> --
> John Brock
> (E-Mail Removed)

--

Dave Peterson

Thank you for a useful page. Alas though, it only explains how to
clear old items from a pivot cache. I want to add new "old" items,
without the runaround of adding and then deleting records from the
database.

In article <(E-Mail Removed)>,
Dave Peterson <(E-Mail Removed)> wrote:
>Debra Dalgleish has some instructions here:
>http://contextures.com/xlPivot04.html

>John Brock wrote:
>>
>> 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).
>> --
>> John Brock
>> (E-Mail Removed)
>
>--
>
>Dave Peterson


--
John Brock
(E-Mail Removed)

Click on the Pivot Table and then do Right click and select the refresh
option. This would help the pivot table to take the effect of the changes you
have made to the database....

--
Qaiser


"John Brock" wrote:

> 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).
> --
> John Brock
> (E-Mail Removed)
>
>

A pivot table doesn't allow display of data that doesn't (or didn't) come
from the source database.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"John Brock" <(E-Mail Removed)> wrote in message
news:g34m2p$3o9$(E-Mail Removed)...
> Thank you for a useful page. Alas though, it only explains how to
> clear old items from a pivot cache. I want to add new "old" items,
> without the runaround of adding and then deleting records from the
> database.
>
> In article <(E-Mail Removed)>,
> Dave Peterson <(E-Mail Removed)> wrote:
>>Debra Dalgleish has some instructions here:
>>http://contextures.com/xlPivot04.html
>
>>John Brock wrote:
>>>
>>> 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).
>>> --
>>> John Brock
>>> (E-Mail Removed)
>>
>>--
>>
>>Dave Peterson
>
>
> --
> John Brock
> (E-Mail Removed)
>