ASPxPivotGrid.CustomSummary Event

Enables summary values to be calculated manually.

Namespace: DevExpress.Web.ASPxPivotGrid

Assembly: DevExpress.Web.ASPxPivotGrid.v20.2.dll

NuGet Package: DevExpress.Web

Declaration

C#

public event PivotGridCustomSummaryEventHandler CustomSummary

VB.NET

Public Event CustomSummary As PivotGridCustomSummaryEventHandler

Event Data

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

Property	Description
ColumnField	Gets the column field that corresponds to the current cell. Inherited from PivotGridCustomSummaryEventArgsBase<T>.
ColumnFieldValue	Gets the value of the column field that corresponds to the current cell. Inherited from PivotGridCustomSummaryEventArgsBase<T>.
CustomValue	Gets or sets a custom summary value. Inherited from PivotGridCustomSummaryEventArgsBase<T>.
DataField	Gets the data field against which the summary is calculated. Inherited from PivotGridCustomSummaryEventArgsBase<T>.
FieldName	Gets the name of the data field against which the summary is calculated. Inherited from PivotGridCustomSummaryEventArgsBase<T>.
RowField	Gets the row field that corresponds to the current cell. Inherited from PivotGridCustomSummaryEventArgsBase<T>.
RowFieldValue	Gets the value of the row field that corresponds to the current cell. Inherited from PivotGridCustomSummaryEventArgsBase<T>.
SummaryValue	Gets an object that contains the values of the predefined summaries which are calculated for the current cell. Inherited from PivotGridCustomSummaryEventArgsBase<T>.

The event data class exposes the following methods:

Method	Description
CreateDrillDownDataSource()	Returns data records associated with the current cell. Inherited from PivotGridCustomSummaryEventArgsBase<T>.

Remarks

The ASPxPivotGrid calculates summaries against data fields. A field’s PivotGridFieldBase.SummaryType property specifies the type of summary function. The control automatically calculates all the predefined summary functions (see the PivotSummaryType topic for a list of the available functions) and it allows custom summaries to be calculated manually using the CustomSummary event.

To calculate a custom summary for a specific data field, set its PivotGridFieldBase.SummaryType property to PivotSummaryType.Custom. In this instance, the CustomSummary event fires for each cell that corresponds to this data field. Use the PivotGridCustomSummaryEventArgsBase<T>.DataField property to identify the data field.

To identify the kind of a processed cell, you can use the PivotGridCustomSummaryEventArgsBase<T>.ColumnField and PivotGridCustomSummaryEventArgsBase<T>.RowField properties.

• If ColumnField or RowField is null, it means that the processed cell is a Grand Total.
• If both ColumnField and RowField are last fields in the corresponding area, this means that this is an ordinary cell.
• In other cases, this is a Total cell.

For example, you can handle the CustomSummary event to calculate custom summaries against multiple fields, particular records, etc. Use the PivotGridCustomSummaryEventArgsBase<T>.CreateDrillDownDataSource method to get a list of records that correspond to the processed cell. This list can then be traversed to calculate a custom summary. The custom summary value should be assigned to the PivotGridCustomSummaryEventArgsBase<T>.CustomValue property.

The ASPxPivotGrid calculates all the predefined summaries (AVERAGE, MIN, MAX, SUM, etc.) for each cell. The calculated summaries can be accessed using the PivotGridCustomSummaryEventArgsBase<T>.SummaryValue property and used in custom summary calculations.

Note

The CustomSummary is not supported in server and OLAP modes.

Example

This example demonstrates different approaches to calculate a custom summary.

The task is to calculate the percentage of units that cost over $50 and show that value in the total columns.

The following approaches are shown:

• The CustomSummary event. A custom summary is calculated for the “Unit Price” field. The field’s SummaryType property is set to PivotSummaryType.Custom. The ASPxPivotGrid.CustomSummary event is handled to count the records whose total sum exceeds $50. The ratio of these records to all the records is assigned to the e.CustomValue parameter.

• An unbound expression. A field is an unbound field with an expression that calculates the ratio.

• Data Binding API. In Optimized mode create a field bound to the ExpressionDataBinding object with the following expression:

  ToDecimal(Sum(iif([Extended_Price]>=500,1,0)))/Count()
  

View Example: How to Implement Custom Summary

DataBindingApiExample.aspx

<dx:ASPxPivotGrid ID="ASPxPivotGrid1" runat="server" DataSourceID="AccessDataSource1" Theme="Office365">
    <OptionsData DataProcessingEngine="Optimized" />
    <Fields>
        <dx:PivotGridField ID="fieldProductName" Area="RowArea" AreaIndex="0"
            Caption="Product Name" FieldName="ProductName">
        </dx:PivotGridField>
        <dx:PivotGridField ID="fieldExtendedPriceNew" Area="DataArea" AreaIndex="0"
            Caption="Percentage of Orders over $500">
            <DataBindingSerializable>
                <dx:ExpressionDataBinding Expression="ToDecimal(Sum(iif([Extended_Price]>=500,1,0)))/Count()" />
            </DataBindingSerializable>
            <CellFormat FormatString="p" FormatType="Numeric"></CellFormat>
        </dx:PivotGridField>
    </Fields>
