Pivot Table calculated items with dynamic file source
 

Group:

I'm trying to use/add calculated items within a Pivot Table. The
Pivot Table input source is dynamic. I change the
Pivotcache.connection to read in the input from one of multiple files.

I can individually:
1) Add a calculated item to a Pivot Table
2) Change the Pivotcache.connection to a new file and update the Pivot
Table.

I cannot do both at the same time for the same Pivot Table.

When trying to add a calculated item to a Pivot Table, I get the
following errors:
Excel 2003: "Averages, standard deviations, and variances are not
supported when a pivottable report has calculated items"

Excel 2000: "Multiple data fields of the same field are not
supported when a Pivot Table has calculated items."

If I attempt to change the Pivotcache.connection to a new file, this
Pivot Table is not updated. All other Pivot Tables that do not have a
calculated item, but use the same Pivotcache, are updated accordingly.


Any tips/pointers would be appreciated

Bob Nealon