Pivot Table refreshes but the Row Items are not updated







I received a 2010 Excel workbook from a client which had a pivot table in it connected to a 2008 R2 Cube.  The issue was that though you hit refresh on the table a customer name was not updating.  The customer name had been changed in the source system and the client wanted to see the change in the table.

I quickly recreated the pivot table in another sheet off the same connection and saw that I had different results – the value that the client was expecting appeared.  I checked the connection properties and made sure that they were pointing to the right place.  The query showed up in profiler when the table was refreshed and the results of that query returned the correct data in SSMS.

So how is it that an Excel Pivot Table successfully refreshes but the data does not change?  I put a question up on the forums and found out the answer there.  I would like to share it, because this was such unexpected behavior and it took a while to figure out and test.  Here is a link to the discussion:  http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/29d689a6-15a2-4342-8593-75e2f9ba9777

Ultimately, the problem was that the user accidentally (or not) changed a client name in the pivot table and that name stuck, even though the data was being refreshed.  Below is an example in adventure works.

First, this is what the table looks like to start:



Note the name of “A Typical Bike Shop,” – we’ll cleverly change this to “A Not Typical Bike Shop.”






All I do is edit the name in the formula window.  Why should one be able to do this?  I have no idea, maybe no one knows where to fix it in the source system, or maybe the ETL team was fired.  More likely someone just types over top, inadvertently. 

Now, if I do a refresh, nothing changes, the name remains the same. 

 



Note that even the name is changed in the filter list as above.  Next, we’ll add a label filter and filter where the name as Begins With “A Typical….”  I would not expect this filter to capture “A Not Typical…,”but there you have it below.  The label filter actually uses the real data to perform the filtering, not the typed over value.

 
So how do you fix it and get things back to the way they were?  The suggestion on the forums was to use this add in: http://www.contextures.com/xlPivotAddIn.html

This sort of works, but it changes the values to the member unique names:



The office site states that:

If you hide and then redisplay levels in PivotTable reports based on OLAP source data, any renamed fields or items revert to their original names.

http://office.microsoft.com/en-us/excel-help/rename-a-pivottable-field-or-item-HP005202103.aspx

I have not been able to get that magic to work.  So, sadly, the work around seems to be that you have to scrap the pivot table and start over.  This might not be so bad if you could even tell that the data had been altered in some way, but there is visually indication that anything has changed.  This is an important thing to be aware when working with pivot data.

Mark Wojciechowicz
2013-02-21

Labels: , ,