Log In
Home
Support
Demos
Documentation
Blogs
Training
Webinars
[Expand]General Information
[Collapse]WinForms Controls
  Prerequisites
 [Expand]What's Installed
 [Expand]Build an Application
 [Collapse]Controls and Libraries
  [Expand]Forms and User Controls
  [Expand]Messages, Notifications and Dialogs
  [Expand]Editors and Simple Controls
  [Expand]Ribbon, Bars and Menu
  [Expand]Application UI Manager
  [Expand]Docking Library
  [Expand]Data Grid
  [Expand]Vertical Grid
  [Collapse]Pivot Grid
   [Expand]Getting Started
   [Expand]Fundamentals
   [Expand]Binding to Data
   [Collapse]Data Shaping
    [Expand]Summarization
     Grouping
    [Expand]Sorting
    [Collapse]Filtering
      Filtering Overview
      Group Filtering
      Prefilter
     [Expand]Filtering by Summaries
     [Expand]OLAP Filtering Specifics
    [Expand]Editing
     Data Formatting
   [Expand]Data Analysis
   [Expand]Layout
   [Expand]Focus and Navigation
   [Expand]Printing and Exporting
   [Expand]Appearance
   [Expand]Design-time Features
   [Expand]Miscellaneous
   [Expand]UI Elements
   [Expand]End-User Capabilities
   [Expand]Examples
   [Expand]Member Tables
  [Expand]Tree List
  [Expand]Chart Control
  [Expand]Diagrams
  [Expand]Gauges
  [Expand]Map Control
  [Expand]Scheduler
  [Expand]Spreadsheet
  [Expand]Rich Text Editor
  [Expand]Spell Checker
  [Expand]Form Layout Managers
  [Expand]Navigation Controls
  [Expand]Printing-Exporting
  [Expand]PDF Viewer
   Reporting
  [Expand]Snap
  [Expand]TreeMap Control
 [Expand]Common Features
  Get More Help
 [Expand]API Reference
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]ASP.NET Core Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Document Server
[Expand]Reporting
[Expand]Report Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

Filtering Overview

Filtering allows you to display only specific records within the Pivot Grid. You can enable end-users to apply filtering to certain fields using filter popups or complex filter criteria using the Filter Editor.

This topic describes how to apply filtering to filter, column and row field values. For details on how to apply filtering to summary values displayed in the Data area, see Filtering by Summaries.

Expanded Filter Popup

The filter popup allows end-users to filter certain filter, column or row fields. To apply filtering to a specific field, an end-user clicks the filter icon () within the column header.

The Pivot Grid supports two types of filter popups: Excel-style and Classic.

Example

Description

Excel-style

The Excel-style filter popup's content depends on the type of data the related field displays. In the "Values" tab, end-users can select specific field values from the Pivot Grid.

The "Filters" tab supplies users with additional options related to the field type. For example, when filtering a string field, you can show only those records that begin with 'C':

Filters applied using the Excel-style filter popup are displayed in the Filter Panel and can be changed in the Filter Editor dialog, which allows end-users to apply complex filter conditions.

Note

The Excel-style cannot be used in the following cases:

Classic

The Classic filter popup displays unique values stored in a field's underlying data source. It also displays a toolbar that allows end-users to control various filtering options.

To learn more about the toolbar functionality, see Filter Button and Drop-Down.

To specify the filter popup type globally, use the PivotGridOptionsFilterPopup.FieldFilterPopupMode property. The code below shows how to use Excel-style filter popups for all fields:

To specify the filter popup type for a specified field, use PivotGridFieldOptionsEx.FieldFilterPopupMode:

To hide filter buttons from all field headers, set the PivotGridOptionsCustomization.AllowFilter property to false. You can also do this for individual fields using their PivotGridFieldOptions.AllowFilter properties.

The table below lists the main members related to filter popups.

Common Popup API

Description

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 remove and modify items within Excel-style filter popups, as well as add custom items that apply specific filtering conditions.
PivotGridControl.FilterPopupExcelPrepareTemplate Allows you to replace templates used by Excel-style filter popups and external editors generated using Filtering UI Context.
PivotGridControl.FilterPopupExcelQueryFilterCriteria Allows you to customize a filter criteria applied using Excel-style filter popups.
PivotGridControl.ShowFilterPopupExcel Allows you to customize the Excel-style filter popup (for instance, to hide specific filtering 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.

Expanded Filter Editor

The Pivot Grid allows end-users to apply complex filter conditions at runtime using the Filter Editor dialog. End-users can invoke a Filter Editor using the Show Filter context menu command or the Edit Filter button in the Filter Panel.

This invokes the Filter Editor dialog that allows end-users to build complex filter criteria with an unlimited number of filter conditions combined by logical operators.

The Filter Panel at the bottom of the Pivot Grid displays the resulting filter sting and provides additional capabilities to work with the applied filter (such as toggling or resetting filtering). Note that filtering applied to specific fields can be changed later in the Excel filter popup.

You can prevent end-users from invoking a Filter Editor by disabling the PivotGridOptionsCustomization.AllowPrefilter option. In this instance, the Show Prefilter context menu item is hidden, and the Filter Editor can only be invoked in code.

The table below lists main members related to the Filter Editor.

API

Description

PivotGridOptionsCustomization.AllowPrefilter Gets or sets whether end-users are allowed to invoke the Prefilter.
PivotGridFieldOptions.ShowInPrefilter Gets or sets whether the field is shown in Prefilter.
PivotGridControl.ActiveFilter Provides access to settings of the filter applied to the PivotGridControl's data.
PivotGridControl.ActiveFilterCriteria Gets or sets the filter criteria applied to the PivotGridControl's data.
PivotGridControl.ActiveFilterString Gets or sets a string that specifies the filter criteria applied to the PivotGridControl's data.
PivotGridControl.ActiveFilterEnabled Gets or sets whether to apply the filter criteria specified using the ActiveFilterCriteria or ActiveFilterString.
PivotGridControl.PrefilterCriteriaChanged Fires when the Prefilter's criteria are changed.

Expanded Filter Panel

The Filter Panel is displayed at the bottom of the Pivot Grid and provides additional capabilities to work with the applied filter.

The following capabilities are available for end-users:

  • A cross button to remove filtering.
  • A checkbox to toggle filtering.
  • The Edit Filter button which invokes the Filter Editor for changing the filter criteria.

Expanded Filtering UI Context

The Pivot Grid allows you to use the Filtering UI Context that produces a set of editors end-users can utilize to filter displayed data. Filters applied using these editors are synchronized with filters applied using the Excel-style filter popup. You can see the Excel Style Filtering demo to see a sample filtering UI generated for the Pivot Grid.

Refer to the Filtering UI Context for more information.

Expanded Filtering in Code

The Pivot Grid provides an API that allows you to apply complex filter criteria or filter individual fields.

  • Create Complex Filter Criteria

    Use the PivotGridControl.ActiveFilterCriteria or PivotGridControl.ActiveFilterString properties to apply complex filtering in code. When you create a filter expression in code, use the PivotGridFieldBase.PrefilterColumnName property to refer to a field.

    The example below shows how to apply complex filter criteria in code using the PivotGridControl.ActiveFilterCriteria property.

    See the Criteria Language Syntax to learn more about filter criteria syntax.

    Note

    You cannot apply complex filtering to data fields. If a data field is used in a filter criterion that has been specified in code, an error message is displayed within the Filter Panel. This message also appears if a filter criterion contains a field name that does not exist. Use the BaseFilter.IsValid property to determine whether the filter criteria is valid or not.

  • Apply Filtering to Individual Fields

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

How would you rate this topic?​​​​​​​