The event handler receives an argument of type PivotCellValueEventArgs containing data related to this event.
The following
PivotCellValueEventArgs properties provide information specific to this event.
| Property |
Description |
| ColumnCustomTotal |
Gets the column custom total which displays the current cell.
|
| ColumnField |
Gets the innermost column field which corresponds to the processed cell.
|
| ColumnFieldIndex |
For internal use. |
| ColumnIndex |
Gets the visual index of the column that contains the processed cell. |
| ColumnValueType |
Gets the type of column which contains the processed cell. |
| Data |
For internal use.
|
| DataField |
Gets the data field which identifies the column where the processed cell resides.
|
| Item |
For internal use. |
| RowCustomTotal |
Gets the row custom total which contains the current cell.
|
| RowField |
Gets the innermost row field which corresponds to the processed cell.
|
| RowFieldIndex |
For internal use. |
| RowIndex |
Gets the index of the row that contains the processed cell. |
| RowValueType |
Gets the type of row which contains the processed cell. |
| SummaryType |
Gets the summary type of the currently processed value. |
| SummaryValue |
Gets the summary value currently being processed. |
| Value |
Gets or sets the processed cell's value. |
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.
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 CustomCellDisplayText event which raises after the CustomCellValue event.
Example
This example calculates the percentage based on the "Beverages" row value for each column.
The application handles the 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.
| 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)
{
if (e.DataField.Name == "PercentOfBeverages")
{
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
|