FilterContext Property

expression.FilterContext

expression    Required. An expression that returns a PivotField object.

Remarks

When you apply a conditional filter to a field, the filter can apply to that field, or one of its parent fields in the field set. For example, assume that your PivotTable list contains a field set named Store. The Store field set contains the following fields: Country, Region, City, and Store Number. The Country field is a parent to the Region field, the Region field is a parent to the City field, and the City field is a parent to the Store Number field.

Now you want to find the top five most profitable cities. If you set this property to the City field, then the top five most profitable cities will be displayed in the PivotTable list. If you set this property to the Region field, then the top five cities will be returned for each region.

Setting this property to a child of the field that you are applying a conditional filter to will result in a run-time error. For example, you cannot set this property to the Store Number field when you are applying a conditional filter to the City field.

Example

This example displays the least profitable city in each state.

Sub LeastProfitableByState()

    Dim ptView
    Dim ptConstants
    Dim fldFilterField

    Set ptConstants = PivotTable1.Constants

    ' Set a variable to the active view of the PivotTable list.
    Set ptView = PivotTable1.ActiveView

    ' Set a variable to the field that is to be filtered.
    Set fldFilterField = PivotTable1.ActiveData.RowAxis.Fields("Store City")

    ' Filter the stores based on profit.
    Set fldFilterField.FilterOn = ptView.Totals("Profit")

    ' Set the function used to filter the stores.
    fldFilterField.FilterFunction = ptConstants.plFilterFunctionBottomCount

    ' Display the least profitable store.
    fldFilterField.FilterFunctionValue = 1

    ' Set the context of the filter. Although we are filtering based on
    ' the Store City field, setting the filter context to the Store State
    ' field means that the least profitable store from each state will be
    ' displayed.
    Set ptView.FieldSets("Store").Fields("Store City").FilterContext = _
               ptView.FieldSets("Store").Fields("Store State")

End Sub