PivotTable question

  • Thread starter Thread starter lc
  • Start date Start date
L

lc

I found a way to refresh data in pivot table by changing its query
(PivotCache.CommandText). The problem is that, even though data gets
refreshed fine, the dimension members remain the same. How do I go about
refreshing everything? The "Refresh Data" toolbar button refreshes all.

BTW, I also noticed that, if a dimension is already used on one of the pivot
pages, its members will never refresh until it gets removed from pivot page
and the data got refreshed again.

Finally, dimension members do refresh with new members (if query changed
so), but old members that no longer should show are still there.

Any ideas?

TIA

lc
 
Debra Dalgleish said:
There's code on the following page for removing old items from a
dropdown. It's not tested on an OLAP cube, but may help:
Click to expand...

Thanks Debra. I stumbled across these examples and tried both of them.

The MissingItemsList simply doesn't work with 2003.

pt.PivotCache.MissingItemsList = xlMissingItemsNone
pt.PivotCache.CommandText = .....

but nothing happens. It appears (at least with 2003) that you must call
PivotTable.RefreshTable or PivotTable.PivotCache.Refresh. That updates
everything but it also re-queries the datasource. Problem is that the query
already ran when I assigned a SQL statement to CommandText.
PivotTabe.ManualUpdate doesn't seem to have any impact.

With For loop, items do disappear but its a long process and if one of the
on-screen dimensions contains a number of items (like 300), the screen
flickers with each Item delete. Is there a way to lock screen for updates?

Thanks a lot.

lc
 
I'm a bit confused.Do you just want:

ActiveSheet.PivotTables("PivotTableNameInHere").RefreshTable

This is from a recorded macro
Or:

ThisWorkbook.RefreshAll

This will refresh all pivots in the workbook.

Regards,
Jason.
 
jason said:
I'm a bit confused.Do you just want:

ActiveSheet.PivotTables("PivotTableNameInHere").RefreshTable

This is from a recorded macro
Or:

ThisWorkbook.RefreshAll
Click to expand...

I change the query in my procedure and load it into
PivotTable.PivotCache.CommandText to execute it. Just by doing this, the
query fires and data gets refreshed. However, what doesn't get refreshed are
the items in the dimensions fields. In order to remove them I would either
1) loop through PivotFields and PivotField.Items and delete the appropriate
ones. The problem with this solution is that each delete causes the screen
to refresh with a lot of flicker. Besides, with larger dimensions, this can
literally take ages.
2) call PivotTable.RefreshTable or PivotTable.PivotCache.Refresh. Either of
these two cause the query to fire again where, in fact, the query already
ran when CommandText was assigned.

Once again, MissingItemsList has no effect in 2003 - not that I can see, at
least.

Thanks.

lc
 
In case anyone else runs into the same catch 22 game:

I took a longer route and am deleting PivotItems myself. The screen flicker
is gone with a call to Application.ScreenUpdating.

lc
 
Back
Top