Pop-up Filter

• May 22, 2020
• 5 minutes to read

You can filter out a subset of rows and/or columns to display only those that meet certain criteria. Data can be filtered against single or multiple fields.

Tip

See the ASP.NET Pivot Grid Filter Popup demo.

Users can build simple filter criteria and apply them in a Filter Window. If a field’s PivotGridFieldOptions.AllowFilter option is enabled, a user can invoke this window by clicking a filter button displayed in a field header.

Enable the Filter mode to synchronize filters applied in the field filter and Filter Editor. You can customize the resulting filter string or clear it to reset the applied filters. Set the PivotGridWebOptionsFilter.FilterPanelMode to Filter to enable this mode.

A filter window contains unique field values. Use the PivotGridFieldBase.GetUniqueValues method to obtain these values.

Note

Users cannot change filters applied to data fields.

To create complex filter criteria, invoke the Filter Editor.

Availability

You can hide filter buttons from field headers to prevent users from applying or changing filters at runtime.

To hide filter buttons from all field headers, set the PivotGridOptionsCustomization.AllowFilter property to false. You can also do this for an individual field with the field’s PivotGridFieldOptions.AllowFilter property.

Note that this does not affect filters applied in code.

Display only Available Filter Items

The ShowOnlyAvailableItems property allows you to simulate group filter behavior without merging fields into a group. This property is not in effect when you open a filter window for grouped fields.

A filter drop-down window invoked for a particular field contains all the unique field values, although some of these values may actually be excluded from the pivot grid by filtering applied to other fields.

For example, a pivot grid contains two dimension fields: Category Name and Product Name.

If you select only one product category from the Category Name filter drop-down, it excludes all products related to other categories from the pivot grid. This means that whether you check or uncheck any of these products in the Product Name filter drop-down, this has no effect, since they are hidden by category filtering:

To remove such field values from filter drop-downs, set the PivotGridOptionsFilterBase.ShowOnlyAvailableItems property to true:

Filtering in Code

In addition to end-user filtering, ASPxPivotGrid allows you to specify filter conditions in code.

To display only the required data, add the necessary values to the PivotGridFieldBase.FilterValues collection. The type of the filter values should be the same as the value type of the current field.

The filtering functionality depends upon the PivotGridFieldFilterValues.FilterType property. This property specifies whether filter values should be displayed in or hidden from the pivot grid.

• To display filter values, set the PivotGridFieldFilterValues.FilterType property to PivotFilterType.Included. In this case, the PivotGridControl displays records that contain the specified filter values in a corresponding field.
• To hide filter values, set the PivotGridFieldFilterValues.FilterType property to PivotFilterType.Excluded. In this instance, the PivotGridField.FilterValues collection contains values that will be hidden within the pivot grid.

Pivot fields can contain null (or DBNull) values. The PivotGridFieldFilterValues.ShowBlanks property determines whether data source records that contain null values should be processed by the pivot grid. If this property is set to false, the pivot grid does not calculate summaries for these records.

Example: How to Filter Data in Code

This example shows how to apply a filter to a field. The filter selects records that contain 'Brazil' or 'USA' in the 'Country' field.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/how-to-apply-a-filter-e1867

