Pivot Table multiple filters

Topics: Resolved
Oct 5, 2010 at 9:10 PM

Hi, I'm working with a complex pivot table that references a cube, and the problem that I'm running into is that each time I change one of my filters in the pivot table, this kicks off the OLAP query for several minutes.  Then I can make my next filter selection in the pivot table, then the OLAP query kicks off again.  Then I make the next filter selection, etc.

I stumbled across this add-in for Excel and I'm wondering if it will allow me to filter on multiple fields and build my filter criteria before refreshing the pivot table, hence causing the OLAP query to run only once.

I appreciate any input that you can provide!!  Thanks!

Coordinator
Oct 6, 2010 at 8:32 PM

I guessed that the "Defer Layout Update" button might help, but checking that box disables filter dropdowns. So that doesn't help.

OLAP PivotTable Extensions doesn't have any functionality that would help you. But it's something we could consider for a future release... though honestly, it's probably pretty low on the list.

You could probably write a VBA macro that would do this for you if you know ahead of time the values you want to filter to.

Or you might consider removing everything from rows, columns, and values, changing the filters, and then adding back rows, columns, and values. Not handy, but should perform better.

Or get your AS administrator to optimize the cube and calculations so it's not so slow :-)

Sorry... can't suggest anything else.

Marked as answer by furmangg on 6/10/2014 at 4:23 AM
Coordinator
Jun 10, 2014 at 12:22 PM
Marked as answer by furmangg on 6/10/2014 at 4:22 AM