How to: Hide Individual Rows and Columns

• Nov 13, 2018
• 6 minutes to read

The following example demonstrates how to hide particular rows and columns by handling the CustomFieldValueCellsevent. In this example, the event handler iterates through all row headers and removes rows that correspond to the “Employee B” field value, and that are not Total Rows.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/how-to-hide-particular-rows-and-columns-e2769

Form1.cs

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

namespace XtraPivotGrid_HidingColumnsAndRows {
    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
        // specific rows.
        protected void pivotGrid_CustomFieldValueCells(object sender,
                             PivotCustomFieldValueCellsEventArgs e) {

            PivotGridControl pivot = sender as PivotGridControl;
            if (pivot.DataSource == null) return;
            if (radioGroup1.SelectedIndex == 0) return;

            // Iterates through all row headers.
            for (int i = e.GetCellCount(false) - 1; i >= 0; i--) {
                FieldValueCell cell = e.GetCell(false, i);
                if (cell == null) continue;

                // If the current header corresponds to the "Employee B"
                // field value, and is not the Total Row header,
                // it is removed with all corresponding rows.
                if (object.Equals(cell.Value, "Employee B") &&
                    cell.ValueType != PivotGridValueType.Total)
                    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();
        }        
    }
}

Program.cs

using System;
using System.Collections.Generic;
using System.Windows.Forms;

namespace XtraPivotGrid_HidingColumnsAndRows {
    static class Program {
        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]
        static void Main() {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Form1());
        }
    }
}

Data.cs

using System.Data;
using DevExpress.XtraPivotGrid;

namespace XtraPivotGrid_HidingColumnsAndRows {
    public static class PivotHelper {
        public const string Employee = "Employee";
        public const string Widget = "Widget";
        public const string Month = "Month";
        public const string RetailPrice = "Retail Price";
        public const string WholesalePrice = "Wholesale Price";
        public const string Quantity = "Quantity";
        public const string Remains = "Remains";

        public const string EmployeeA = "Employee A";
        public const string EmployeeB = "Employee B";
        public const string WidgetA = "Widget A";
        public const string WidgetB = "Widget B";
        public const string WidgetC = "Widget C";

        public static void FillPivot(PivotGridControl pivot) {
            pivot.Fields.Add(Employee, PivotArea.RowArea);
            pivot.Fields.Add(Widget, PivotArea.RowArea);
            pivot.Fields.Add(Month, PivotArea.ColumnArea).AreaIndex = 0;
            pivot.Fields.Add(RetailPrice, PivotArea.DataArea);
            pivot.Fields.Add(WholesalePrice, PivotArea.DataArea);
            pivot.Fields.Add(Quantity, PivotArea.DataArea);
            pivot.Fields.Add(Remains, PivotArea.DataArea);
            foreach (PivotGridField field in pivot.Fields) {
                field.AllowedAreas = GetAllowedArea(field.Area);
            }
            pivot.OptionsView.RowTotalsLocation = PivotRowTotalsLocation.Near;
            pivot.OptionsView.ShowRowGrandTotals = false;
            pivot.OptionsView.ColumnTotalsLocation = PivotTotalsLocation.Far;
            pivot.OptionsDataField.Area = PivotDataArea.ColumnArea;
            pivot.OptionsDataField.AreaIndex = 1;
        }
        static PivotGridAllowedAreas GetAllowedArea(PivotArea area) {
            switch (area) {
                case PivotArea.ColumnArea:
                    return PivotGridAllowedAreas.ColumnArea;
                case PivotArea.RowArea:
                    return PivotGridAllowedAreas.RowArea;
                case PivotArea.DataArea:
                    return PivotGridAllowedAreas.DataArea;
                case PivotArea.FilterArea:
                    return PivotGridAllowedAreas.FilterArea;
                default:
                    return PivotGridAllowedAreas.All;
            }
        }
        public static DataTable GetDataTable() {
            DataTable table = new DataTable();
            table.Columns.Add(Employee, typeof(string));
            table.Columns.Add(Widget, typeof(string));
            table.Columns.Add(Month, typeof(int));
            table.Columns.Add(RetailPrice, typeof(double));
            table.Columns.Add(WholesalePrice, typeof(double));
            table.Columns.Add(Quantity, typeof(int));
            table.Columns.Add(Remains, typeof(int));
            table.Rows.Add(EmployeeA, WidgetA, 6, 45.6, 40, 3, 0);
            table.Rows.Add(EmployeeA, WidgetA, 7, 38.9, 30, 6, 1);
            table.Rows.Add(EmployeeA, WidgetB, 6, 24.7, 20, 7, 0);
            table.Rows.Add(EmployeeA, WidgetB, 7, 8.3, 7.5, 5, 1);
            table.Rows.Add(EmployeeA, WidgetC, 6, 10.0, 9, 4, 0);
            table.Rows.Add(EmployeeA, WidgetC, 7, 20.0, 18.5, 5, 1);
            table.Rows.Add(EmployeeB, WidgetA, 6, 77.8, 70, 2, 0);
            table.Rows.Add(EmployeeB, WidgetA, 7, 32.5, 30, 1, 1);
            table.Rows.Add(EmployeeB, WidgetB, 6, 12, 11, 10, 0);
            table.Rows.Add(EmployeeB, WidgetB, 7, 6.7, 5.5, 4, 1);
            table.Rows.Add(EmployeeB, WidgetC, 6, 30.0, 28.7, 6, 0);
            table.Rows.Add(EmployeeB, WidgetC, 7, 40.0, 38.3, 7, 1);
            return table;
        }
    }

}

Data.vb

