Solved: Pivot Cache Consolidation

[Phelony]

Hi Guys :mkay

Yesterday I created a thread by the same name and thought I'd solved the problem.

http://www.vbaexpress.com/forum/showthread.php?t=32061

In short, I've got about 70 pivot tables in a workbook and some of them are using a different versions of the same pivot cache.

In an astoundingly intelligent move, I've put different tables using different source data on the same worksheets and any attempt I make to resolve this issue (and the 203mb workbook size) results in data being completely removed.

This workbook acts as data storage for a reporting workbook and it's size is causing considerable issues. :help

Can anyone suggest a way to identify different pivots using different data and consolidate the cache where the source is the same?

I really really really hate pivot tables and am really trying to avoid having to rebuild them all as the overall structure of the reports has taken weeks to establish....:motz2:

Phel x

[Phelony]

What if I were to rename the pivot tables that use the same cache to the same name and then re-designate them with the same cache?

Something like:

Dim PT as ("Pivottable1")
Dim wks as Worksheet

For each PT in wks.Pivottables
PT.refreshtable
PT.cacheindex =1
PT.Savedata = false
Next

???

[JWhite]

Some questions:

1. Is this a single-user application, that is, does the same user maintain the data AND use the pivottables? Otherwise, why wouldn't the data be in a different workbook or in a database? That would probably be a good idea in any case because if the data and the pivottables are in the same workbook that means the pivotcaches AND the source data are both taking up space in the workbook, making it larger.

2. What do you mean when you say "different versions of the same pivotcache"? You only need one pivotcache for each datasource and then you can have any number of pivottables pulling data from that pivotcache. I just wanted to make sure you have that straight - one data source = one pivotcache. Adding another pivottable which uses an existing pivotcache takes very little memory but new pivotcaches take roughly as much memory as the data source.

3. Just read your second post. I've never used "cacheindex" because I don't see the need. When you create a pivottable you attach it to the most appropriate pivotcache and set the SAVEDATA parameter at that time. When you refresh any pivottable using that pivotcache, they all get refreshed.

FYI, I have an application which has an enormous database as it's source. Our users load hundreds of megabytes (a subset of the database) into a pivotcache and may create dozens of pivottables from that pivotcache. I only have to refresh one pivottable - any one - and they all refresh. But I don't have much experience with multiple pivotcaches so maybe I'm missing something.

[Paul_Hossler]

Q1: What version of Excel? I believe 2007 uses PT caches a little differently.

I have one WB that has 5-6 PT's on one cache, and 2-3 on another.

PT's are created with VBA, and so far no problems

Q2: You said "DIfferent version of the same PT cache". Did you mean the PT's are selecting different fields to display but from the same source data?

Paul

[Phelony]

I eventually wrote more code to find out the depth of my problem. Upon seeing the results, I've decided that I'm better off digging myself out of the hole as an automated solution will cause more havoc than good! Have edited this response and marked as solved as a result.

Thank you both for your responses.

Phel x

The below handily lists each workshet, pivot and it's index.

Sub test()
Dim ws As Worksheet
Dim wsPvtList As Worksheet
Dim pt As PivotTable
Dim rng As Range
Set wsPvtList = Worksheets.Add

wsPvtList.Range("A1:C1") = Array("Worksheet", "Pivot Name", "Pivot Index")

Set rng = wsPvtList.Range("A2")

For Each ws In Worksheets
If ws.Name <> wsPvtList.Name Then
For Each pt In ws.PivotTables
rng.Value = pt.Parent.Name
rng.Offset(, 1).Value = pt.Name
rng.Offset(, 2).Value = pt.CacheIndex
Set rng = rng.Offset(1)
Next pt
End If
Next ws

End Sub


Previous response prior to recent re-edit

Hi guys and thank you for responding to my query.

JWhite,

1. it is a single user application as the same user does maintain the data and the pivottables. Data is produced from another system as a csv which is then loaded into an mdb and then in turn reported through excel.

This is not the ideal way of doing this in my view, however, as I'm using Excel 2003 a single worksheet cannot hold a sufficent amount of data for the reporting requirements, i.e. the data is in excess of 65k rows.

2. Regrettably the answer to this lies in bad design. The data produced forms part of a weekly report and to repair an issue with one of the pivot tables it was rebuilt. A miscommunication led to an entirely new pivot table being created using a new cache from the same data source, this has resulted in two versions of several pivot tables being created. This error was then replicated and has resulted in several pivot tables having an independant cache when in fact they should be using the same data.

It is this problem that I am attempting to repair, hopefully without having to completely rebuild the workbook.

Paul,

1. It is excel 2003 as detailed above.

2. Different fields are used, however they are all using the same source data and therefore should only be using one pivot cache, as I've said above, duplicate cache's have been erroneously created resulting in the workbook swelling to virtually unusable proportions.



Basically, I'm trying to tie the pivot cache's together where they are using the same source data. When I've tried to correct this mannually in someinstances it results in a new pivot table being created which then causes the associated GETPIVOTDATA formulas to fall apart and subseuqently any other formula which depend on them.

I do appreciate that in many ways "going back to the drawing board" is the simplest solution, however, if I could get the above to work correctly it would solve an immediate problem, cap the well so to speak.

I trust this clarifies your queries, I am continuing to experiment with repairing the pivottables, although to be honest I am close to considering a rebuild to be a far easier solution. :(