</dx:ASPxPivotGrid>

ExpressionExample.aspx

<dx:ASPxPivotGrid ID="ASPxPivotGrid1" runat="server" DataSourceID="AccessDataSource1" Theme="Office365">
    <OptionsData DataProcessingEngine="Optimized" />
    <Fields>
        <dx:PivotGridField ID="fieldProductName" Area="RowArea" AreaIndex="0"
            Caption="Product Name" FieldName="ProductName">
        </dx:PivotGridField>
        <dx:PivotGridField ID="fieldExtendedPriceNew" Area="DataArea" AreaIndex="0"
            Caption="Percentage of Orders over $500"
            UnboundExpression="ToDecimal(Sum(iif([Extended_Price]>=500,1,0)))/Count()"
            UnboundType="Decimal"
            UnboundFieldName="fieldExtendedPriceNew">
            <CellFormat FormatString="p" FormatType="Numeric"></CellFormat>
        </dx:PivotGridField>
    </Fields>
</dx:ASPxPivotGrid>

EventExample.aspx

<dx:ASPxPivotGrid ID="ASPxPivotGrid1" runat="server" DataSourceID="AccessDataSource1"
OnCustomSummary="ASPxPivotGrid1_CustomSummary" Theme="DevEx">
    <Fields>
        <dx:PivotGridField ID="fieldProductName" Area="RowArea" AreaIndex="0"
        Caption="Product Name" FieldName="ProductName">
        </dx:PivotGridField>
        <dx:PivotGridField ID="fieldExtendedPrice" Area="DataArea" AreaIndex="0" 
        Caption="Percentage of Orders over $500" SummaryType="Custom" 
        CellFormat-FormatString="p" CellFormat-FormatType="Numeric"
        FieldName="Extended_Price">
        </dx:PivotGridField>
    </Fields>
</dx:ASPxPivotGrid>

EventExample.aspx.cs

using DevExpress.Web.ASPxPivotGrid;
using DevExpress.XtraPivotGrid;

namespace ASPxPivotGrid_CustomSummary
{
    public partial class CustomSummaryEventExample : System.Web.UI.Page
    {
        static int minSum = 500;
        protected void ASPxPivotGrid1_CustomSummary(object sender,
                                                    PivotGridCustomSummaryEventArgs e)
        {
            if (e.DataField != fieldExtendedPrice) return;

            // A variable which counts the number of orders whose sum exceeds $500.
            int order500Count = 0;

            // Get the record set for the current cell.
            PivotDrillDownDataSource ds = e.CreateDrillDownDataSource();

            // Iterate through the records and count the orders.
            for (int i = 0; i < ds.RowCount; i++)
            {
                PivotDrillDownDataRow row = ds[i];

                // Get the order's total sum.
                decimal orderSum = (decimal)row[fieldExtendedPrice];
                if (orderSum >= minSum) order500Count++;
            }

            // Calculate the percentage.
            if (ds.RowCount > 0)
            {
                e.CustomValue = (decimal)order500Count / ds.RowCount;
            }
        }
    }
}

EventExample.aspx.vb

Imports DevExpress.Web.ASPxPivotGrid
Imports DevExpress.XtraPivotGrid

Namespace ASPxPivotGrid_CustomSummary
    Partial Public Class CustomSummaryEventExample
        Inherits System.Web.UI.Page

        Private Shared minSum As Integer = 500
        Protected Sub ASPxPivotGrid1_CustomSummary(ByVal sender As Object, ByVal e As PivotGridCustomSummaryEventArgs)
            If e.DataField IsNot fieldExtendedPrice Then
                Return
            End If

            ' A variable which counts the number of orders whose sum exceeds $500.
            Dim order500Count As Integer = 0

            ' Get the record set for the current cell.
            Dim ds As PivotDrillDownDataSource = e.CreateDrillDownDataSource()

            ' Iterate through the records and count the orders.
            For i As Integer = 0 To ds.RowCount - 1
                Dim row As PivotDrillDownDataRow = ds(i)

                ' Get the order's total sum.
                Dim orderSum As Decimal = DirectCast(row(fieldExtendedPrice), Decimal)
                If orderSum >= minSum Then
                    order500Count += 1
                End If
            Next i

            ' Calculate the percentage.
            If ds.RowCount > 0 Then
                e.CustomValue = CDec(order500Count) / ds.RowCount
            End If
        End Sub
    End Class
End Namespace