Refreshing causes me to lose Calculated Members

Sep 25, 2015 at 6:21 PM

This is my first post here & I have been using OLAP PivotTable Extension for a while now & I have find it very useful.

I have been experiencing an issue when using Calculated Members in OLAP PivotTable Extension.
I setup my pivot table
I create my Calculate Member which is just a sum of three options I have in the Columns.
I filter my Columns in the pivot table to only show my new Calculate Member
Brilliant this is what I wanted! The pivot table is setup correctly.

The problem occurs when I want to refresh the pivot table. Once it's finished refreshing the pivot table is empty although all the elements I placed in the Rows, Columns, Filter etc. are still there. When I select the filter for the Columns I see my Calculated Member but it's greyed out (although its selected) When I select any one of the options in the Columns it populates the pivot table & shows my Calculated Member too. The thing is I don't want to show any other options I just want to show the Calculated Member I have. What could be causing this to happen? I have many different thing to get around but have failed. Could someone help please.

I am using Excel 2013 32bit with OLAP PivotTable Extension v0.8.4

Sep 25, 2015 at 7:11 PM
Hmm. I haven't witnessed this. Are you able to create a simple repro with dummy data and share the cube backup and the PivotTable? I'd be happy to look at this.

Is it Multidimensional? Tabular? What exact version number when you connect SSMS Object Explorer shows on the SSAS server node?
Sep 30, 2015 at 1:32 PM
Hi its a multi dimensional cube SSAS is version 11.0.3412.0. I'm not sure I can export the data because I don't have access & it's pretty sensitive.

I carried out a few tests & discovered something odd in Excel. I started a new Workbook & connected to the Cube to start a new pivot table within the pivot table options there is option 'Evaluate calculated members from OLAP server in filters' I have no idea what it does but when I uncheck this & refresh the pivot table it seems to maintain the setup & the data is still there only thing is I don't know if it's refreshing properly & calculating the Measure.

This is what I see in the pivot table options when I start a new Workbook & create the pivot table from scratch.

New Pivot Tables

The unfortunate thing is I have already created a lot of files with a lot of pivot tables using a Calculated Measure when I look at the pivot table options in that I see something different

Existing Pivot Tables

I think this may be causing the issue but I am not entirely sure. Both files were created in Excel 2013 so I can't see why one would have the 'Evaluate calculated members from OLAP server in filters' option & the other would not. The Measure I have created is nothing overly complicated either.


So has anyone seen this before or experienced anything like this? & does anyone know what the 'Evaluate calculated members from OLAP server in filters' as I can't find anything on this.

Nov 6, 2015 at 9:01 PM
If you open OLAP PivotTable Extensions and go to the About menu it should tell you about the PivotTable version. For example a newly created PivotTable should be 2013 version. But the old saved PivotTables are likely older versions. You may want to follow the instructions for upgrade and see the calculations are preserved.

Just checking but you are defining a new measure named P12M. It's a measure not a calculated dimension member right?

If they are lost during upgrade, I wonder if you can turn off upgrade. If you click on the PivotTable then press Alt-F11 then Ctrl-G then paste the following into the Immediate window and press enter:

ActiveCell.PivotTable.PivotCache().UpgradeOnRefresh = False

Then paste in the following line and hit enter and ensure it says False:


That should turn off upgrade on refresh. I'm just guessing that may be the problem because of the differences in the PivotTable Options window you included in screenshots.

What's your version number of Excel if you go to File... Account... About Excel? Wondering if you've got Office 365 and the latest update or not.