Skip to main content

Pop-up Filter

  • 7 minutes to read

Use the pop-up filter to filter fields in column, row, and filter header areas.

Click the filter icon (Pivot_FilterIcon) in the column header to filter a specific field.

The Pivot Grid supports Excel-style and Classic pop-up filters.

Run Demo: Excel Style Filtering Run Demo: Classic Filter Popup

To specify the filter type for all the controls in the application, use the static WindowsFormsSettings.ColumnFilterPopupMode option.

To specify the filter type for all Pivot Grid fields, set the PivotGridOptionsFilterPopup.FieldFilterPopupMode option to FieldFilterPopupMode.Excel.

pivotGridControl.OptionsFilterPopup.FieldFilterPopupMode = FieldFilterPopupMode.Excel;

To specify a field‘s filter type, use PivotGridFieldOptionsEx.FieldFilterPopupMode:

fieldTrademark.Options.FieldFilterPopupMode = FieldFilterPopupMode.Excel;

Note

You cannot use Excel-style filters in OLAP mode.

Example

Description

Excel-style

FilterPopup_Excel

A filter’s content depends on the type of data in the related field. Select field values in the “Values” tab and specify additional options related to the field type in the “Filters” tab. For example, when you filter a string field, you can show only those records that begin with ‘C’:

WinPivot_Filtering_Excel_TextFilters

In Asynchronous Mode, Excel-style pop-up filters apply a filter only when you click Ok and close the popup. Filters applied in the Excel-style pop-up filter are displayed in the Filter Panel and can be changed in the Filter Editor. This allows you to apply complex filter criteria.

Classic

FilterPopup_Classic

The Classic pop-up filter displays unique values from a field’s underlying data source. Use buttons on toolbar to control filtering options.

VisualElements_FilterDropdown_Toolbar

See Filter Button and Popup for more information about toolbar functionality.

To hide filter buttons in all field headers, set the PivotGridOptionsCustomization.AllowFilter property to false, or use a field’s PivotGridFieldOptions.AllowFilter property for an individual field.

For more that one field in a column / row area, you can simulate group filter behavior and hide filter values that are hidden by the another filters. Use the following properties for corresponding pop-up filter’s mode:

Pop-up Filter Mode Property
Excel-style PivotGridFieldOptionsFilter.PopupExcelFilterShowAllValues
Classic PivotGridOptionsFilterBase.ShowOnlyAvailableItems

Filter Popup API

The following tables list the main members related to pop-up filters:

Common Popup API Description
WindowsFormsSettings.ColumnFilterPopupMode Gets or sets the default display mode of column filter dropdowns in all GridControls, TreeLists and PivotGridControls in the current application.
PivotGridOptionsFilterPopup.FieldFilterPopupMode Gets or sets the field’s filter popup mode for all fields.
PivotGridFieldOptions.AllowFilter Gets or sets whether an end-user can apply a filter to the current field.
PivotGridOptionsCustomization.AllowFilter Gets or sets whether filter buttons are displayed within field headers.
PivotGridFieldOptions.AllowFilterBySummary Gets or sets whether end-users are allowed to filter pivot grid data by summaries calculated against the current data field.
PivotGridOptionsCustomization.AllowFilterInCustomizationForm Gets or sets whether filtering can be applied to fields via the Customization Form.
PivotGridAppearances.HeaderFilterButton Gets the appearance settings used to paint filter buttons.
PivotGridAppearances.HeaderFilterButtonActive Gets the appearance settings used to paint the filter buttons displayed within the field headers that are involved in filtering.
PivotGridOptionsView.HeaderFilterButtonShowMode Gets or sets how filter buttons are rendered.
PivotGridControl.FieldFilterChanging Allows you to customize the filter that is being applied or cancel filtering.
PivotGridControl.FieldFilterChanged Occurs after a specific field’s filter criteria was changed.
Excel-style Popup API Description
PivotGridField.ShowExcelFilterPopup Invokes the Excel-style filter popup for the current field.
PivotGridField.OptionsFilter Provides access to the field’s filtering options.
PivotGridControl.FilterPopupExcelCustomizeTemplate Allows you to customize templates used by Excel-style filter popups and external editors generated using Filtering UI Context.
PivotGridControl.FilterPopupExcelData Allows you to add, remove, and modify data values and customize predefined filters in the Excel style pop-up filter menus. Filter items added manually on this event must be unique and sorted.
PivotGridControl.FilterPopupExcelPrepareTemplate Allows you to replace templates used by Excel-style filter popups and external editors generated using Filtering UI Context.
PivotGridControl.FilterPopupExcelQueryFilterCriteria Fires when a filter criteria is about to be applied to data and allows you to customize the filter criteria.
PivotGridControl.ShowFilterPopupExcel Allows you to customize the Excel-style filter popup (for instance, to hide specific filter conditions from the Filters tab).
Classic Popup API Description
PivotGridField.ShowFilterPopup Invokes the Classic filter popup for the current field.
PivotGridControl.OptionsFilterPopup Provides access to options that define the Classic filter popup’s appearance and behavior.
PivotGridFieldOptions.GroupFilterMode Gets or sets the filtering mode used for the field’s owner group.
PivotGridFieldOptionsEx.IsFilterRadioMode Gets or sets whether an end-user is allowed to select only a single item in the filter drop-down.
PivotGridField.CanFilterRadioMode Gets whether end-users are allowed to select only a single item in the field’s filter drop-down.
PivotGridField.DropDownFilterListSize Gets or sets the width and height of the field’s filter dropdown.
PivotGridControl.CustomFilterPopupItems Allows you to customize items displayed in the Classic filter popup.
PivotGridControl.GroupFilterChanged Occurs after a group filter condition was changed.
PivotFieldDisplayTextEventArgs.IsPopulatingFilterDropdown Gets whether the current event is called to populate the filter dropdown.
PivotGridOptionsFilterBase.ShowOnlyAvailableItems Gets or sets whether filter items that cannot be displayed because of filtering applied to other fields should be hidden.

