ASPxPivotGrid.CustomFieldValueCells Event

Allows you to customize field value cells.

Namespace: DevExpress.Web.ASPxPivotGrid

Assembly: DevExpress.Web.ASPxPivotGrid.v24.1.dll

NuGet Package: DevExpress.Web

Declaration

C#

public event EventHandler<PivotCustomFieldValueCellsEventArgs> CustomFieldValueCells

VB.NET

Public Event CustomFieldValueCells As EventHandler(Of PivotCustomFieldValueCellsEventArgs)

Event Data

The CustomFieldValueCells event's data class is PivotCustomFieldValueCellsEventArgs. The following properties provide information specific to this event:

Property	Description
ColumnCount	Gets the number of columns in the pivot grid. Inherited from PivotCustomFieldValueCellsEventArgsBase.
IsUpdateRequired	Gets whether the area where the field value cells reside needs to be redrawn after the event is handled. Inherited from PivotCustomFieldValueCellsEventArgsBase.
RowCount	Gets the number of rows in the pivot grid. Inherited from PivotCustomFieldValueCellsEventArgsBase.

The event data class exposes the following methods:

Method	Description
FindAllCells(Boolean, Predicate<Object[]>)	Returns a list of the headers of the column/row whose summary values match the specified condition.
FindCell(Boolean, Predicate<Object[]>)	Returns the header of the column/row whose summary values match the specified condition.
GetCell(Boolean, Int32)	Returns the field value cell by its index.
GetCellCount(Boolean)	Returns the number of field value cells in the specified area. Inherited from PivotCustomFieldValueCellsEventArgsBase.
GetCellValue(Int32, Int32)	Gets the value of a data cell by its column and row indexes. Inherited from PivotCustomFieldValueCellsEventArgsBase.
GetGrandTotalLocation(Boolean)	Returns the location of Grand Total columns or rows. Inherited from PivotCustomFieldValueCellsEventArgsBase.
GetLevelCount(Boolean)	Returns the number of column or row levels. Inherited from PivotCustomFieldValueCellsEventArgsBase.
Remove(FieldValueCellBase)	Removes the specified field value cell. Inherited from PivotCustomFieldValueCellsEventArgsBase.
SetGrandTotalLocation(Boolean, GrandTotalLocation)	Sets the location of Grand Total columns or rows to the specified value. Inherited from PivotCustomFieldValueCellsEventArgsBase.
Split(Boolean, Predicate<FieldValueCell>, FieldValueSplitData[])	Splits all field value cells that match the specified condition.
Split(Boolean, Predicate<FieldValueCell>, Boolean, FieldValueSplitData[])	Splits all field value cells that match the specified condition, or only the first matching cell.
Split(Boolean, Predicate<FieldValueCell>, Boolean, IList<FieldValueSplitData>)	Splits all field value cells that match the specified condition, or only the first matching cell.
Split(Boolean, Predicate<FieldValueCell>, IList<FieldValueSplitData>)	Splits all field value cells that match the specified condition.

Remarks

The CustomFieldValueCells event occurs when the layout of the ASPxPivotGrid is changed, allowing you to customize column and row headers: field value cells, data field, total and grand total headers.

Use the event parameter’s PivotCustomFieldValueCellsEventArgs.GetCell method to obtain data related to an individual cell, by its index. This method returns a FieldValueCell object, which provides the data. Use the PivotCustomFieldValueCellsEventArgsBase.GetCellCount method to obtain the total number of field value cells. Column/row headers can also be identified by their column/row. Use the PivotCustomFieldValueCellsEventArgs.FindCell method to obtain the header whose column/row matches a specific condition.

The CustomFieldValueCells event allows you to specify the location of grand total headers using the PivotCustomFieldValueCellsEventArgsBase.SetGrandTotalLocation method. To obtain the current location of grand total headers, use the PivotCustomFieldValueCellsEventArgsBase.GetGrandTotalLocation method.

When handling the CustomFieldValueCells event, you can also remove individual cells with their nested columns and rows via the PivotCustomFieldValueCellsEventArgsBase.Remove method.

The PivotCustomFieldValueCellsEventArgs.Split method allows you to split field value cells that have more than one nested cell. This method splits cells that match the specified condition (or, optionally, only the first matching cell) in a custom manner defined by the FieldValueSplitData objects.

Note

