Error Publishing PivotTable to MOSS.

[Guest]

We create an Excel 2007 PivotTable against a cube that contains KPI’s, but we
do not include any KPI elements in the PivotTable itself. Excel Services
won’t publish it, saying ‘The workbook that you selected cannot be opened.
The workbook may be in an unsupported file format, or may be corrupt.’

If however we create the PivotTable against the same cube with KPI’s
removed, everything works!

 

[Guest]

When you publish an Excel Pivot Table to MOSS you get this error: "The
workbook that you selected cannot be opened. The workbook may be in an
unsupported format, or it may be corrupt. Would you like to try and open this
file in Excel?"

There is a Microsoft KB article that addresses this behavior (kb929766).
Also you can check the cube for any KPI's that use weighting. If the cube
that the PivotTable connects to has any KPI's present that use weighting then
you will get the error above. There are two ways to resolve the error.

1. Remove the weighting from the KPI using the Analysis Services Business
Intelligence Development Studio.

2. Edit the PivotTable cache to remove any values in the weighting tags of
the XML file. This can be done by renaming the .xlsx file to .zip and
extracting the zip file. Go to /xl/pivotCache/pivotCacheDefinition1.xml. You
can open this file with an XML Editor or notepad and make sure that "weight"
is not set to a value. At the end of the example below you will see that no
value is entered for weight.

<kpi uniqueName="Revenue 1st District Qtr 3 2007" caption="Revenue 1st
District Qtr 3 2007" displayFolder="" parent="Revenue All Districts Qtr 3
2007" value="[Measures].[Revenue 1st District Qtr 3 2007 Value]"
goal="[Measures].[Revenue 1st District Qtr 3 2007 Goal]"
status="[Measures].[Revenue 1st District Qtr 3 2007 Status]"
trend="[Measures].[Revenue 1st District Qtr 3 2007 Trend]" weight=""/>

 

[Guest]

Please note that it is best to remove the KPI weighting in the cube, rather
than editing the workbook file directly as I suggested earlier, since it may
get corrupted when you refresh the Excel client. Looking at the file
contents directly can assist in identifying the issue, but should actually be
fixed in the cube.

When you publish an Excel Pivot Table to MOSS you get this error: "The
workbook that you selected cannot be opened. The workbook may be in an
unsupported format, or it may be corrupt. Would you like to try and open this
file in Excel?"

There is a Microsoft KB article that addresses this behavior (kb929766).
Also you can check the cube for any KPI's that use weighting. If the cube
that the PivotTable connects to has any KPI's present that use weighting then
you will get the error above. There are two ways to resolve the error.

1. Remove the weighting from the KPI using the Analysis Services Business
Intelligence Development Studio.

2. Edit the PivotTable cache to remove any values in the weighting tags of
the XML file. This can be done by renaming the .xlsx file to .zip and
extracting the zip file. Go to /xl/pivotCache/pivotCacheDefinition1.xml. You
can open this file with an XML Editor or notepad and make sure that "weight"
is not set to a value. At the end of the example below you will see that no
value is entered for weight.

<kpi uniqueName="Revenue 1st District Qtr 3 2007" caption="Revenue 1st
District Qtr 3 2007" displayFolder="" parent="Revenue All Districts Qtr 3
2007" value="[Measures].[Revenue 1st District Qtr 3 2007 Value]"
goal="[Measures].[Revenue 1st District Qtr 3 2007 Goal]"
status="[Measures].[Revenue 1st District Qtr 3 2007 Status]"
trend="[Measures].[Revenue 1st District Qtr 3 2007 Trend]" weight=""/>

We create an Excel 2007 PivotTable against a cube that contains KPI’s, but we
do not include any KPI elements in the PivotTable itself. Excel Services
won’t publish it, saying ‘The workbook that you selected cannot be opened.
The workbook may be in an unsupported file format, or may be corrupt.’

If however we create the PivotTable against the same cube with KPI’s
removed, everything works!