1

Closed

Error when applying filter list

description

I am using Excel 2007. When I try to use the Filter List feature, I get the following error. The error occurs when I click on the "Filter PivotTable" button, after selecting a filter hierarchy and pasting a list of values.
 
Exception from HRESULT: 0x800A03EC
at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
at Microsoft.Office.Interop.Excel.PivotCache.MakeConnection()
at OlapPivotTableExtensions.MainForm.ConnectAdomdClientCube()
at OlapPivotTableExtensions.MainForm.workerFilterList_DoWork(Object sender, DoWorkEventArgse)
 
Any idea of what could be wrong ?
Let me know if you need more details.
 
Regards,
Bert
Closed Aug 30, 2012 at 1:58 PM by furmangg
clearing the PivotTable cache before filter list apparently helped

comments

furmangg wrote Mar 8, 2012 at 1:21 AM

Looks like it's unable to connect judging based on the stack trace. Are you able to refresh the PivotTable? What's the connection string if you edit the connection and look at the properties of the connection?

bda75 wrote Mar 8, 2012 at 7:27 AM

I am able to refresh the pivottable, but the first attempt sometimes fails with a connection issue. When immediately repeated, the second attempt always succeeds. This is why I also tried to refresh the pvtt before applying the filter with the add-in, but doing so does not bring any improvement.

Here is the connection string:
Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=OLAP2;Data Source=sqlssas;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error

furmangg wrote Mar 8, 2012 at 5:22 PM

If you're sometimes getting connection errors, then that's likely what FilterList is seeing, too. Sorry that's not a very friendly error message.

If you try Filter List several times does it sometimes succeed?

Are you using integrated security where you're connecting to the cube under the credentials you logged into your laptop under? Or did you type in a username and password when you connected to SSAS?

bda75 wrote Mar 9, 2012 at 7:40 AM

Once it has failed, the button is greyed and no other attempt can be made. You need to close the tool, re-lauch, paste your values...etc and it always fails.

I am indeed using integrated security. I do not need to provide any credential manually.

bda75 wrote Mar 9, 2012 at 1:17 PM

Found the solution :-) : clear the pivot table cache (this command is available from the pivottable context right click menu). Everything works fine afterwards.
I do not know how useful this can be to you, but I was starting from a file saved as a template. I have created several such templates to contain frequently used pivot table combinations.

furmangg wrote Mar 9, 2012 at 1:18 PM

I would recommend researching the source of the frequent connection failures outside of OLAP PivotTable Extensions. Probably the SSAS forums are the proper place for that:
http://social.msdn.microsoft.com/forums/en-US/sqlanalysisservices/threads/

I will consider changing my add-in to handle connection failures better... better error messages and better ability to retry. I'll leave this issue open for that purpose. Please update us if you make progress.

bda75 wrote Mar 9, 2012 at 2:19 PM

Thanks for you help, and most of all thanks for sharing this very useful add-in. At least, I now know how to make it work all the times.

Sign in to add a comment