Custom values provided via the ASPxPivotGrid.CustomCellValue, ASPxPivotGrid.CustomSummary and ASPxPivotGrid.CustomCellDisplayText events are not available when handling the CustomFieldValueCells event, because it is raised prior to these events.

Example

The following example demonstrates how to split field value cells. In this example, the Grand Total column header is split into two cells: Price and Count. To do this, handle the CustomFieldValueCells event and use the event parameter’s Split method. Cells that should be split are identified by a predicate that returns true for those cells. The quantity, size and captions of newly created cells are specified by an array of cell definitions (the FieldValueSplitData objects).

View Example

Default.aspx

<head id="Head1" runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
        <asp:RadioButtonList ID="radioButtonList" runat="server"
            AutoPostBack="true" CellSpacing="10" >
            <asp:ListItem Selected="True">Default Layout</asp:ListItem>
            <asp:ListItem>Split Grand Total Column Header</asp:ListItem>
        </asp:RadioButtonList>
        <br />
        <div>
            <dx:ASPxPivotGrid ID="pivotGrid" runat="server" Width="500px" 
                OnFieldValueDisplayText="pivotGrid_FieldValueDisplayText"
                OnCustomFieldValueCells="pivotGrid_CustomFieldValueCells"
                OptionsCustomization-AllowFilter="False"
                OptionsCustomization-AllowDrag="False">
            </dx:ASPxPivotGrid>
        </div>
    </form>    
</body>

</html>

Default.aspx.cs

using System;
using System.Collections.Generic;
using System.Globalization;
using System.Web.UI;
using DevExpress.Web.ASPxPivotGrid;
using DevExpress.XtraPivotGrid;
using DevExpress.XtraPivotGrid.Data;

namespace ASPxPivotGrid_SplittingCells {
    public partial class _Default : Page {
        protected void Page_Load(object sender, EventArgs e) {
            if (!IsCallback && !IsPostBack) {
                PivotHelper.FillPivot(pivotGrid);
            }
            pivotGrid.DataSource = PivotHelper.GetDataTable();
        }
        protected void pivotGrid_CustomFieldValueCells(object sender,
                             PivotCustomFieldValueCellsEventArgs e) {
            if (pivotGrid.DataSource == null) return;
            if (radioButtonList.SelectedIndex == 0) return;

            // Creates a predicate that returns true for the Grand Total
            // headers, and false for any other column/row header.
            // Only cells that match this predicate are split.
            Predicate<FieldValueCell> condition =
                new Predicate<FieldValueCell>(delegate(FieldValueCell matchCell) {
                return matchCell.ValueType == PivotGridValueType.GrandTotal &&
                    matchCell.Field == null;
            });

            // Creates a list of cell definitions that represent newly created cells.
            // Two definitions are added to the list. The first one identifies
            // the Price cell, which has two nested cells (the Retail Price and Wholesale Price
            // data field headers). The second one identifies the Count cell with 
            // one nested cell (the Quantity data field header).
            List<FieldValueSplitData> cells = new List<FieldValueSplitData>(2);
            cells.Add(new FieldValueSplitData("Price", 2));
            cells.Add(new FieldValueSplitData("Count", 1));

            // Performs splitting.
            e.Split(true, condition, cells);
        }
        protected void pivotGrid_FieldValueDisplayText(object sender, 
                                PivotFieldDisplayTextEventArgs e) {
            if (e.Field == pivotGrid.Fields[PivotHelper.Month] && e.Value is int) {
                e.DisplayText = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName((int)e.Value);
            }
        }
    }
}

Default.aspx.vb

Imports Microsoft.VisualBasic
Imports System
Imports System.Collections.Generic
Imports System.Globalization
Imports System.Web.UI
Imports DevExpress.Web.ASPxPivotGrid
Imports DevExpress.XtraPivotGrid
Imports DevExpress.XtraPivotGrid.Data

