Back to Devexpress

ASPxPivotGrid.CustomSummary Event

aspnet-devexpress-dot-web-dot-aspxpivotgrid-dot-aspxpivotgrid-e7b6858f.md

latest14.5 KB
Original Source

ASPxPivotGrid.CustomSummary Event

Enables summary values to be calculated manually.

Namespace : DevExpress.Web.ASPxPivotGrid

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

NuGet Package : DevExpress.Web

Declaration

csharp
public event PivotGridCustomSummaryEventHandler CustomSummary
vb
Public Event CustomSummary As PivotGridCustomSummaryEventHandler

Event Data

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

PropertyDescription
ColumnFieldGets the column field that corresponds to the current cell. Inherited from PivotGridCustomSummaryEventArgsBase<T>.
ColumnFieldValueGets the value of the column field that corresponds to the current cell. Inherited from PivotGridCustomSummaryEventArgsBase<T>.
CustomValueGets or sets a custom summary value. Inherited from PivotGridCustomSummaryEventArgsBase<T>.
DataFieldGets the data field against which the summary is calculated. Inherited from PivotGridCustomSummaryEventArgsBase<T>.
FieldNameGets the name of the data field against which the summary is calculated. Inherited from PivotGridCustomSummaryEventArgsBase<T>.
RowFieldGets the row field that corresponds to the current cell. Inherited from PivotGridCustomSummaryEventArgsBase<T>.
RowFieldValueGets the value of the row field that corresponds to the current cell. Inherited from PivotGridCustomSummaryEventArgsBase<T>.
SummaryValueGets 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:

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

Remarks

Note

This member is not supported in Optimized, OLAP, and Server modes. Use ExpressionDataBinding for Optimized mode instead.

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.

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:

View Example: How to Implement Custom Summary

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

See Also

ASPxPivotGrid Class

ASPxPivotGrid Members

DevExpress.Web.ASPxPivotGrid Namespace