Filter UI Context

You can use editors to filter data in the Pivot Grid. Filter values from editors are synchronized with filter values from the Excel-style pop-up filter. See an example of a UI filter for the Pivot Grid in the Excel-style Filtering demo.

Refer to the Filtering UI Context for more information.

Example

The example below shows how to apply filtering to the ‘fieldTradeMark’ field by adding ‘Chevrolet’ or ‘Chrysler’ to the PivotGridFieldBase.FilterValues collection.

pivotGridControl.BeginUpdate();
try {
    fieldTrademark.FilterValues.Clear();
    fieldTrademark.FilterValues.FilterType = PivotFilterType.Included;
    fieldTrademark.FilterValues.Add("Chevrolet");
    fieldTrademark.FilterValues.Add("Chrysler");
}
finally {
    pivotGridControl.EndUpdate();
}

Group Filter

Use a group filter to filter values in grouped fields.

Run Demo: Excel Style Filtering Run Demo: Group Filter

Each filter value corresponds to a unique value stored in a Pivot Grid Control’s underlying data source. The field filter filters values of individual fields and the group filter filters hierarchically arranged fields. The image below shows a PivotGridControl with a Column Header Area that contains ‘Year’, ‘Month’, and ‘Day’ fields. These fields use Excel-style pop-up filters (PivotGridOptionsFilterPopup.FieldFilterPopupMode is FieldFilterPopupMode.Excel).

xtrapivotgrid_FieldFilterWithGroup

Group Filter Mechanism

Use the PivotGroupFilterValues.Values property to access a collection of group filter values. Each group filter value corresponds to a field value from a grouped field.

Group filter values are arranged hierarchically. The PivotGroupFilterValues.Values collection contains values from the first field in the group (first-level values). Each first-level value corresponds to a collection of values from the second field in the group (child values), etc.

Use group filter values to define the data source records that the PivotGridControl should process. Group filter values correspond to data source records in the following cases:

  • The data source record contains the group filter value in a corresponding field.
  • The data source record contains ancestor values of the group filter value in corresponding fields.
  • The group filter value does not contain child values.

Set the PivotGroupFilterValues.FilterType property to PivotFilterType.Included, to process data source records that correspond to a group filter value. The PivotFilterType.Excluded value is used to process data source records that do not correspond to any group filter value.

The image below shows a drop-down filter with the ‘Year - Quarter - Day’ group. The group’s filter values collection contains a ‘2018’ value that has ‘Quarter 3’ and ‘Quarter 4’ child values, and the filter type is set to PivotFilterType.Excluded. As a result, the ‘Quarter 3’ column for 2019 is displayed, and the column that corresponds to the 3rd and the 4th quarters of 2018 is hidden.

xtrapivotgrid_GroupFilter

Use Group Filter in Code

Use the PivotGridGroup.FilterValues property to access group filter conditions in the PivotGroupFilterValues object. To apply a group filter in code, do the following:

Wrap the code in the PivotGridControl.BeginUpdate and PivotGridControl.EndUpdate method calls to avoid unnecessary control updates when you customize filter properties.

You can use the PivotGridFieldOptions.GroupFilterMode property to specify the pop-up filter’s availability for individual field in a group. The default Tree mode enables the group filter. Set the PivotGridFieldOptions.GroupFilterMode property to List to enable the field filter. In List mode, you can simulate group filter behavior and hide filter values that are hidden by the another filters. Use the following properties for corresponding pop-up filter’s mode:

Pop-up Filter Mode Property
Excel-style PivotGridFieldOptionsFilter.PopupExcelFilterShowAllValues
Classic PivotGridOptionsFilterBase.ShowOnlyAvailableItems

End users can use a group drop-down filter to apply, remove, and change group filters.

Excel-style Classic
pivotgrid_GroupFilterMode_ExcelGroupFilterDropdown pivotgrid_GroupFilterMode_GroupFilterDropdown

Example

How to: Implement Group Filter for the Pivot Grid