Custom summaries allow you to manually calculate summaries using custom algorithms. This can be useful in the following instances:
- calculating a custom summary function;
- using multiple data fields in a summary calculation;
- calculating a summary for individual records (for instance, for the records which match specific criteria).
To enable a custom summary calculation, set the data field’s PivotGridFieldBase.SummaryType property to PivotSummaryType.Custom. Handle the ASPxPivotGrid.CustomSummary event to implement a custom summary calculation algorithm. This event is fired for each summary cell that corresponds to this data field.
When handling the ASPxPivotGrid.CustomSummary event, use the PivotGridCustomSummaryEventArgsBase<T>.CreateDrillDownDataSource method to obtain a subset of records in a datasource which correspond to the currently processed summary cell.
Example: How to Calculate a Custom Summary
The following example shows how to calculate a custom summary.Assume that the ASPxPivotGrid control is bound to an "Invoices" table, which contains invoices information (product name, extended price, salesperson, etc). A field that displays the ratio of units cost over $50 is to be added.In this example, a custom summary is calculated against the "Unit Price" field. Its PivotGridCustomTotalBase.SummaryType property is set to PivotSummaryType.Custom and the caption to "Percentage of units cost over $50". The PivotGridControl.CustomSummary event is handled to only count those records whose total sum exceeds $50. The ratio of these records to all the records is a custom summary value and, therefore, is assigned to the PivotGridCustomSummaryEventArgsBase.CustomValue parameter.
using System.Web.UI;
using DevExpress.Web.ASPxPivotGrid;
using DevExpress.XtraPivotGrid;
namespace ASPxPivotGrid_CustomSummary {
public partial class _Default : 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 corresponding to 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;
}
}
}
}
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs"
Inherits="ASPxPivotGrid_CustomSummary._Default" %>
<%@ Register Assembly="DevExpress.Web.ASPxPivotGrid.v10.2, Version=10.2.4.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>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<dx:ASPxPivotGrid ID="ASPxPivotGrid1" runat="server" DataSourceID="AccessDataSource1"
OnCustomSummary="ASPxPivotGrid1_CustomSummary">
<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>
<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/nwind.mdb"
SelectCommand="SELECT [Extended Price] AS Extended_Price, [ProductName] FROM [OrderReports]">
</asp:AccessDataSource>
</div>
</form>
</body>
</html>
<%@ Page Language="vb" AutoEventWireup="true" CodeBehind="Default.aspx.vb"
Inherits="ASPxPivotGrid_CustomSummary._Default" %>
<%@ Register Assembly="DevExpress.Web.ASPxPivotGrid.v10.2, Version=10.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>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<dx:ASPxPivotGrid ID="ASPxPivotGrid1" runat="server" DataSourceID="AccessDataSource1"
OnCustomSummary="ASPxPivotGrid1_CustomSummary">
<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>
<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/nwind.mdb"
SelectCommand="SELECT [Extended Price] AS Extended_Price, [ProductName] FROM [OrderReports]">
</asp:AccessDataSource>
</div>
</form>
</body>
</html>
Imports Microsoft.VisualBasic
Imports System.Web.UI
Imports DevExpress.Web.ASPxPivotGrid
Imports DevExpress.XtraPivotGrid
Namespace ASPxPivotGrid_CustomSummary
Partial Public Class _Default
Inherits 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 corresponding to 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 = CDec(row(fieldExtendedPrice))
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