How to: Locate a Column (Row) Header By Its Summary Values

• Aug 01, 2019
• 3 minutes to read

The following example demonstrates how to handle the CustomFieldValueCells event to locate a specific column/row header identified by its column’s/row’s summary values.

In this example, a predicate is used to locate a column that contains only zero summary values. The column header is obtained by the event parameter’s FindCell method, and then removed via the Remove method.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/how-to-locate-field-value-cells-matching-a-particular-condition-e2772

Form1.cs

using System;
using System.Globalization;
using System.Windows.Forms;
using DevExpress.XtraPivotGrid;

namespace XtraPivotGrid_FindCells {
    public partial class Form1 : Form {
        public Form1() {
            InitializeComponent();
            pivotGridControl1.CustomFieldValueCells += 
                new PivotCustomFieldValueCellsEventHandler(pivotGrid_CustomFieldValueCells);
        }
        void Form1_Load(object sender, EventArgs e) {
            PivotHelper.FillPivot(pivotGridControl1);
            pivotGridControl1.DataSource = PivotHelper.GetDataTable();
            pivotGridControl1.BestFit();
        }

        // Handles the CustomFieldValueCells event to remove columns with
        // zero summary values.
        protected void pivotGrid_CustomFieldValueCells(object sender,
                                     PivotCustomFieldValueCellsEventArgs e)
        {
            PivotGridControl pivot = sender as PivotGridControl;
            if (pivot.DataSource == null) return;
            if (radioGroup1.SelectedIndex == 0) return;

            // Obtains the first encountered column header whose column
            // matches the specified condition, represented by a predicate.
            FieldValueCell cell = e.FindCell(true, new Predicate<object[]>(

                // Defines the predicate returning true for columns
                // that contain only zero summary values.
                delegate(object[] dataCellValues)
                {
                    foreach (object value in dataCellValues)
                    {
                        if (!object.Equals((decimal)0, value))
                            return false;
                    }
                    return true;
                }));

            // If any column header matches the condition, this column is removed.
            if (cell != null) e.Remove(cell);
        }
        void pivotGridControl1_FieldValueDisplayText(object sender,
                                        PivotFieldDisplayTextEventArgs e)
        {
            PivotGridControl pivot = sender as PivotGridControl;
            if (e.Field == pivot.Fields[PivotHelper.Month])
            {
                e.DisplayText = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName((int)e.Value);
            }
        }
        void radioGroup1_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.pivotGridControl1.LayoutChanged();
        }        
    }
}

Form1.vb

Imports System
Imports System.Globalization
Imports System.Windows.Forms
Imports DevExpress.XtraPivotGrid

Namespace XtraPivotGrid_FindCells
    Partial Public Class Form1
        Inherits Form

        Public Sub New()
            InitializeComponent()
            AddHandler pivotGridControl1.CustomFieldValueCells, AddressOf pivotGrid_CustomFieldValueCells
        End Sub
        Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
            PivotHelper.FillPivot(pivotGridControl1)
            pivotGridControl1.DataSource = PivotHelper.GetDataTable()
            pivotGridControl1.BestFit()
        End Sub

        ' Handles the CustomFieldValueCells event to remove columns with
        ' zero summary values.
        Protected Sub pivotGrid_CustomFieldValueCells(ByVal sender As Object, 
                            ByVal e As PivotCustomFieldValueCellsEventArgs)
            Dim pivot As PivotGridControl = TryCast(sender, PivotGridControl)
            If pivot.DataSource Is Nothing Then
                Return
            End If
            If radioGroup1.SelectedIndex = 0 Then
                Return
            End If

            ' Obtains the first encountered column header whose column
            ' matches the specified condition, represented by a predicate.
            Dim cell As FieldValueCell = e.FindCell(True,
                            New Predicate(Of Object())(Function(dataCellValues() As Object)
                ' Defines the predicate returning true for columns
                ' that contain only zero summary values.
                For Each value As Object In dataCellValues
                    If Not Object.Equals(CDec(0), value) Then
                        Return False
                    End If
                Next value
                Return True
            End Function))

            ' If any column header matches the condition, this column is removed.
            If cell IsNot Nothing Then
                e.Remove(cell)
            End If
        End Sub
        Private Sub pivotGridControl1_FieldValueDisplayText(ByVal sender As Object,
                       ByVal e As PivotFieldDisplayTextEventArgs) Handles pivotGridControl1.FieldValueDisplayText
            Dim pivot As PivotGridControl = TryCast(sender, PivotGridControl)
            If e.Field Is pivot.Fields(PivotHelper.Month) Then
                e.DisplayText = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(CInt((e.Value)))
            End If
        End Sub
        Private Sub radioGroup1_SelectedIndexChanged(ByVal sender As Object,
                            ByVal e As EventArgs) Handles radioGroup1.SelectedIndexChanged
            Me.pivotGridControl1.LayoutChanged()
        End Sub
    End Class
End Namespace