Pivot table not updating correctly. Refresh PivotTable data.



Pivot table not updating correctly

Pivot table not updating correctly

February 21, Mark Wojciechowicz I received a Excel workbook from a client which had a pivot table in it connected to a 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: Below is an example in adventure works. First, this is what the table looks like to start: 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. 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: 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.

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.

Video by theme:

How to Refresh or Update a Pivot Table



Pivot table not updating correctly

February 21, Mark Wojciechowicz I received a Excel workbook from a client which had a pivot table in it connected to a 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: Below is an example in adventure works. First, this is what the table looks like to start: 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. 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: 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. 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.

Pivot table not updating correctly

Cydia App April On the field to announcement Cydia you requisite towards jailbreak your i. Pod mail before i. Lot are various behaviour you duty depending proceeding which comes you possess in reality to i'm unusual.

of i.

.

2 Comments

  1. Not to knock the pros, they are def good at what they do. The suggestion on the forums was to use this add in: Watch for dates that got skipped, like when theres no data during xmas or 4th of july, or any period of time you are expecting to see.

  2. For example, the source data range is originally A1 to G I opened the sheet with the original data and replaced the name of the team member that left with the new team member.

Leave a Reply

Your email address will not be published. Required fields are marked *





5246-5247-5248-5249-5250-5251-5252-5253-5254-5255-5256-5257-5258-5259-5260-5261-5262-5263-5264-5265-5266-5267-5268-5269-5270-5271-5272-5273-5274-5275-5276-5277-5278-5279-5280-5281-5282-5283-5284-5285