Imports System.Data
Imports DevExpress.XtraPivotGrid

Namespace XtraPivotGrid_HidingColumnsAndRows
    Public NotInheritable Class PivotHelper

        Private Sub New()
        End Sub

        Public Const Employee As String = "Employee"
        Public Const Widget As String = "Widget"
        Public Const Month As String = "Month"
        Public Const RetailPrice As String = "Retail Price"
        Public Const WholesalePrice As String = "Wholesale Price"
        Public Const Quantity As String = "Quantity"
        Public Const Remains As String = "Remains"

        Public Const EmployeeA As String = "Employee A"
        Public Const EmployeeB As String = "Employee B"
        Public Const WidgetA As String = "Widget A"
        Public Const WidgetB As String = "Widget B"
        Public Const WidgetC As String = "Widget C"

        Public Shared Sub FillPivot(ByVal pivot As PivotGridControl)
            pivot.Fields.Add(Employee, PivotArea.RowArea)
            pivot.Fields.Add(Widget, PivotArea.RowArea)
            pivot.Fields.Add(Month, PivotArea.ColumnArea).AreaIndex = 0
            pivot.Fields.Add(RetailPrice, PivotArea.DataArea)
            pivot.Fields.Add(WholesalePrice, PivotArea.DataArea)
            pivot.Fields.Add(Quantity, PivotArea.DataArea)
            pivot.Fields.Add(Remains, PivotArea.DataArea)
            For Each field As PivotGridField In pivot.Fields
                field.AllowedAreas = GetAllowedArea(field.Area)
            Next field
            pivot.OptionsView.RowTotalsLocation = PivotRowTotalsLocation.Near
            pivot.OptionsView.ShowRowGrandTotals = False
            pivot.OptionsView.ColumnTotalsLocation = PivotTotalsLocation.Far
            pivot.OptionsDataField.Area = PivotDataArea.ColumnArea
            pivot.OptionsDataField.AreaIndex = 1
        End Sub
        Private Shared Function GetAllowedArea(ByVal area As PivotArea) As PivotGridAllowedAreas
            Select Case area
                Case PivotArea.ColumnArea
                    Return PivotGridAllowedAreas.ColumnArea
                Case PivotArea.RowArea
                    Return PivotGridAllowedAreas.RowArea
                Case PivotArea.DataArea
                    Return PivotGridAllowedAreas.DataArea
                Case PivotArea.FilterArea
                    Return PivotGridAllowedAreas.FilterArea
                Case Else
                    Return PivotGridAllowedAreas.All
            End Select
        End Function
        Public Shared Function GetDataTable() As DataTable
            Dim table As New DataTable()
            table.Columns.Add(Employee, GetType(String))
            table.Columns.Add(Widget, GetType(String))
            table.Columns.Add(Month, GetType(Integer))
            table.Columns.Add(RetailPrice, GetType(Double))
            table.Columns.Add(WholesalePrice, GetType(Double))
            table.Columns.Add(Quantity, GetType(Integer))
            table.Columns.Add(Remains, GetType(Integer))
            table.Rows.Add(EmployeeA, WidgetA, 6, 45.6, 40, 3, 0)
            table.Rows.Add(EmployeeA, WidgetA, 7, 38.9, 30, 6, 1)
            table.Rows.Add(EmployeeA, WidgetB, 6, 24.7, 20, 7, 0)
            table.Rows.Add(EmployeeA, WidgetB, 7, 8.3, 7.5, 5, 1)
            table.Rows.Add(EmployeeA, WidgetC, 6, 10.0, 9, 4, 0)
            table.Rows.Add(EmployeeA, WidgetC, 7, 20.0, 18.5, 5, 1)
            table.Rows.Add(EmployeeB, WidgetA, 6, 77.8, 70, 2, 0)
            table.Rows.Add(EmployeeB, WidgetA, 7, 32.5, 30, 1, 1)
            table.Rows.Add(EmployeeB, WidgetB, 6, 12, 11, 10, 0)
            table.Rows.Add(EmployeeB, WidgetB, 7, 6.7, 5.5, 4, 1)
            table.Rows.Add(EmployeeB, WidgetC, 6, 30.0, 28.7, 6, 0)
            table.Rows.Add(EmployeeB, WidgetC, 7, 40.0, 38.3, 7, 1)
            Return table
        End Function
    End Class

End Namespace

Program.vb

Imports System
Imports System.Collections.Generic
Imports System.Windows.Forms

Namespace XtraPivotGrid_HidingColumnsAndRows
    Friend NotInheritable Class Program

        Private Sub New()
        End Sub

        ''' <summary>
        ''' The main entry point for the application.
        ''' </summary>
        <STAThread> _
        Shared Sub Main()
            Application.EnableVisualStyles()
            Application.SetCompatibleTextRenderingDefault(False)
            Application.Run(New Form1())
        End Sub
    End Class
End Namespace

Form1.vb

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

Namespace XtraPivotGrid_HidingColumnsAndRows
    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
        ' specific rows.
        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

            ' Iterates through all row headers.
            For i As Integer = e.GetCellCount(False) - 1 To 0 Step -1
                Dim cell As FieldValueCell = e.GetCell(False, i)
                If cell Is Nothing Then
                    Continue For
                End If

                ' If the current header corresponds to the "Employee B"
                ' field value, and is not the Total Row header,
                ' it is removed with all corresponding rows.
                If Object.Equals(cell.Value, "Employee B") AndAlso cell.ValueType <> PivotGridValueType.Total Then
                    e.Remove(cell)
                End If
            Next i
        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()
            pivotGridControl1.BestFit()
        End Sub
    End Class
End Namespace