PivotGridControl.CustomCellValue Event

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

Namespace: DevExpress.XtraPivotGrid

Assembly: DevExpress.XtraPivotGrid.v20.1.dll

Declaration

public event EventHandler<PivotCellValueEventArgs> CustomCellValue
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.

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. 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. 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.

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

IMPORTANT

Do not change cell values, modify the control's layout, or change the control's object model in the events used for custom control painting. Actions that update the layout can cause the control to malfunction.

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.

ExCustomCellValue

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.

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;
        }
    }
}

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.

hide-totals-customcellvalue

NOTE

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

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;
    } 
}
See Also