OLAP Pivot table - drop down data not getting refreshed

[Chinmayi007]

Hi
(I have been advised to re-post the question in Excel or SQL Analysis services forum)I have two Pivot tables which are connected to an OLAP Cube and the data in table gets refreshed when Data->Refresh->Refresh All is done, but for some reason the drop down list does not get refreshed. There are two cases:

In one of the Pivot table, the drop down for the 'filter' is not getting updated with the new items.
In the second, the drop down for the 'row label' is not letting go of the old items.


I looked for a solution on few forums and found this thread to be the closest to mine:

http://blog.contextures.com/archives/2011/04/22/clear-old-items-in-pivot-table-drop-downs/

Among the suggested solutions, the Manual Clear.. helped to resolve the first case (Remove the filter > save the sheet > close excel> open it again > refresh > add the filter), however it did not resolve the second one.

The other solutions did not help which I believe work only on non-OLAP pivot tables (the solutions included programmatically removing the old items from the drop-down using VB script and other was Right-click->Pivot table options-> Data tab -> select None in number of items to retain field )

Any help will be really appreciated. Thanks

Chinmayee​

 

[mole999]

? if you look at pivot table options, tab DATA, and change Number of items to retain per field to NONE, maybe that will allow it to refresh

 

[Chinmayi007]

Hi there! I did try for that option, but apparently that option is not enabled when you are dealing with OLAP based pivot tables.

 

[mole999]

ah, sorry about that. I guess it must be possible to code the data that goes in the drop down