This project has moved. For the latest updates, please go here.

Error at Clear PivotTable Cache after level expanded


The customer has German version of Office 2010 v14.0.4760.1000 32-bit. After installing OLAP PivotTable Extensions and loading an Excel file containing an OLAP PivotTable the clear PivotTable Cache works. But when the rows are touched - expanded and or collapsed, we get the error in the attachement. The cube was created on AS 2008 R2, v10.50.1600 64-bit.

file attachments


furmangg wrote Sep 25, 2012 at 3:45 PM

Can you describe the full sequence of events further? When do you "touch the rows"? Before or after the PivotTable cache is cleared?

The way I intended the feature to work is to clear the PivotTable cache, then save the file, then distribute it to other people without fear of cached data revealing something they shouldn't see. I didn't intend for you to clear the cache then continue to interact with the PivotTable, if that's what you're doing.

The error message in the screenshot says essentially that the connection string doesn't specify Cube=YourCube. The OLAP PivotTable Extensions code should add this to the connection string temporarily. If it's having trouble doing that, feel free to edit the connection and add that Cube=YourCubeName property into the connection string yourself to see if that solves the problem.

HelmutK wrote Sep 26, 2012 at 6:39 PM

Open excel workbook containing PivotTable, touch the rows then clear PivotTable Cache --> Error
Open excel workbook containing PivotTable, immediately clear PivotTable Cache --> works OK, save.
Open excel workbook containing PivotTable, clear PivotTable Cache, refresh to fill the PivotTable again, touch rows, clear PivotTable Cache --> Error
Save Excel Workbook, reopen it, immediately clear PivotTable Cache --> works OK, save.

Problem: whenever you touch the rows (or columns?) and then clear PivotTable Cache, you get the error and the clear PivotTable Cache does not work.
Workaround: Whenever you touch the rows (or columns?) of a pivotTable, before you clear PivotTable Cache you must first save the Excel workbook. Then after re-opening the Excel workbook immediately clear PivotTable Cache and then save the empty workbook.

Where should they enter the Cube=YourCubeName ?
Where does OPTE get the YourCubeName from ?
Perhaps this source looks different in the German version ?

I cannot test it anymore. I have installed the English version of Office and cannot reproduce the problem.

HelmutK wrote Oct 8, 2012 at 11:42 AM

Hi Greg, do you have any comment on my description or answer to m questions?
Regards, Helmut

furmangg wrote Oct 8, 2012 at 9:54 PM

Sorry for the delay. I don't have any clue on how to fix the problem. But if you can put Cube=YourCubeName in the connection string, that might be a workaround. If you look at the current connection and look at the Command Text, it will show the cube name. The code that grabs the cube name is OLEDBConnection.CommandText.

If you have other suggestions, I'm open to suggestions.

HelmutK wrote Oct 27, 2012 at 2:58 PM

The connection string dialog shows:
Connection Name: DE000-SAS01V OLAP_Fertigartikel Stock_Balance
Connection File: http://bi/Verbindungsbibliothek/DE000-SAS01V OLAP_Fertigartikel Stock_Balance.odc
Connection String: Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=OLAP_Fertigartikel;Data Source=DE000-SAS0V;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error
Command Type: Cube
Command Text: Stock_Balance

Looks correct to me - even the cube name is shown in the Command Text.
What do you think?
Regards, Helmut

HelmutK wrote Oct 30, 2012 at 10:02 PM

The customer did include the Cube=MyCube; in the connection string and the error did not occur. Any idea why it is not inserted by the code?

wrote Feb 22, 2013 at 1:07 AM

xiaoyuandlg wrote Jan 7, 2014 at 3:12 AM

I got the same question, having to move to another pivot table like control.

furmangg wrote Feb 24, 2014 at 5:21 AM

I added some debugging code into the latest release (0.8.2). If you can reproduce this error again, I would appreciate a screenshot of the new error message.