2014-12-15
I was troubleshooting a problem in which a pivot table appeared not to be refreshing properly. One of the category's names in the rows section was incorrect. Querying the cube, the data looked fine. In fact, creating a new pivot table in the same workbook fetched the correct values. What the problem turned out to be was that the user had typed over the pivot table and changed one of the values. This pivot table was connected to an SSAS cube, but this behavior is the same no matter what the data source is. Here's is a more visual explanation:
If I create a table in a worksheet and then create a pivot table from that:
I can try to change one of the values in the amount field, but that will return this error:
However, I can change one of the row labels:
If I refresh the pivot table, the value "My Name" will still be there instead of "Category 2." When this is an analysis services or powerpivot source, if you converted the pivot table to formulas, you will see it applied this way:
=CUBEMEMBER("MyModel","[Table].[categories].&[Category 2]","My Name")
The last value in the CubeMember() function is optional and sets the caption property. In any case, if we extract the excel file, we can look at the xml files that make it up:
In the xl folder, we will find a reference to the label change in the sharedstrings.xml. We will also find another reference in xl\PivotTables\PivotTable1.xml (or whatever the pivot table name is), which looks like this in my example:
<pivotField axis="axisRow" showAll="0">
<items count="4">
<item x="0"/>
<item n="My Name" x="1"/>
<item x="2"/>
<item t="default"/>
</items>
</pivotField>
So the value is saved in the definition of the pivot table (not quite the pivot cache which is another set of files). This is some pretty tricky behavior to be aware of. I really cannot think of a sensible use case where it is a good thing that you can change these values, but there you have it.
Mark WojceichowiczLabels: EXCEL
