aspnet-9221-components-pivot-grid-examples-layout-how-to-hide-individual-rows-and-columns.md
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.
<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 "Employee B""
Value="Delete All Rows Corresponding to "Employee B"" />
</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>
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);
}
}
}
}
<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 "Employee B""
Value="Delete All Rows Corresponding to "Employee B"" />
</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>
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
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