Filtering in Code

Aug 01, 2019
4 minutes to read

In addition to end-user filtering, ASPxPivotGrid provides the capability to specify filter conditions in code.

Filter Field Values 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

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

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();
                }
            }
        }
    }
}

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

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

Controls

Tools

Controls and Extensions

Tools

Maintenance Mode

Controls

Tools

Controls and Extensions

Tools

Maintenance Mode

Filter Field Values in Code

Example: How to Filter Data in Code