Pop-up Filter

  • 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.

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.

filter

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

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.

ShowOnlyAvailableItems_PivotGrid

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:

ShowOnlyAvailableFilterItems

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.

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

<%@ 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>
See Also