Namespace ASPxPivotGrid_SplittingCells
    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
        Protected Sub pivotGrid_CustomFieldValueCells(ByVal sender As Object, _
                                ByVal e As PivotCustomFieldValueCellsEventArgs)
            If pivotGrid.DataSource Is Nothing Then
                Return
            End If
            If radioButtonList.SelectedIndex = 0 Then
                Return
            End If

            ' Creates a predicate that returns true for the Grand Total
            ' headers, and false for any other column/row header.
            ' Only cells that match this predicate are split.
            Dim condition As New Predicate(Of FieldValueCell)(AddressOf AnonymousMethod1)

            ' Creates a list of cell definitions that represent newly created cells.
            ' Two definitions are added to the list. The first one identifies
            ' the Price cell, which has two nested cells (the Retail Price and Wholesale Price
            ' data field headers). The second one identifies the Count cell with 
            ' one nested cell (the Quantity data field header).
            Dim cells As New List(Of FieldValueSplitData)(2)
            cells.Add(New FieldValueSplitData("Price", 2))
            cells.Add(New FieldValueSplitData("Count", 1))

            ' Performs splitting.
            e.Split(True, condition, cells)
        End Sub
        Private Function AnonymousMethod1(ByVal matchCell As FieldValueCell) As Boolean
            Return matchCell.ValueType = PivotGridValueType.GrandTotal AndAlso _
                matchCell.Field Is Nothing
        End Function
        Protected Sub pivotGrid_FieldValueDisplayText(ByVal sender As Object, _
                                    ByVal e As PivotFieldDisplayTextEventArgs)
            If e.Value Is Nothing Then
                Return
            End If
            If Object.Equals(e.Field, pivotGrid.Fields(PivotHelper.Month)) Then
                e.DisplayText = _
                    CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(CInt(Fix(e.Value)))
            End If
        End Sub
    End Class
End Namespace

Data.cs

using System.Data;
using DevExpress.Web.ASPxPivotGrid;
using DevExpress.XtraPivotGrid;

namespace ASPxPivotGrid_SplittingCells {
    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(ASPxPivotGrid 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);
            foreach (PivotGridField field in pivot.Fields) {
                field.AllowedAreas = GetAllowedArea(field.Area);
            }
            pivot.OptionsView.RowTotalsLocation = PivotRowTotalsLocation.Far;
            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);
            table.Rows.Add(EmployeeA, WidgetA, 7, 38.9, 30, 6);
            table.Rows.Add(EmployeeA, WidgetB, 6, 24.7, 20, 7);
            table.Rows.Add(EmployeeA, WidgetB, 7, 8.3, 7.5, 5);
            table.Rows.Add(EmployeeA, WidgetC, 6, 10.0, 9, 4);
            table.Rows.Add(EmployeeA, WidgetC, 7, 20.0, 18.5, 5);
            table.Rows.Add(EmployeeB, WidgetA, 6, 77.8, 70, 2);
            table.Rows.Add(EmployeeB, WidgetA, 7, 32.5, 30, 1);
            table.Rows.Add(EmployeeB, WidgetB, 6, 12, 11, 10);
            table.Rows.Add(EmployeeB, WidgetB, 7, 6.7, 5.5, 4);
            table.Rows.Add(EmployeeB, WidgetC, 6, 30.0, 28.7, 6);
            table.Rows.Add(EmployeeB, WidgetC, 7, 40.0, 38.3, 7);
            return table;
        }
    }
}

Data.vb

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

Namespace ASPxPivotGrid_SplittingCells
    Public NotInheritable Class PivotHelper
        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"

        Private Sub New()
        End Sub
        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)
            For Each field As PivotGridField In pivot.Fields
                field.AllowedAreas = GetAllowedArea(field.Area)
            Next field
            pivot.OptionsView.RowTotalsLocation = PivotRowTotalsLocation.Far
            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)
            table.Rows.Add(EmployeeA, WidgetA, 7, 38.9, 30, 6)
            table.Rows.Add(EmployeeA, WidgetB, 6, 24.7, 20, 7)
            table.Rows.Add(EmployeeA, WidgetB, 7, 8.3, 7.5, 5)
            table.Rows.Add(EmployeeA, WidgetC, 6, 10.0, 9, 4)
            table.Rows.Add(EmployeeA, WidgetC, 7, 20.0, 18.5, 5)
            table.Rows.Add(EmployeeB, WidgetA, 6, 77.8, 70, 2)
            table.Rows.Add(EmployeeB, WidgetA, 7, 32.5, 30, 1)
            table.Rows.Add(EmployeeB, WidgetB, 6, 12, 11, 10)
            table.Rows.Add(EmployeeB, WidgetB, 7, 6.7, 5.5, 4)
            table.Rows.Add(EmployeeB, WidgetC, 6, 30.0, 28.7, 6)
            table.Rows.Add(EmployeeB, WidgetC, 7, 40.0, 38.3, 7)
            Return table
        End Function
    End Class
End Namespace