Default.aspx (C#)

<%@ Page Language="C#" AutoEventWireup="true"
      CodeBehind="Default.aspx.cs" Inherits="ApplyFilter._Default" %>

<%@ Register Assembly="DevExpress.Web.ASPxPivotGrid.v10.2, Version=10.2.1.0,
    Culture=neutral, PublicKeyToken=b88d1754d700e49a"
    Namespace="DevExpress.Web.ASPxPivotGrid"
    TagPrefix="dx" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
       "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <dx:ASPxPivotGrid ID="ASPxPivotGrid1" runat="server" 
            DataSourceID="AccessDataSource1">
            <Fields>
                <dx:PivotGridField ID="fieldUnitPrice"
                    Area="DataArea" AreaIndex="0" 
                    FieldName="UnitPrice">
                </dx:PivotGridField>
                <dx:PivotGridField ID="fieldProductName"
                    Area="RowArea" AreaIndex="0" 
                    FieldName="ProductName">
                </dx:PivotGridField>
                <dx:PivotGridField ID="fieldQuantity"
                    Area="DataArea" AreaIndex="1" 
                    FieldName="Quantity">
                </dx:PivotGridField>
                <dx:PivotGridField ID="fieldCity" Area="ColumnArea" AreaIndex="2" 
                    FieldName="City">
                </dx:PivotGridField>
                <dx:PivotGridField ID="fieldRegion" Area="ColumnArea" AreaIndex="1" 
                    FieldName="Region">
                </dx:PivotGridField>
                <dx:PivotGridField ID="fieldCountry" Area="ColumnArea" AreaIndex="0" 
                    FieldName="Country">
                </dx:PivotGridField>
            </Fields>
        </dx:ASPxPivotGrid>
        <asp:AccessDataSource ID="AccessDataSource1" runat="server" 
            DataFile="~/App_Data/nwind.mdb" 
            SelectCommand="SELECT [UnitPrice], [ProductName],
             [Quantity], [City], [Region], [Country] FROM [Invoices]">
        </asp:AccessDataSource>
    </div>
    </form>
</body>
</html>

Default.aspx.cs

using System;
using DevExpress.Web.ASPxPivotGrid;

namespace ApplyFilter {
    public partial class _Default : System.Web.UI.Page {
        protected void Page_Load(object sender, EventArgs e) {
            if (!IsPostBack && !IsCallback) {
                PivotGridField field = ASPxPivotGrid1.Fields["Country"];
                // Locks the control to prevent excessive updates when multiple properties are modified.
                ASPxPivotGrid1.BeginUpdate();
                try {
                    // Clears the filter value collection and add two items to it.
                    field.FilterValues.Clear();
                    field.FilterValues.Add("Brazil");
                    field.FilterValues.Add("USA");
                    // Specifies that the control should only display the records 
                    // which contain the specified values in the Country field.
                    field.FilterValues.FilterType = DevExpress.XtraPivotGrid.PivotFilterType.Included;
                }
                finally {
                    // Unlocks the control.
                    ASPxPivotGrid1.EndUpdate();
                }
            }
        }
    }
}

Default.aspx.vb

Imports Microsoft.VisualBasic
Imports System
Imports DevExpress.Web.ASPxPivotGrid

Namespace ApplyFilter
    Partial Public Class _Default
        Inherits System.Web.UI.Page
        Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
            If (Not IsPostBack) AndAlso (Not IsCallback) Then
                Dim field As PivotGridField = ASPxPivotGrid1.Fields("Country")
                ' Locks the control to prevent excessive updates when multiple properties are modified.
                ASPxPivotGrid1.BeginUpdate()
                Try
                    ' Clears the filter value collection and add two items to it.
                    field.FilterValues.Clear()
                    field.FilterValues.Add("Brazil")
                    field.FilterValues.Add("USA")
                    ' Specifies that the control should only display the records 
                    ' which contain the specified values in the Country field.
                    field.FilterValues.FilterType = DevExpress.XtraPivotGrid.PivotFilterType.Included
                Finally
                    ' Unlocks the control.
                    ASPxPivotGrid1.EndUpdate()
                End Try
            End If
        End Sub
    End Class
End Namespace

Default.aspx (VB.NET)

<%@ Page Language="vb" AutoEventWireup="true"
      CodeBehind="Default.aspx.vb" Inherits="ApplyFilter._Default" %>

<%@ Register Assembly="DevExpress.Web.ASPxPivotGrid.v10.2, Version=10.2.1.0,
    Culture=neutral, PublicKeyToken=b88d1754d700e49a"
    Namespace="DevExpress.Web.ASPxPivotGrid"
    TagPrefix="dx" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
       "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <dx:ASPxPivotGrid ID="ASPxPivotGrid1" runat="server" 
            DataSourceID="AccessDataSource1">
            <Fields>
                <dx:PivotGridField ID="fieldUnitPrice"
                    Area="DataArea" AreaIndex="0" 
                    FieldName="UnitPrice">
                </dx:PivotGridField>
                <dx:PivotGridField ID="fieldProductName"
                    Area="RowArea" AreaIndex="0" 
                    FieldName="ProductName">
                </dx:PivotGridField>
                <dx:PivotGridField ID="fieldQuantity"
                    Area="DataArea" AreaIndex="1" 
                    FieldName="Quantity">
                </dx:PivotGridField>
                <dx:PivotGridField ID="fieldCity" Area="ColumnArea" AreaIndex="2" 
                    FieldName="City">
                </dx:PivotGridField>
                <dx:PivotGridField ID="fieldRegion" Area="ColumnArea" AreaIndex="1" 
                    FieldName="Region">
                </dx:PivotGridField>
                <dx:PivotGridField ID="fieldCountry" Area="ColumnArea" AreaIndex="0" 
                    FieldName="Country">
                </dx:PivotGridField>
            </Fields>
        </dx:ASPxPivotGrid>
        <asp:AccessDataSource ID="AccessDataSource1" runat="server" 
            DataFile="~/App_Data/nwind.mdb" 
            SelectCommand="SELECT [UnitPrice], [ProductName],
             [Quantity], [City], [Region], [Country] FROM [Invoices]">
        </asp:AccessDataSource>
    </div>
    </form>
</body>
</html>