Manually Filtering to Two or More Items in a Pivot Field

In addition to setting up a calculated pivot item to display the total of a couple of products that make up a dimension, it is possible to manually filter a particular PivotField.

For example, I have one client who sells shoes. In the report showing sales of sandals, he wants to see just the stores that are in warm weather states. The code to hide a particular store is

PT.PivotFields("Store").PivotItems("Minneapolis").Visible = False

You need to be very careful to never set all items to False or the macro will end with an error. This tends to happen more than you would expect. An application may first show products A and B, then on the next loop show products C and D. If you attempt to make A and B not visible before making C and D visible, you will be in the situation of having no products visible along the PivotField, which causes an error. To correct this, always loop through all PivotItems, making sure to turn them back to Visible before the second pass through the loop.

The process of manually filtering records seems to offer a way to filter a particular PageField to two items. Let's say that you want to produce a pivot report showing Revenue, Profit, and GP% by Region for the sum of product lines ABC and DEF.

Conceptually, you would follow these steps in the user interface:

1. Produce a pivot table with Region down the side, Products across the top, and Revenue in the data field.

2. Manually filter the Product PivotField to include only products ABC and DEF.

3. Move the Product page field to the page area.

4. Add Profit and GP% to the column field area.

0 0

Post a comment