Filtering in Code

• May 05, 2018
• 3 minutes to read

In addition to end-user filtering, PivotGridControl 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 PivotGridField.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 FieldFilterValues.FilterType property. This property specifies whether filter values should be displayed in or hidden from the pivot grid.

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

Example

This example shows how to apply a filter to a field. For this, a new FilterFieldValues method was created.

The first condition will select records which contain ‘UK’ in the ‘fieldCountry’ field. The second condition will filter the ‘Category’ field to display only four categories: ‘Beverages’, ‘Condiments’, ‘Seafood’, and ‘Produce’.

Note

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

MainWindow.xaml.vb

Imports System.Windows
Imports System
Imports DevExpress.Xpf.PivotGrid
Imports System.Collections.Generic

Namespace WpfPivotGridFilterValues
    ''' <summary>
    ''' Interaction logic for MainWindow.xaml
    ''' </summary>
    Partial Public Class MainWindow
        Inherits Window

        Public Sub New()
            InitializeComponent()

            ' Filters values in the Country field using the created method.
            FilterFieldValues(fieldCountry,
                  New String() {"UK"}, FieldFilterType.Included)

            ' Filters values in the Category field using the created method.
            FilterFieldValues(fieldCategory,
                  New String() {"Beverages", "Condiments", "Seafood", "Produce"}, FieldFilterType.Included)
        End Sub

        ' Creates a new method that allows filtering field values.
        Private Function FilterFieldValues(ByVal field As PivotGridField,
                                           ByVal filterValues() As Object,
                                           ByVal filterType As FieldFilterType) As Object()
            ' Locks the control to prevent its excessive updates when multiple properties are modified.
            pivotGridControl1.BeginUpdate()
            Try
                ' Clears the filter value collection and adds object items to it.
                field.FilterValues.Clear()
                For Each filterValue As Object In filterValues
                    field.FilterValues.Add(filterValue)
                Next filterValue
            Finally
                ' Specifies that the control should only display records 
                ' which contain the specified values in the field.
                field.FilterValues.FilterType = filterType

                ' Unlocks the control.
                pivotGridControl1.EndUpdate()
            End Try
            Return filterValues
        End Function
    End Class
End Namespace

MainWindow.xaml

<Window x:Class="WpfPivotGridFilterValues.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:dxpg="http://schemas.devexpress.com/winfx/2008/xaml/pivotgrid"
        xmlns:dx="http://schemas.devexpress.com/winfx/2008/xaml/core"
        dx:ThemeManager.Theme="Office2013LightGray" 
        Title="Pivot Grid Filtering" 
        Height="260" Width="620" 
        xmlns:my="clr-namespace:WpfPivotGridFilterValues.nwindDataSetTableAdapters" 
        xmlns:my1="clr-namespace:WpfPivotGridFilterValues">
    <Window.Resources>
        <dx:TypedSimpleSource x:Key="TypedSimpleSource" AdapterType="my:SalesPersonTableAdapter" 
                              ContextType="my1:nwindDataSet" Path="SalesPerson">
            <dx:DesignDataManager.DesignData>
                <dx:DesignDataSettings RowCount="5" />
            </dx:DesignDataManager.DesignData>
        </dx:TypedSimpleSource>
    </Window.Resources>
    <Grid>
        <dxpg:PivotGridControl Name="pivotGridControl1" 
                               DataSource="{Binding Path=Data, Source={StaticResource TypedSimpleSource}}">
            <dxpg:PivotGridControl.Fields>
                <dxpg:PivotGridField Area="RowArea" Caption="Category" FieldName="CategoryName" 
                                     Name="fieldCategory" AreaIndex="0" />
                <dxpg:PivotGridField Area="ColumnArea" FieldName="Country" 
                                     Name="fieldCountry" AreaIndex="1" />
                <dxpg:PivotGridField Area="DataArea" FieldName="Extended Price" 
                                     Name="fieldExtendedPrice" AreaIndex="0" />
                <dxpg:PivotGridField Area="ColumnArea" Caption="Sales Person" 
                                     Name="pivotGridField2" FieldName="Sales Person" AreaIndex="2" />
            </dxpg:PivotGridControl.Fields>
        </dxpg:PivotGridControl>
    </Grid>
</Window>

MainWindow.xaml.cs

using System.Windows;
using System;
using DevExpress.Xpf.PivotGrid;
using System.Collections.Generic;

namespace WpfPivotGridFilterValues
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();

            // Filters values in the Country field using the created method.
            FilterFieldValues(fieldCountry, new string[] { "UK" }, FieldFilterType.Included);

            // Filters values in the Category field using the created method.
            FilterFieldValues(fieldCategory, new string[] { "Beverages", "Condiments", "Seafood", "Produce" }, 
                FieldFilterType.Included);
        }

        // Creates a new method that allows filtering field values.
        private object[] FilterFieldValues(PivotGridField field, object[] filterValues, 
            FieldFilterType filterType)
        {
            // Locks the control to prevent its excessive updates when multiple properties are modified.
            pivotGridControl1.BeginUpdate();
            try
            {
                // Clears the filter value collection and adds object items to it.
                field.FilterValues.Clear();
                foreach (object filterValue in filterValues)
                    field.FilterValues.Add(filterValue);
            }
            finally
            {
                // Specifies that the control should only display records 
                // which contain the specified values in the field.
                field.FilterValues.FilterType = filterType;

                // Unlocks the control.
                pivotGridControl1.EndUpdate();
            }
            return filterValues;
        }
    }
}