Crashing Pivot tables in Excel 2007 (SP 1)

Question

We are using MS Excel 2007 Pivot tables to access en SSAS 2005 Cube. Farly often when we reopen an excel spreadsheet with one or more pivot tables we get an error like this:

Excel found unreadable Content. Do you wish to repair

 

When we click yes the following log is shown:

Removed Feature: PivotTable report from /xl/pivotCache/pivotCacheDefinition1.xml part (PivotTable cache)
Removed Feature: PivotTable report from /xl/pivotCache/pivotCacheDefinition2.xml part (PivotTable cache)
Removed Feature: PivotTable report from /xl/pivotTables/pivotTable1.xml part (PivotTable view)
Removed Feature: PivotTable report from /xl/pivotTables/pivotTable2.xml part (PivotTable view)
Removed Records: Workbook properties from /xl/workbook.xml part (Workbook)

And all the pivot tables are converted to plain text.

I have read about this in KB 929766 but this do not apply since KPIs are not used.

The KB 943088 is more interesting but after upgrading all user with SP 1 the problem is still there, mostly when we open old excel-files that has been created without SP 1 but opened and saved once with SP 1. After that we are not able to open them at all, either with or without SP 1 installed.

Is there some way to “save” pivot tables from destruction? I can’t ask the users to rebuild all there spreadsheets that have been created prior SP1. What will happen when there is a new SP for Excel? Rebuild all spreadsheets and pivot tables again?

Answer 1

Have you found a solution?

I'm suffering the same issue on new documents and always based on the same cube!

 

Answer 2

i'm having the exact same problem.
KB943088 looks intresting but doesnt help solve the problem.

i've tried to uninstall office 2007 to get rid of SP1 and that didnt help anything.
i can reproduce this 100% of the times on my computer by simply creating a new empty excel book and add one pivot table from a SSAS 2005 cube.
when i save it localy on my computer and reopen it i get that error about the file being corrupted.

one would atleast think that excel could open its own files on the same computer with the same SP level.

problem is that because the file gets corrupted moss 2007 doesnt like it.

Answer 3

i removed office 2007, installed it again and then ran windows update patches to get sp1 and now the problem seems to be gone.

Answer 4

Hi.

I have the same problem with one of my cubes. It only happens when i use a certain dimension, either in the filter or rows or columns.

To be correct, when I use it in the filter it lets me save the workbook, and then the problem happens. If I use it on rows or columns it crashes excel immediately.  This dimension is a parent-child dimension. I have other similar in the same cube with more members and they don't crash excel neither maker the content unreadable.

I'm trying to get to a solution, but since you all just refer  the use of the cube, not on any localized part of cube I thought I could help by referring my situation.

Hope this helps everyone.

---

Luís Patrício

Answer 5

 

TobbeJ1 said:

i removed office 2007, installed it again and then ran windows update patches to get sp1 and now the problem seems to be gone.

I've finaly found the cause of this problem!
The problem has to do with translation of the olap cube, or more specificaly missing translations for some items in the cube and how this is handled by excel.

The reason it worked after i reinstalled it was because the cube i have created is fully translated in english (the default language if no other translation is avaialbe) so after installing english i had all attributes and measures fully translated and no empty captions.

What happens is that the caption atribute of the cacheHierarchy tag in the xml files in /xl/pivotCache/ is an empty string and this excel does not like.

What i did was:
i had 2 files, one working saved with english excel and one broken saved with swedish excel.
i used OpenXmlDiff from the open xml format sdk v2 and started looking around at what the differences was.

i noticed that one attribute of a measure had an caption="" tag, so i unziped the broken xlsx file, added some text  to the caption and zipped the file again and opened it with excel, and it worked!
(i also experimeted with removing swedish characters åäö from names, but i belive that was not the cause)

Answer 6

 Thanks TobbeJ2, your post put me on the right track and solved our problem!

We are a MS partner delivering BI solutions and the problem that customers could not save their OLAP pivottable in excel 2007 without losing pivot funcationality was really troubling us. As you mentioned I have found out that the cubes which are causing problems all contain missing translations (in Dutch in our case). When I connect to the cube with the default language (English, the translations are not adressed then) the problem was gone! We will add a translation for each field, for each language and then the problem should be solved.

Thanks again!

 

Answer 7

I have spend most of to day on this issue because adding captions for each translation wasn't enough to solve the problem. In the pivotCacheDefinition1.xml file (rename .xlsx to .zip and unzip to locate this file) there was indeed an empty caption (caption="") for one of the dimensions. In this dimension the translations were completely filled however. Changing this value manually did not work. It seems excel keeps changing the xml back on opening of the file.

The solution was to remove the translations completely from the faulty dimension. The problem is now gone!

For several other dimensions translations work fine though! This dimensoin is the only dimension sharing the same dim table and key with another dimension the cube (it is an alternative hierarchy put in a separate dimension). I suspect the problem has to do with this somehow.

We have opened a call with Microsoft anyway. If they come up with more information I will let you know.

Answer 8

yes excel will change the xml files back next time you open the file, so modifying the xlsx file is just a temporary fix.

remeber that you can have hidden attributes that will not show up by default in the translation tab.
so you need to use one of the buttons at the top to make sure you see all possibel translatable attributes and you need to translate even the hidden ones or the file will break.

there must be a bug somewere causing this, question is if it's in excel or analysis services or maybe both.

Answer 9

Try saving as XLSB (binary format).

Answer 10

i am prety sure that doesn't help at all.
it even makes things worse because now you can manualy modify the file to fix it.

as stated before, this issue is with missing translations in the cube.
and i'd consider this a bug because if there is missing translations it should fall back to the default language.

Answer 11

We opened a case with Premier Support and they confirmed that it is a bug in Excel 2007. The solution they gave us was to use Excel 2010 since the MDX that is generated in Excel 2010 is different. Then you can save it and open it in Excel 2007 without any issue.

I hope it helps to someone.

---

PabloMaria

Answer 12

Can confirm you suggestion. Had the problem very often in Excel2007 and after installing Excel 2010 (some weeks ago) the problem is gone!

 

Regards Heimo

Answer 13

Hi Guys,

I think this is my problem...except that I am unable to work out the technical part of the whole process, and migrating from 2007 to 2010 did not help the process. I was hoping to send the corrupted .xls files to one of you for redemption.

Can I, please?

 

McNeal

Answer 14

Try saving as XLSB (binary format).

finally works!!!!!!, try this format it ok

Answer 15

I have the same problem in Excell 2010. It means the proble still apers in new version :-(.

Answer 16

I also come across the same problem of Crashing PivotTables in Excel 2007.....mine was very simple PT sourced from a Table in another sheet. I saved the fully working file and when re-opened the PT functionality gone.

The problem I noticed with my PT is it has two Fields of same name "Years". One field in source table itself and other I get when I grouped the dates field for Years. 

After editing the pivotCacheDefinition1.xml for one of the "Years" to "Years2", I got my fully working PT back.

Hope this help you with your quest.

Murtaza

Answer 17

Has anyone tried to delete the ~*.Tmp file from there system?

http://support.microsoft.com/kb/211632

Answer 18

Same problem to me, in Excel 2010.

Same solution here:
As TobbeJ2 expained, it can be solved my modifying the caption="" to caption="whatever" and changing your SSAS Cube Model so that all Elements have a translated caption.