Can anyone help me beta test this code to filter pivots?

[jeffreyweir]

Howdy folks. I've punched up some code that lets users quickly filter a PivotTable field based on an external range. So say you've got a pivotfield called 'Cities' with 10,000 cities in it, and an external range with say 100 of those cities. This code will let you very easily filter those 10,000 cities to just show the 100 of interest.

This is designed to go in your Personal Macro Workbook, so you can use it again and again.

Whenever you run it, you will be prompted to select the PivotField you want to filter (unless you have already selected a PivotField), and then you will be prompted to select the range of search terms, and then Excel will very quickly filter your PivotTable to match those terms.

It's pretty quick: here's how it performs on a PivotField containing 20,000 random alphanumeric strings:
Filter on 100 search terms: 7 seconds
Filter on 10,000 search terms: 1 minute, 30 seconds
Filter on 19,900 search terms: 4 seconds

The usual approach to a task like this is to just iterate through each pivotitem in the PivotItems collection, and check if that PivotItem is in the list of search terms. If it is, you make the PivotItem visible, otherwise you hide it. But on very large pivots, that can take ages because changing the .visible property is very slow, and under this approach, you are going to set that .visible property for every singe PivotItem in the PivotField.

A faster approach is to make all PivotItems visible, and then iterate through the PivotItems and then change the .visible status to FALSE if that PivotItem is NOT in the list of search terms. So if you only have say 19,900 search terms, then you will only use the .visible method 10 times. Much faster. But if you only have 10 items in your filter list, then you'll be setting that .Visible status 19,900 times. Very slow.

So my code works out whether it’s quickest to:

• make all items visible in the pivot, and then hide just the PivotItems that don’t match the filter terms; or
• hide all but one items in the PivotField, and unhide just the PivotItems that do match the filter terms

Hiding all but one items is tricky - you can't do it directly except in Page Fields, and
that method doesn't let you select multiple items anyway. Plus, as soon as you drag a page field with just one item showing to a row field, Excel clears the filter, so that all items are visible again. And you're back to square 1 again.

My code gets around this with a clever trick I discovered:
* it makes a temp copy of the pivot, and make the pf of interest a page field
* it then turns off multiple items and selects just one PivotItem - which pretty much instantly hides all the other items
* I then connect this temp pivot to the original pivot with a slicer. This very quickly syncs up the field on the original pivot with the temp copy, so that only one field is showing.


It would be good if you could take this code for a spin, and see if you turn up any issues. Note that you need the FilterPivot() routine to launch it.

Look forward to comments, feedback, suggestions.
Note that I'm using a dictionary with late binding. Had some issues with collections early on, which is why I switched to using a dictionary.
Regards,
Jeff

 

[jeffreyweir]

I'll have to upload the code in a workbook, because it's too long to post here. Be up shortly.

Okay, sample file attached above.

 

[NARAYANK991]

Hi Jeff ,

Just for comparison purposes , do you have the times for the 3 cases , using the usual approach ?

Narayan

 

[jeffreyweir]

Good point. I have some timings from some earlier code, but because I've been making tweaks here and there, it's not really comparing apples with apples. I'm in the process of taking this new code and retrofitting the old approaches into it, so we can do a fair trial.

 

[jeffreyweir]

So I tried an efficient tweak of the 'traditional' method:

• I added all filter terms to a dictionary
• I then added all PivotItems to a dictionary
• If this caused an error, I know that this PivotItem IS in my list of filter terms. In this case, I check what the current visible status is of the PivotItem. If it's NOT visible, I make it visible. If it IS visible, I do nothing
• IF this didn't cause an error, I know that this PivotItem IS NOT in my list of filter terms. In this case, I check what the current visible status is of the PivotItem. If it's visible, I hide it. If it's already hidden, I do nothing

So it's quite efficient in that it only changes the .visible status of the PivotItem if it has to. Which is good, because this is the bottleneck. And the general approach of using a Dictionary is very efficient, compared to other ways I've seen on line that use say applicaiton.match to check the PivotItem against a variant array or (far worse) against the original FilterTerms range in the worksheet.

On a pivot of 20,000 items that currently has all items visible, here's how this 'traditional tweaked' method performed:

• It took 5 minutes to filter on a list of 100 terms. My beta code takes 5 seconds. When I ran it again without clearing the filter, it took 2 minutes . That faster time is because it didn't have to change the .visible status of any items at all, because they were already in the 'correct' state after the last run. But it still had to check them all. I was still surprised it took that long. Presumably while checking the visible status is a lot quicker than changing the status, it's still quite time consuming compared to say adding pivotitems to a dictionary.
• It took 3.5 minutes to filter on a list of 10,000 terms, compared with 1.5 minutes for my beta code . The shorter time compared to the first test case is because it only had to set the .visible status of half the pivot items. . It took 2 minutes when I ran it again without clearing the filter, same as before. That's what I would expect.
• It took 2 minutes to filter on a list of 19900 items, compared with 3 seconds for my beta code. It took just under two minutes when I ran it again without clearing the filter, same as before.

So my beta code is blazingly fast by comparison if you are filtering on either just a few items, or nearly all of them. And my beta code is over twice as fast by comparison if you are looking for half the items.

Will add additional routines to my test workbook as time permits so you can try different approaches.