PivotGridControl.CustomCellValue Event

Allows you to replace cell values with values calculated in the event handler.

Namespace: DevExpress.XtraPivotGrid

Assembly: DevExpress.XtraPivotGrid.v19.1.dll

Declaration

C#

public event EventHandler<PivotCellValueEventArgs> CustomCellValue

VB.NET

Public Event CustomCellValue As EventHandler(Of PivotCellValueEventArgs)

Event Data

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

Property	Description
ColumnCustomTotal	Gets the column custom total which displays the current cell. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
ColumnField	Gets the innermost column field which corresponds to the processed cell. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
ColumnFieldIndex	For internal use. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
ColumnIndex	Gets the visual index of the column that contains the processed cell. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
ColumnValueType	Gets the type of column which contains the processed cell. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
Data	For internal use. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
DataField	Gets the data field which identifies the column where the processed cell resides. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
Item	For internal use. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
RowCustomTotal	Gets the row custom total which contains the current cell. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
RowField	Gets the innermost row field which corresponds to the processed cell. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
RowFieldIndex	For internal use. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
RowIndex	Gets the index of the row that contains the processed cell. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
RowValueType	Gets the type of row which contains the processed cell. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
SummaryType	Gets the summary type of the currently processed value. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
SummaryValue	Gets the summary value currently being processed. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
Value	Gets or sets the processed cell’s value.

The event data class exposes the following methods:

Method	Description
CreateDrillDownDataSource()	Returns data records used to calculate a summary value for the cell. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
CreateDrillDownDataSource(List<String>)	Returns data records used to calculate a summary value for the current cell. Allows you to specify the columns to return. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
CreateDrillDownDataSource(Int32, List<String>)	Returns data records used to calculate a summary value for the current cell. Allows you to specify the columns and limit the number of records to return. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
CreateDrillDownDataSource(Int32)	Returns data records used to calculate a summary value for the current cell. Allows you to specify the columns and limit the number of records to return. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
CreateOLAPDrillDownDataSource(List<String>)	Obsolete. In OLAP mode, returns a list of records used to calculate a summary value for the current cell. Allows you to specify the columns to be returned. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
CreateOLAPDrillDownDataSource(Int32, List<String>)	Obsolete. In OLAP mode, returns a list of records used to calculate a summary value for the current cell. Allows you to specify the columns and limit the number of records to be returned. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
CreateServerModeDrillDownDataSource(List<String>)	Obsolete. In server mode, returns a list of records used to calculate a summary value for the current cell. Allows you to specify the columns to be returned. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
CreateServerModeDrillDownDataSource(Int32, List<String>)	Obsolete. In server mode, returns a list of records used to calculate a summary value for the current cell. Allows you to specify the columns and limit the number of records to be returned. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
CreateSummaryDataSource()	Returns a summary data source. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
GetCellValue(TField)	Returns a cell value calculated against the specified data field. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
GetCellValue(Int32, Int32)	Returns a cell value by the column and row indexes. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
GetCellValue(Object[], Object[], TField)	Returns a cell value calculated for the specified column and row field values, against the specified data field. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
GetColumnFields()	Returns an array of column fields which correspond to the current cell. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
GetColumnGrandTotal(TField)	Returns a Column Grand Total value calculated for the current row field values, against the specified data field. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
GetColumnGrandTotal(Object[], TField)	Returns a Column Grand Total value calculated for the specified row field values, against the specified data field. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
GetFieldValue(TField, Int32)	Returns the specified column or row field’s value for the cell, addressed by its zero-based index in the data area. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
GetFieldValue(TField)	Returns the value of the specified column or row field which identifies the column/row in which the processed cell resides. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
GetGrandTotal(TField)	Gets the Grand Total value for the specified field. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
GetNextColumnCellValue(TField)	Returns the value of the cell in the same row but in the next column. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
GetNextRowCellValue(TField)	Returns the value of the cell in the next row. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
GetPrevColumnCellValue(TField)	Returns the value of the cell in the same row but in the previous column. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
GetPrevRowCellValue(TField)	Returns the value of the cell in the previous row. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
GetRowFields()	Returns an array of the row fields which correspond to the current cell. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
GetRowGrandTotal(TField)	Returns a Row Grand Total value calculated for the current column field values, against the specified data field. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
GetRowGrandTotal(Object[], TField)	Returns a Row Grand Total value calculated for the specified column field values, against the specified data field. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
IsFieldValueExpanded(TField)	Indicates whether the specified field’s value that represents the row or column header of the processed cell is expanded. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
IsFieldValueRetrievable(TField)	Gets whether the value of the specified column or row field can be retrieved for the current cell by the PivotCellEventArgsBase<TField, TData, TCustomTotal>.GetFieldValue method. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.
IsOthersFieldValue(TField)	Indicates whether the processed data cell resides within the “Others” row/column when the Top X Value feature is enabled. Inherited from PivotCellEventArgsBase<TField, TData, TCustomTotal>.

