How to take data from PivotTable cache?

Question

I have lost the connection to source analytics service. However, I still have data in PivotTable's cache (I can see from tooltips).

How to get that source data?

• I am using Office365

** I cannot use show details since the pivot table has some filteres applied. And i cannot remove filters since it asks me to connect source.

(And yes I have checked this thread but it didn't work for me: Recreate Source Data from PivotTable Cache. It gives 1004 error.)

Answer 1

The easiest way I can think of for a table based data set is:

• Show the field list on your pivot table.
• Remove all filters, column labels, row labels, and values

• Add one field to the values. This will show one aggregate value in the pivot table.
• Double click the value and a sheet with all the data should pop up.
• If it doesn't, go to the pivot table options then on the data tab check "Enable show details".

This method however will not work with OLAP data. Excel does not download the entire cube; it queries for new data slices with every change to the filters or layout of the pivot table/chart. So even if you could access the data in the pivot cache it would not hold the entire cube, but only the slices needed to show the current layout. You CAN create a snapshot cube file to hold all of the data needed to run in offline mode, however it requires you to be able to connect to the server at least once to create the file.