ASPxPivotGrid.CustomCellValue Event

Allows you to replace cell values with custom ones.

Namespace: DevExpress.Web.ASPxPivotGrid

Assembly: DevExpress.Web.ASPxPivotGrid.v19.2.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
CellItem	For internal use. Inherited from PivotCellBaseEventArgs.
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>.
GetAbsoluteColumnIndex(Int32)	Returns the column’s absolute index by its visible index within the current page. Inherited from PivotCellBaseEventArgs.
GetAbsoluteRowIndex(Int32)	Returns the row’s absolute index by its visible index within the current page. Inherited from PivotCellBaseEventArgs.
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 for each cell. You can handle it to replace the calculated cell values with custom ones. Use the event parameter’s PivotCellEventArgsBase<TField, TData, TCustomTotal>.SummaryValue property to obtain the summary value calculated for the cell. The custom cell value should be provided to the PivotCellEventArgsBase<TField, TData, TCustomTotal>.Value property.

Use the event parameter’s PivotCellEventArgsBase<TField, TData, TCustomTotal>.ColumnField, PivotCellEventArgsBase<TField, TData, TCustomTotal>.DataField and PivotCellEventArgsBase<TField, TData, TCustomTotal>.RowField properties to obtain the column, data and row fields that correspond to the processed cell, respectively. The PivotCellEventArgsBase<TField, TData, TCustomTotal>.ColumnIndex and PivotCellEventArgsBase<TField, TData, TCustomTotal>.RowIndex properties are used to obtain the visual indices of the column and row that contain the cell.

You can also provide custom display text by handling the ASPxPivotGrid.CustomCellDisplayText event, which is raised after the CustomCellValue event.

Note

Values provided with the CustomCellValue event cannot be used to sort data according to the PivotGridFieldBase.SortBySummaryInfo object’s settings.

Example

This example shows how to calculate a percent of a current cell related to the first row value, regardless of the current sorting.

To accomplish this task, handle the ASPxPivotGrid.CustomCellValue event. To obtain a cell value, address it. Create arrays of row and column values corresponding to the desired cell, and call the PivotCellEventArgsBase<TField, TData, TCustomTotal>.GetCellValue method, passing them in arguments.

The image below shows the result.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/how-to-access-other-cell-value-while-calculating-the-current-cell-t218105

WebForm1.aspx.cs

using System;
using DevExpress.XtraPivotGrid;
using DevExpress.Utils;

namespace WebAppCustomCellValue
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        object beveragesValue = "Beverages";
        DevExpress.Web.ASPxPivotGrid.PivotGridField fieldPercentOfBeverages;

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack && !IsCallback)
            {
                // Creates and specifies a 'Percent' field.
                fieldPercentOfBeverages = ASPxPivotGrid1.Fields.Add("Extended Price",
                    PivotArea.DataArea);
                fieldPercentOfBeverages.Caption = "Percent";
                fieldPercentOfBeverages.CellFormat.FormatType = FormatType.Numeric;
                fieldPercentOfBeverages.CellFormat.FormatString = "P";
                fieldPercentOfBeverages.ID = "fieldPercent";
            }
        }

        protected void ASPxPivotGrid1_CustomCellValue(object sender,
            DevExpress.Web.ASPxPivotGrid.PivotCellValueEventArgs e)
        {
            // Calculates 'Percent' field values.
            if (e.DataField.ID == "fieldPercent")
            {
                object[] columnPath = GetValues(e, true,
                    ASPxPivotGrid1.Fields.GetFieldByName("fieldCategoryName"), "Beverages");
                object[] rowPath = GetValues(e, false,
                    ASPxPivotGrid1.Fields.GetFieldByName("fieldCategoryName"), "Beverages");
                decimal beveragesValue =
                    Convert.ToDecimal(e.GetCellValue(columnPath, rowPath, e.DataField));
                if (beveragesValue == 0)
                    e.Value = null;
                else
                    e.Value = Convert.ToDecimal(e.Value) / beveragesValue;
            }
            else return;
        }
        object[] GetValues(DevExpress.Web.ASPxPivotGrid.PivotCellValueEventArgs e, bool isColumn,
            PivotGridFieldBase targetField, object targetValue)
        {
            DevExpress.Web.ASPxPivotGrid.PivotGridField[] fields = 
                isColumn ? e.GetColumnFields() : e.GetRowFields();
            object[] targetPath = new object[fields.Length];
            for (int i = 0; i < targetPath.Length; i++)
            {
                if (object.ReferenceEquals(fields[i], targetField))
                    targetPath[i] = targetValue;
                else
                    targetPath[i] = e.GetFieldValue(fields[i]);
            }
            return targetPath;
        }
    }
}

