How to: Hide Individual Rows and Columns

• Dec 17, 2020
• 4 minutes to read

The following example demonstrates how to hide particular rows and columns by handling the CustomFieldValueCells event. 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.

View Example

Default.aspx (C#)

<dx:ASPxRadioButtonList ID="ASPxRadioButtonList1" runat="server" SelectedIndex="0" AutoPostBack="true" >
    <Items>
        <dx:ListEditItem Text="Default Layout" Value="Default Layout" />
        <dx:ListEditItem Text="Delete All Rows Corresponding to &quot;Employee B&quot;" 
            Value="Delete All Rows Corresponding to &quot;Employee B&quot;" />
    </Items>
</dx:ASPxRadioButtonList>        

<dx:ASPxPivotGrid ID="pivotGrid" runat="server" Width="500px" 
    OnFieldValueDisplayText="pivotGrid_FieldValueDisplayText"
    OnCustomFieldValueCells="pivotGrid_CustomFieldValueCells"
    OptionsCustomization-AllowFilter="false"
    OptionsCustomization-AllowDrag="false">
</dx:ASPxPivotGrid>

Default.aspx.cs

using System;
using System.Globalization;
using System.Web.UI;
using DevExpress.Web.ASPxPivotGrid;
using DevExpress.XtraPivotGrid;

namespace ASPxPivotGrid_HidingColumnsAndRows {
    public partial class _Default : Page {
        protected void Page_Load(object sender, EventArgs e) {
            if (!IsCallback && !IsPostBack) {
                PivotHelper.FillPivot(pivotGrid);
            }
            pivotGrid.DataSource = PivotHelper.GetDataTable();
        }

        // Handles the CustomFieldValueCells event to remove
        // specific rows.
        protected void pivotGrid_CustomFieldValueCells(object sender,
                             PivotCustomFieldValueCellsEventArgs e) {

            if (pivotGrid.DataSource == null) return;
            if (ASPxRadioButtonList1.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);
            }
        }
        protected void pivotGrid_FieldValueDisplayText(object sender, 
                                    PivotFieldDisplayTextEventArgs e) {
                                        return;
            if (e.Field == pivotGrid.Fields[PivotHelper.Month]) {
                e.DisplayText = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName((int)e.Value);
            }
        }
    }
}

Default.aspx (VB.NET)

<dx:ASPxRadioButtonList ID="ASPxRadioButtonList1" runat="server" SelectedIndex="0" AutoPostBack="true" >
    <Items>
        <dx:ListEditItem Text="Default Layout" Value="Default Layout" />
        <dx:ListEditItem Text="Delete All Rows Corresponding to &quot;Employee B&quot;" 
            Value="Delete All Rows Corresponding to &quot;Employee B&quot;" />
    </Items>
</dx:ASPxRadioButtonList>        

<dx:ASPxPivotGrid ID="pivotGrid" runat="server" Width="500px" 
    OnFieldValueDisplayText="pivotGrid_FieldValueDisplayText"
    OnCustomFieldValueCells="pivotGrid_CustomFieldValueCells"
    OptionsCustomization-AllowFilter="false"
    OptionsCustomization-AllowDrag="false">
</dx:ASPxPivotGrid>

Default.aspx.vb

Imports System
Imports System.Globalization
Imports System.Web.UI
Imports DevExpress.Web.ASPxPivotGrid
Imports DevExpress.XtraPivotGrid

Namespace ASPxPivotGrid_HidingColumnsAndRows
    Partial Public Class _Default
        Inherits Page

        Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
            If (Not IsCallback) AndAlso (Not IsPostBack) Then
                PivotHelper.FillPivot(pivotGrid)
            End If
            pivotGrid.DataSource = PivotHelper.GetDataTable()
        End Sub

        ' Handles the CustomFieldValueCells event to remove
        ' specific rows.
        Protected Sub pivotGrid_CustomFieldValueCells(ByVal sender As Object, ByVal e As PivotCustomFieldValueCellsEventArgs)

            If pivotGrid.DataSource Is Nothing Then
                Return
            End If
            If ASPxRadioButtonList1.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
        Protected Sub pivotGrid_FieldValueDisplayText(ByVal sender As Object, ByVal e As PivotFieldDisplayTextEventArgs)
                                        Return
            If Object.Equals(e.Field, pivotGrid.Fields(PivotHelper.Month)) Then
                e.DisplayText = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(CInt((e.Value)))
            End If
        End Sub
    End Class
End Namespace

Data.vb

Imports System.Data
Imports DevExpress.Web.ASPxPivotGrid
Imports DevExpress.XtraPivotGrid

Namespace ASPxPivotGrid_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 ASPxPivotGrid)
            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.ShowColumnGrandTotals = 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