Remarks

The CustomCellValue event fires after all pivot calculations, when cell values are filtered, grouped, and sorted. The PivotGridControl raises the CustomCellValue event for each cell and you can modify cell values at the final stage before display.

When this event occurs, all cell positions are determined. An event handler can perform custom calculations based on a cell with a known position. The cell position is specified by row and column indices or relative to the current cell (next or previous row or column).

You can perform custom calculations and assign the result to the PivotCellValueEventArgs.Value property.

The PivotCellValueEventArgs class contains event data and provides properties and methods to obtain all data and summatries related to the current cell, the cell position and values of the adjacent cells.

The CustomCellValue event cannot be used to sort rows or columns by custom values. Handle the CustomSummary event instead. It allows you to access cells unrelated to their visual position and use the result to sort rows and columns.

For more information on summary values (totals) refer to the Summarization - Totals and UI Elements - Totals articles.

Warning

Sorting by custom summary values in the event handler may cause an infinite loop because a value change may change the cell order and trigger the event.

Tip

To format a displayed value, handle the PivotGridControl.CustomCellDisplayText event which raises after the CustomCellValue event.

Examples

This example calculates the percentage based on the “Beverages” row value for each column.

The application handles the PivotGridControl.CustomCellValue event to display a calculated percentage value in the cell that belongs to the Percent column.

The grand total cell values for the column that display percentage are set to null to hide them.

The image below shows the result.

Note

The complete sample project How to Use the Other Cell’s Values in the Current Cell Value Calculation is available in the DevExpress Examples repository.

Form1.cs

using DevExpress.XtraEditors;
using DevExpress.XtraPivotGrid;
using System.Linq;
using System;

namespace WinAppCustomCellValue
{
    public partial class Form1 : XtraForm
    {
        public Form1()
        {
            InitializeComponent();
            salesPersonTableAdapter1.Fill(nwindDataSet1.SalesPerson);

            PivotGridField fieldPercentOfBeverages = new PivotGridField()
            {
                FieldName = "Extended Price",
                Area = PivotArea.DataArea,
                Caption = "% Beverages",
                Name = "PercentOfBeverages"
            };

            fieldPercentOfBeverages.CellFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
            fieldPercentOfBeverages.CellFormat.FormatString = "P";
            pivotGridControl1.Fields.Add(fieldPercentOfBeverages);
        }

        private void pivotGridControl1_CustomCellValue(object sender, PivotCellValueEventArgs e)
        {
            // Calculates the 'Percent' field values.
            if (e.DataField.Name == "PercentOfBeverages")
            {
                // Do not display grand total values.
                if (e.RowValueType == PivotGridValueType.GrandTotal)
                {
                    e.Value = null;
                    return;
                }

                var rowValues = e.GetRowFields().Select(f => f.FieldName == "CategoryName" ? "Beverages" : e.GetFieldValue(f)).ToArray();
                var columnValues = e.GetColumnFields().Select(f => f.FieldName == "CategoryName" ? "Beverages" : e.GetFieldValue(f)).ToArray();
                decimal beveragesValue = Convert.ToDecimal(e.GetCellValue(columnValues, rowValues, e.DataField));
                if (beveragesValue == 0)
                    e.Value = null;
                else
                    e.Value = Convert.ToDecimal(e.Value) / beveragesValue;
            }
            else return;
        }
    }
}

Form1.vb

Imports DevExpress.XtraEditors
Imports DevExpress.XtraPivotGrid
Imports System.Linq
Imports System

Namespace WinAppCustomCellValue
    Partial Public Class Form1
        Inherits XtraForm

        Public Sub New()
            InitializeComponent()
            salesPersonTableAdapter1.Fill(nwindDataSet1.SalesPerson)

            Dim fieldPercentOfBeverages As New PivotGridField() With {.FieldName = "Extended Price", .Area = PivotArea.DataArea, .Caption = "% Beverages", .Name = "PercentOfBeverages"}

            fieldPercentOfBeverages.CellFormat.FormatType = DevExpress.Utils.FormatType.Numeric
            fieldPercentOfBeverages.CellFormat.FormatString = "P"
            pivotGridControl1.Fields.Add(fieldPercentOfBeverages)
        End Sub

        Private Sub pivotGridControl1_CustomCellValue(ByVal sender As Object, ByVal e As PivotCellValueEventArgs) Handles pivotGridControl1.CustomCellValue
            ' Calculates the 'Percent' field values.
            If e.DataField.Name = "PercentOfBeverages" Then
                ' Do not display grand total values.
                If e.RowValueType = PivotGridValueType.GrandTotal Then
                    e.Value = Nothing
                    Return
                End If

                Dim rowValues = e.GetRowFields().Select(Function(f)If(f.FieldName = "CategoryName", "Beverages", e.GetFieldValue(f))).ToArray()
                Dim columnValues = e.GetColumnFields().Select(Function(f)If(f.FieldName = "CategoryName", "Beverages", e.GetFieldValue(f))).ToArray()
                Dim beveragesValue As Decimal = Convert.ToDecimal(e.GetCellValue(columnValues, rowValues, e.DataField))
                If beveragesValue = 0 Then
                    e.Value = Nothing
                Else
                    e.Value = Convert.ToDecimal(e.Value) / beveragesValue
                End If
            Else
                Return
            End If
        End Sub
    End Class
