Pivot Table - Hide Pivot Items unless...

[JMH022]

I have been searching and can't find what I need...Perhaps the gurus can help.

I have a pivot table that has a BIG list of product codes. I am trying to use VBA to help me quickly filter my pivot table to hide all product codes EXCEPT the 5 I want to see. Scrolling through the drop-down list to select the 5 out of 1,000 codes is a royal pain.

Here is a sample of the code (kind-of) I am trying to create:

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Product Code")
.PivotItems("All").Visible = False ' All is not in the list. I need to know if there is a way to have the code de-select all codes

.PivotItems("PROD1").Visible = True
.PivotItems("PROD2").Visible = True
.PivotItems("PROD3").Visible = True
.PivotItems("PROD4").Visible = True
.PivotItems("PROD5").Visible = True
End with

I'd appreciate any help...

 

[Fazza]

Hi, Jim

What if when the pivot table is created, filtering for only the five you want? So an SLQ clause : WHERE [Product Code] LIKE 'PROD[1-5]'

Of if you didn't want to hard code it, have a separate table listing those five codes. The SQL could then be like
SELECT B.*
FROM BigTable B, FilterList F
WHERE B.[Product Code] = F.[Product Code]

To stay in VBA, set the five you want visible then loop through every pivot item in the field.pivotitems and hide all the others. For speed, before looping set manualupdate to true and set it back to false after

regards

 

[JMH022]

Sorry for the delay in getting back.

Not sure what you mean by filtering when the PT is created. Where/how do I do this?

I am more familiar with VBA than I am SQL. You mentioned:

To stay in VBA, set the five you want visible then loop through every pivot item in the field.pivotitems and hide all the others. For speed, before looping set manualupdate to true and set it back to false after

This is what I am trying to do, but the list of pivot items may change from day to day. I only want to see a specific 5 codes every time I refresh the table, so I need to figure out how to de-select all the pivot fields and select only the 5 I need. Can you help me out?

 

[Fazza]

hi, Jim

When the pivot table is created, its source data can be two tables. SQL is used to define the way the two tables are joined. One table can be your current 'big' table and the other table the five codes you want to see in the results. Earlier I called the small table the filter table. By making the table join like I wrote above, the dataset into the pivot table will be only the records in the big table whose product code is in the big table. This is a query just like in Access.

With this set up, each day/time you change the inputs in the filter table (and there can be zero to whatever entries, not always 5) and then refresh the pivot table. It will only have the data you want in it.

To set it up, create the two tables - big data and filter data - with normal defined names. In a new workbook start the pivot table wizard (ALT-D-P) and choose the external data option at the first step. Then Excel files, OK, browse for your files, OK, select a table name or both, continue until you have the option to edit in MS Query, change the SQL, see the dataset, 'open door' icon to exit MS Query & complete the pivot table.

On the other hand, staying with your VBA.

To stay in <ACRONYM title="visual basic for applications">VBA</ACRONYM>, set the five you want visible then loop through every pivot item in the field.pivotitems and hide all the others. For speed, before looping set manualupdate to true and set it back to false after

Note that this is just slightly different from what you currently have coded. I can have a look at this when I get some time - probably today. Or if you search old posts there will be examples.

 

[Fazza]

sorry, I'm not going to get to this today as am rather busy - so it will be next week before I can look at it

I hope it is clear that your current code is

hide everything
make some visible

In my experience this won't work cause you can't hide everything

that is why I suggested the different approach, (that maybe you can code)

make the ones you want visible
hide the others

 

[Jerry Sullivan]

Jim,

Here are two threads that have code that takes the approach Fazza described of show-then-hide instead of hide-then-show. (They actually make 1 item you want visible, then step through the rest and only change their state if needed).

This example filters a pivottable field to hide all items except those in an array or range.
Help tweaking vba to filter pivot based off range

This example is similar, but allows the use of wildcard patterns (like "PROD[1-5]")
http://www.mrexcel.com/forum/excel-...-pivottable-variable-filters.html#post3093626
(it's a long thread - just look at posts #3-4)

Fazza, Learning how to use SQL to control pivots as you describe is at the top of my to-do list.
Could you point Jim and me to a reference or article that explains the basics of using SQL on a workbook data source?
When I have tried to find that on the web, the instructions seem to always relate to making a data connection to a server instead of a workbook.

 

[Fazza]

hi, Jerry. Thanks for the links to old posts.

I don't know of reference articles for using SQL on workbook data sources. I do know I've posted heaps of examples in the past. Google should find plenty by me, include in the search site:mreexcel.com

Here is one that seems relevant
http://www.mrexcel.com/forum/excel-questions/519932-pivot-tables.html

This thread has related info & some links, you've probably seen it already
http://www.mrexcel.com/forum/excel-questions/315768-creating-pivot-table-multiple-sheets.html

hth

 

[Jerry Sullivan]

Fazza, That's a big help.

I've tried to follow SQL instructions that you have posted in the past and I think the problem has been that we use different versions of Excel.

The first link you suggested seems to address xl2010. I'm eager to try that.

I don't want to hijack Jim's thread. If I have any questions I'll start a new thread.

Thanks so much!

 

[JMH022]

Thank you both for your input. I will make some time to work through these and will report back with my results.