The following example demonstrates how to calculate and display multiple Custom Totals for a field.
In this example, two Custom Totals are implemented for the Category Name field. The first one displays a median calculated against summary values, while the second one displays the first and third quartiles.
To accomplish this task, we create two PivotGridCustomTotal objects and set their summary type to PivotSummaryType.Custom. We also assign the Custom Totals’ names to PivotGridCustomTotalBase.Tag properties to be able to distinguish between the Custom Totals when we calculate their values. Finally, we add the created objects to the Category Name field’s PivotGridField.CustomTotals collection and enable the Custom Totals to be displayed for this field by setting the PivotGridFieldBase.TotalsVisibility property to PivotTotalsVisibility.CustomTotals.
Custom Total values are actually calculated in the ASPxPivotGrid.CustomCellValue event. First, the event handler prepares a list of summary values against which a Custom Total will be calculated. For this purpose, it creates a summary datasource and copies the summary values to an array. After that, the array is sorted and passed to an appropriate method that calculates a median or quartile value against the provided array. Finally, the resulting value is assigned to the event parameter’s PivotCellValueEventArgs.Value property.
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs"
Inherits="ASPxPivotGrid_MultipleCustomTotals._Default" %>
<%@ Register Assembly="DevExpress.Web.ASPxPivotGrid.v14.2, Version=14.2.10.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"
oncustomcellvalue="ASPxPivotGrid1_CustomCellValue" onload="ASPxPivotGrid1_Load"
OptionsPager-RowsPerPage="20" Theme="Metropolis">
<Fields>
<dx:PivotGridField ID="fieldProductName" Area="RowArea" AreaIndex="1"
Caption="Product Name" FieldName="ProductName" GroupIndex="1"
InnerGroupIndex="1">
</dx:PivotGridField>
<dx:PivotGridField ID="fieldShippedYear" Area="ColumnArea" AreaIndex="0" Caption="Year"
FieldName="ShippedDate" GroupInterval="DateYear" GroupIndex="0"
InnerGroupIndex="0" UnboundFieldName="fieldShippedYear">
</dx:PivotGridField>
<dx:PivotGridField ID="fieldProductSales" Area="DataArea" AreaIndex="0" Caption="Sales"
FieldName="ProductSales">
</dx:PivotGridField>
<dx:PivotGridField ID="fieldCategoryName" Area="RowArea" AreaIndex="0"
Caption="Category Name" FieldName="CategoryName" GroupIndex="1"
InnerGroupIndex="0">
</dx:PivotGridField>
<dx:PivotGridField ID="fieldShippedQuarter" Area="ColumnArea" AreaIndex="1"
Caption="Quarter" FieldName="ShippedDate"
GroupInterval="DateQuarter"
ValueFormat-FormatString="Quarter {0}"
ValueFormat-FormatType="Custom" GroupIndex="0" InnerGroupIndex="1"
UnboundFieldName="fieldShippedQuarter">
</dx:PivotGridField>
</Fields>
<Groups>
<dx:PivotGridWebGroup Caption="Date" ShowNewValues="True" />
<dx:PivotGridWebGroup Caption="Category-Product" ShowNewValues="True" />
</Groups>
</dx:ASPxPivotGrid>
<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/nwind.mdb"
SelectCommand="SELECT [CategoryName], [ProductName], [ProductSales],
[ShippedDate] FROM [ProductReports]">
</asp:AccessDataSource>
</div>
</form>
</body>
</html>
using System.Collections;
using DevExpress.Data.PivotGrid;
using DevExpress.Web.ASPxPivotGrid;
using DevExpress.XtraPivotGrid;
namespace ASPxPivotGrid_MultipleCustomTotals {
public partial class _Default : System.Web.UI.Page {
protected void ASPxPivotGrid1_Load(object sender, System.EventArgs e) {
if (IsCallback || IsPostBack) return;
// Creates a PivotGridCustomTotal object that defines the Median Custom Total.
PivotGridCustomTotal medianCustomTotal = new PivotGridCustomTotal(PivotSummaryType.Custom);
// Specifies a unique PivotGridCustomTotal.Tag property value
// that will be used to distinguish between two Custom Totals.
medianCustomTotal.Tag = "Median";
// Specifies formatting settings that will be used to display
// Custom Total column/row headers.
medianCustomTotal.Format.FormatString = "{0} Median";
medianCustomTotal.Format.FormatType = DevExpress.Utils.FormatType.Custom;
// Adds the Median Custom Total for the Category Name field.
fieldCategoryName.CustomTotals.Add(medianCustomTotal);
// Creates a PivotGridCustomTotal object that defines the Quartiles Custom Total.
PivotGridCustomTotal quartileCustomTotal = new PivotGridCustomTotal(PivotSummaryType.Custom);
// Specifies a unique PivotGridCustomTotal.Tag property value
// that will be used to distinguish between two Custom Totals.
quartileCustomTotal.Tag = "Quartiles";
// Specifies formatting settings that will be used to display
// Custom Total column/row headers.
quartileCustomTotal.Format.FormatString = "{0} Quartiles";
quartileCustomTotal.Format.FormatType = DevExpress.Utils.FormatType.Custom;
// Adds the Quartiles Custom Total for the Category Name field.
fieldCategoryName.CustomTotals.Add(quartileCustomTotal);
// Enables the Custom Totals to be displayed instead of Automatic Totals.
fieldCategoryName.TotalsVisibility = PivotTotalsVisibility.CustomTotals;
}
// Handles the CustomCellValue event.
// Fires for each data cell. If the processed cell is a Custom Total,
// provides an appropriate Custom Total value.
protected void ASPxPivotGrid1_CustomCellValue(object sender, PivotCellValueEventArgs e) {
// Exits if the processed cell does not belong to a Custom Total.
if (e.ColumnCustomTotal == null && e.RowCustomTotal == null) return;
// Obtains a list of summary values against which
// the Custom Total will be calculated.
ArrayList summaryValues = GetSummaryValues(e);
// Obtains the name of the Custom Total that should be calculated.
string customTotalName = GetCustomTotalName(e);
// Calculates the Custom Total value and assigns it to the Value event parameter.
e.Value = GetCustomTotalValue(summaryValues, customTotalName);
}
// Returns the Custom Total name.
private string GetCustomTotalName(PivotCellValueEventArgs e) {
return e.ColumnCustomTotal != null ?
e.ColumnCustomTotal.Tag.ToString() :
e.RowCustomTotal.Tag.ToString();
}
// Returns a list of summary values against which
// a Custom Total will be calculated.
private ArrayList GetSummaryValues(PivotCellValueEventArgs e) {
ArrayList values = new ArrayList();
// Creates a summary data source.
PivotSummaryDataSource sds = e.CreateSummaryDataSource();
// Iterates through summary data source records
// and copies summary values to an array.
for (int i = 0; i < sds.RowCount; i++) {
object value = sds.GetValue(i, e.DataField);
if (value == null) {
continue;
}
values.Add(value);
}
// Sorts summary values.
values.Sort();
// Returns the summary values array.
return values;
}
// Returns the Custom Total value by an array of summary values.
private object GetCustomTotalValue(ArrayList values, string customTotalName) {
// Returns a null value if the provided array is empty.
if (values.Count == 0) {
return null;
}
// If the Median Custom Total should be calculated,
// calls the GetMedian method.
if (customTotalName == "Median") {
return GetMedian(values);
}
// If the Quartiles Custom Total should be calculated,
// calls the GetQuartiles method.
if (customTotalName == "Quartiles") {
return GetQuartiles(values);
}
// Otherwise, returns a null value.
return null;
}
// Calculates a median for the specified sorted sample.
private decimal GetMedian(ArrayList values) {
if ((values.Count % 2) == 0) {
return ((decimal)(values[values.Count / 2 - 1]) +
(decimal)(values[values.Count / 2])) / 2;
}
else {
return (decimal)values[values.Count / 2];
}
}
// Calculates the first and third quartiles for the specified sorted sample
// and returns them inside a formatted string.
private string GetQuartiles(ArrayList values) {
ArrayList part1 = new ArrayList();
ArrayList part2 = new ArrayList();
if ((values.Count % 2) == 0) {
part1 = values.GetRange(0, values.Count / 2);
part2 = values.GetRange(values.Count / 2, values.Count / 2);
}
else {
part1 = values.GetRange(0, values.Count / 2 + 1);
part2 = values.GetRange(values.Count / 2, values.Count / 2 + 1);
}
return string.Format("({0}, {1})",
GetMedian(part1).ToString("c2"),
GetMedian(part2).ToString("c2"));
}
}
}
<%@ Page Language="vb" AutoEventWireup="true" CodeBehind="Default.aspx.vb"
Inherits="ASPxPivotGrid_MultipleCustomTotals._Default" %>
<%@ Register Assembly="DevExpress.Web.ASPxPivotGrid.v14.2, Version=14.2.10.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"
oncustomcellvalue="ASPxPivotGrid1_CustomCellValue" onload="ASPxPivotGrid1_Load"
OptionsPager-RowsPerPage="20" Theme="Metropolis">
<Fields>
<dx:PivotGridField ID="fieldProductName" Area="RowArea" AreaIndex="1"
Caption="Product Name" FieldName="ProductName" GroupIndex="1"
InnerGroupIndex="1">
</dx:PivotGridField>
<dx:PivotGridField ID="fieldShippedYear" Area="ColumnArea" AreaIndex="0" Caption="Year"
FieldName="ShippedDate" GroupInterval="DateYear" GroupIndex="0"
InnerGroupIndex="0" UnboundFieldName="fieldShippedYear">
</dx:PivotGridField>
<dx:PivotGridField ID="fieldProductSales" Area="DataArea" AreaIndex="0" Caption="Sales"
FieldName="ProductSales">
</dx:PivotGridField>
<dx:PivotGridField ID="fieldCategoryName" Area="RowArea" AreaIndex="0"
Caption="Category Name" FieldName="CategoryName" GroupIndex="1"
InnerGroupIndex="0">
</dx:PivotGridField>
<dx:PivotGridField ID="fieldShippedQuarter" Area="ColumnArea" AreaIndex="1"
Caption="Quarter" FieldName="ShippedDate"
GroupInterval="DateQuarter"
ValueFormat-FormatString="Quarter {0}"
ValueFormat-FormatType="Custom" GroupIndex="0" InnerGroupIndex="1"
UnboundFieldName="fieldShippedQuarter">
</dx:PivotGridField>
</Fields>
<Groups>
<dx:PivotGridWebGroup Caption="Date" ShowNewValues="True" />
<dx:PivotGridWebGroup Caption="Category-Product" ShowNewValues="True" />
</Groups>
</dx:ASPxPivotGrid>
<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/nwind.mdb"
SelectCommand="SELECT [CategoryName], [ProductName], [ProductSales],
[ShippedDate] FROM [ProductReports]">
</asp:AccessDataSource>
</div>
</form>
</body>
</html>
Imports System.Collections
Imports DevExpress.Data.PivotGrid
Imports DevExpress.Web.ASPxPivotGrid
Imports DevExpress.XtraPivotGrid
Namespace ASPxPivotGrid_MultipleCustomTotals
Partial Public Class _Default
Inherits System.Web.UI.Page
Protected Sub ASPxPivotGrid1_Load(ByVal sender As Object, ByVal e As System.EventArgs)
If IsCallback OrElse IsPostBack Then
Return
End If
' Creates a PivotGridCustomTotal object that defines the Median Custom Total.
Dim medianCustomTotal As New PivotGridCustomTotal(PivotSummaryType.Custom)
' Specifies a unique PivotGridCustomTotal.Tag property value
' that will be used to distinguish between two Custom Totals.
medianCustomTotal.Tag = "Median"
' Specifies formatting settings that will be used to display
' Custom Total column/row headers.
medianCustomTotal.Format.FormatString = "{0} Median"
medianCustomTotal.Format.FormatType = DevExpress.Utils.FormatType.Custom
' Adds the Median Custom Total for the Category Name field.
fieldCategoryName.CustomTotals.Add(medianCustomTotal)
' Creates a PivotGridCustomTotal object that defines the Quartiles Custom Total.
Dim quartileCustomTotal As New PivotGridCustomTotal(PivotSummaryType.Custom)
' Specifies a unique PivotGridCustomTotal.Tag property value
' that will be used to distinguish between two Custom Totals.
quartileCustomTotal.Tag = "Quartiles"
' Specifies formatting settings that will be used to display
' Custom Total column/row headers.
quartileCustomTotal.Format.FormatString = "{0} Quartiles"
quartileCustomTotal.Format.FormatType = DevExpress.Utils.FormatType.Custom
' Adds the Quartiles Custom Total for the Category Name field.
fieldCategoryName.CustomTotals.Add(quartileCustomTotal)
' Enables the Custom Totals to be displayed instead of Automatic Totals.
fieldCategoryName.TotalsVisibility = PivotTotalsVisibility.CustomTotals
End Sub
' Handles the CustomCellValue event.
' Fires for each data cell. If the processed cell is a Custom Total,
' provides an appropriate Custom Total value.
Protected Sub ASPxPivotGrid1_CustomCellValue(ByVal sender As Object,
ByVal e As PivotCellValueEventArgs)
' Exits if the processed cell does not belong to a Custom Total.
If e.ColumnCustomTotal Is Nothing AndAlso e.RowCustomTotal Is Nothing Then
Return
End If
' Obtains a list of summary values against which
' the Custom Total will be calculated.
Dim summaryValues As ArrayList = GetSummaryValues(e)
' Obtains the name of the Custom Total that should be calculated.
Dim customTotalName As String = GetCustomTotalName(e)
' Calculates the Custom Total value and assigns it to the Value event parameter.
e.Value = GetCustomTotalValue(summaryValues, customTotalName)
End Sub
' Returns the Custom Total name.
Private Function GetCustomTotalName(ByVal e As PivotCellValueEventArgs) As String
Return If(e.ColumnCustomTotal IsNot Nothing,
e.ColumnCustomTotal.Tag.ToString(),
e.RowCustomTotal.Tag.ToString())
End Function
' Returns a list of summary values against which
' a Custom Total will be calculated.
Private Function GetSummaryValues(ByVal e As PivotCellValueEventArgs) As ArrayList
Dim values As New ArrayList()
' Creates a summary data source.
Dim sds As PivotSummaryDataSource = e.CreateSummaryDataSource()
' Iterates through summary data source records
' and copies summary values to an array.
For i As Integer = 0 To sds.RowCount - 1
Dim value As Object = sds.GetValue(i, e.DataField)
If value Is Nothing Then
Continue For
End If
values.Add(value)
Next i
' Sorts summary values.
values.Sort()
' Returns the summary values array.
Return values
End Function
' Returns the Custom Total value by an array of summary values.
Private Function GetCustomTotalValue(ByVal values As ArrayList,
ByVal customTotalName As String) As Object
' Returns a null value if the provided array is empty.
If values.Count = 0 Then
Return Nothing
End If
' If the Median Custom Total should be calculated,
' calls the GetMedian method.
If customTotalName = "Median" Then
Return GetMedian(values)
End If
' If the Quartiles Custom Total should be calculated,
' calls the GetQuartiles method.
If customTotalName = "Quartiles" Then
Return GetQuartiles(values)
End If
' Otherwise, returns a null value.
Return Nothing
End Function
' Calculates a median for the specified sorted sample.
Private Function GetMedian(ByVal values As ArrayList) As Decimal
If (values.Count Mod 2) = 0 Then
Return (DirectCast(values(values.Count \ 2 - 1), Decimal) +
DirectCast(values(values.Count \ 2), Decimal)) / 2
Else
Return DirectCast(values(values.Count \ 2), Decimal)
End If
End Function
' Calculates the first and third quartiles for the specified sorted sample
' and returns them inside a formatted string.
Private Function GetQuartiles(ByVal values As ArrayList) As String
Dim part1 As New ArrayList()
Dim part2 As New ArrayList()
If (values.Count Mod 2) = 0 Then
part1 = values.GetRange(0, values.Count \ 2)
part2 = values.GetRange(values.Count \ 2, values.Count \ 2)
Else
part1 = values.GetRange(0, values.Count \ 2 + 1)
part2 = values.GetRange(values.Count \ 2, values.Count \ 2 + 1)
End If
Return String.Format("({0}, {1})", GetMedian(part1).ToString("c2"),
GetMedian(part2).ToString("c2"))
End Function
End Class
End Namespace