End Namespace

This example demonstrates how to use the CustomCellValue event to hide field’s row totals and grand totals. When field values are collapsed, the totals remain hidden.

Note

The complete sample project How to Handle the CustomValueCell Event to Hide Row Totals is available in the DevExpress Examples repository.

C#

private void pivotGridControl1_CustomCellValue(object sender, PivotCellValueEventArgs e)
{
    if (e.DataField == fieldLowLevel)
    {
        PivotGridField lastLevelField = pivotGridControl1.GetFieldsByArea(PivotArea.RowArea).Last();
        if(e.RowValueType == PivotGridValueType.Total 
            || e.RowValueType == PivotGridValueType.GrandTotal 
            || e.RowField != lastLevelField)
            e.Value = null;
    } 
}

VB.NET

Private Sub pivotGridControl1_CustomCellValue(ByVal sender As Object, ByVal e As PivotCellValueEventArgs) Handles pivotGridControl1.CustomCellValue
    If e.DataField Is fieldLowLevel Then
        Dim lastLevelField As PivotGridField = pivotGridControl1.GetFieldsByArea(PivotArea.RowArea).Last()
        If e.RowValueType = PivotGridValueType.Total OrElse e.RowValueType = PivotGridValueType.GrandTotal OrElse e.RowField IsNot lastLevelField Then
            e.Value = Nothing
        End If
    End If
End Sub

Related GitHub Examples

The following code snippets (auto-collected from DevExpress Examples) contain references to the CustomCellValue event.

Note

The algorithm used to collect these code examples remains a work in progress. Accordingly, the links and snippets below may produce inaccurate results. If you encounter an issue with code examples below, please use the feedback form on this page to report the issue.

C#

how-to-access-api-of-underlying-controls-in-the-winforms-viewer-t197114/CS/Dashboard_ControlAccess/Form1.cs#L19

    PivotGridControl pivotGridControl = e.PivotGridControl;
    pivotGridControl.CustomCellValue += pivotGridControl_CustomCellValue;
}

winforms-dashboard-control-access/CS/DashboardDesigner_ControlAccess/DesignerForm1.cs#L19

    PivotGridControl pivotGridControl = e.PivotGridControl;
    pivotGridControl.CustomCellValue += PivotGridControl_CustomCellValue; ;
}

winforms-pivotgrid-hide-totals-customcellvalue/CS/HideTotalsCustomCellValueExample/Form1.cs#L14

InitializeComponent();
pivotGridControl1.CustomCellValue += pivotGridControl1_CustomCellValue;
BindingList<PivotData> data = new BindingList<PivotData>();

winforms-pivotgrid-how-to-edit-and-save-cell-values/CS/HowToEditAndSavePivotCellValues/Form1.cs#L14

pivotGridControl1.CustomCellValue += PivotGridControl1_CustomCellValue;
pivotGridControl1.EditValueChanged += PivotGridControl1_EditValueChanged;

VB.NET

how-to-access-api-of-underlying-controls-in-the-winforms-viewer-t197114/VB/Dashboard_ControlAccess/Form1.vb#L20

    Dim pivotGridControl As PivotGridControl = e.PivotGridControl
    AddHandler pivotGridControl.CustomCellValue, AddressOf pivotGridControl_CustomCellValue
End If

winforms-dashboard-control-access/VB/DashboardDesigner_ControlAccess/DesignerForm1.vb#L19

Dim pivotGridControl As PivotGridControl = e.PivotGridControl
AddHandler pivotGridControl.CustomCellValue, AddressOf PivotGridControl_CustomCellValue

winforms-pivotgrid-hide-totals-customcellvalue/VB/HideTotalsCustomCellValueExample/Form1.vb#L13

InitializeComponent()
AddHandler pivotGridControl1.CustomCellValue, AddressOf pivotGridControl1_CustomCellValue
Dim data As New BindingList(Of PivotData)()

winforms-pivotgrid-how-to-edit-and-save-cell-values/VB/HowToEditAndSavePivotCellValues/Form1.vb#L12

AddHandler pivotGridControl1.CustomCellValue, AddressOf PivotGridControl1_CustomCellValue
AddHandler pivotGridControl1.EditValueChanged, AddressOf PivotGridControl1_EditValueChanged