WebForm1.aspx (C#)

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" 
    Inherits="WebAppCustomCellValue.WebForm1" %>

<%@ Register Assembly="DevExpress.Web.ASPxPivotGrid.v14.2, Version=14.2.5.0, Culture=neutral, 
    PublicKeyToken=b88d1754d700e49a"
    Namespace="DevExpress.Web.ASPxPivotGrid" TagPrefix="dx" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <dx:ASPxPivotGrid ID="ASPxPivotGrid1" runat="server" ClientIDMode="AutoID" 
            DataSourceID="AccessDataSource1" Theme="Metropolis" 
            OnCustomCellValue="ASPxPivotGrid1_CustomCellValue">
            <Fields>
                <dx:PivotGridField Area="RowArea" AreaIndex="0" Caption="Category" 
                    FieldName="CategoryName" ID="fieldCategoryName">
                </dx:PivotGridField>
                <dx:PivotGridField Area="ColumnArea" AreaIndex="0" FieldName="Country" ID="fieldCountry">
                </dx:PivotGridField>
                <dx:PivotGridField Area="DataArea" AreaIndex="0" Caption="Price" 
                    FieldName="Extended Price" ID="fieldExtendedPrice">
                </dx:PivotGridField>
            </Fields>
            <OptionsView ShowRowGrandTotals="False" />
        </dx:ASPxPivotGrid>
        <asp:AccessDataSource ID="AccessDataSource1" runat="server" 
            DataFile="~/nwind.mdb" 
            SelectCommand="SELECT [CategoryName], [Country], [Extended Price] FROM [SalesPerson]">
        </asp:AccessDataSource>
    </div>
    </form>
</body>
</html>

WebForm1.aspx.vb

Imports System
Imports DevExpress.XtraPivotGrid
Imports DevExpress.Utils

Namespace WebAppCustomCellValue
    Partial Public Class WebForm1
        Inherits System.Web.UI.Page

        Private beveragesValue As Object = "Beverages"
        Private fieldPercentOfBeverages As DevExpress.Web.ASPxPivotGrid.PivotGridField

        Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
            If (Not IsPostBack) AndAlso (Not IsCallback) Then
                ' Creates and specifies a 'Percent' field.
                fieldPercentOfBeverages = ASPxPivotGrid1.Fields.Add("Extended Price", PivotArea.DataArea)
                fieldPercentOfBeverages.Caption = "Percent"
                fieldPercentOfBeverages.CellFormat.FormatType = FormatType.Numeric
                fieldPercentOfBeverages.CellFormat.FormatString = "P"
                fieldPercentOfBeverages.ID = "fieldPercent"
            End If
        End Sub

        Protected Sub ASPxPivotGrid1_CustomCellValue(ByVal sender As Object, 
        ByVal e As DevExpress.Web.ASPxPivotGrid.PivotCellValueEventArgs)
            ' Calculates 'Percent' field values.
            If e.DataField.ID = "fieldPercent" Then
                Dim columnPath() As Object = GetValues(e, True, 
                ASPxPivotGrid1.Fields.GetFieldByName("fieldCategoryName"), "Beverages")
                Dim rowPath() As Object = GetValues(e, False, 
                ASPxPivotGrid1.Fields.GetFieldByName("fieldCategoryName"), "Beverages")
                Dim beveragesValue As Decimal = 
                Convert.ToDecimal(e.GetCellValue(columnPath, rowPath, 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
        Private Function GetValues(ByVal e As DevExpress.Web.ASPxPivotGrid.PivotCellValueEventArgs, 
        ByVal isColumn As Boolean, ByVal targetField As PivotGridFieldBase, 
        ByVal targetValue As Object) As Object()
            Dim fields() As DevExpress.Web.ASPxPivotGrid.PivotGridField = 
            If(isColumn, e.GetColumnFields(), e.GetRowFields())
            Dim targetPath(fields.Length - 1) As Object
            For i As Integer = 0 To targetPath.Length - 1
                If Object.ReferenceEquals(fields(i), targetField) Then
                    targetPath(i) = targetValue
                Else
                    targetPath(i) = e.GetFieldValue(fields(i))
                End If
            Next i
            Return targetPath
        End Function
    End Class
End Namespace

WebForm1.aspx (VB.NET)

<%@ Page Language="vb" AutoEventWireup="true" CodeBehind="WebForm1.aspx.vb" 
    Inherits="WebAppCustomCellValue.WebForm1" %>

<%@ Register Assembly="DevExpress.Web.ASPxPivotGrid.v14.2, Version=14.2.5.0, Culture=neutral, 
    PublicKeyToken=b88d1754d700e49a"
    Namespace="DevExpress.Web.ASPxPivotGrid" TagPrefix="dx" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <dx:ASPxPivotGrid ID="ASPxPivotGrid1" runat="server" ClientIDMode="AutoID" 
            DataSourceID="AccessDataSource1" Theme="Metropolis" 
            OnCustomCellValue="ASPxPivotGrid1_CustomCellValue">
            <Fields>
                <dx:PivotGridField Area="RowArea" AreaIndex="0" Caption="Category" 
                    FieldName="CategoryName" ID="fieldCategoryName">
                </dx:PivotGridField>
                <dx:PivotGridField Area="ColumnArea" AreaIndex="0" FieldName="Country" ID="fieldCountry">
                </dx:PivotGridField>
                <dx:PivotGridField Area="DataArea" AreaIndex="0" Caption="Price" 
                    FieldName="Extended Price" ID="fieldExtendedPrice">
                </dx:PivotGridField>
            </Fields>
            <OptionsView ShowRowGrandTotals="False" />
        </dx:ASPxPivotGrid>
        <asp:AccessDataSource ID="AccessDataSource1" runat="server" 
            DataFile="~/nwind.mdb" 
            SelectCommand="SELECT [CategoryName], [Country], [Extended Price] FROM [SalesPerson]">
        </asp:AccessDataSource>
    </div>
    </form>
</body>
</html>