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 Sales Person 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 FieldSummaryType.Custom. We also assign the Custom Totals’ names to PivotGridCustomTotal.Tag properties to be able to distinguish between the Custom Totals when we calculate their values. Finally, we add the created objects to the Sales Person field’s PivotGridField.CustomTotals collection and enable the Custom Totals to be displayed for this field by setting the PivotGridField.TotalsVisibility property to FieldTotalsVisibility.CustomTotals.
Custom Total values are actually calculated in the PivotGridControl.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.
<Window xmlns:dxpg="http://schemas.devexpress.com/winfx/2008/xaml/pivotgrid"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:dxb="http://schemas.devexpress.com/winfx/2008/xaml/bars"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
x:Class="DXPivotGrid_MultipleCustomTotals.MainWindow"
Height="600" Width="800"
Title="Main Window">
<Grid>
<dxpg:PivotGridControl CustomCellValue="pivotGridControl1_CustomCellValue"
Name="pivotGridControl1">
<dxpg:PivotGridControl.Groups>
<dxpg:PivotGridGroup Name="group1"/>
<dxpg:PivotGridGroup Name="group2"/>
</dxpg:PivotGridControl.Groups>
<dxpg:PivotGridControl.Fields>
<dxpg:PivotGridField Name="fieldSalesPerson" FieldName="Sales Person" Area="RowArea"
Caption="Customer" Group="{Binding ElementName=group2}"/>
<dxpg:PivotGridField Name="fieldYear" FieldName="OrderDate" Area="ColumnArea"
Caption="Year" GroupInterval="DateYear"
Group="{Binding ElementName=group1}"/>
<dxpg:PivotGridField Name="fieldMonth" FieldName="OrderDate" Area="ColumnArea"
Caption="Month" GroupInterval="DateMonth"
Group="{Binding ElementName=group1}"/>
<dxpg:PivotGridField Name="fieldCategoryName" FieldName="CategoryName"
Area="RowArea" Caption="Category"
Group="{Binding ElementName=group2}"/>
<dxpg:PivotGridField Name="fieldQuantity" FieldName="Quantity" Area="DataArea"/>
</dxpg:PivotGridControl.Fields>
</dxpg:PivotGridControl>
</Grid>
</Window>
using System.Windows;
using DXPivotGrid_MultipleCustomTotals.DataSet1TableAdapters;
using DevExpress.Xpf.PivotGrid;
using System.Collections;
namespace DXPivotGrid_MultipleCustomTotals {
public partial class MainWindow : Window {
SalesPersonTableAdapter salesPersonDataAdapter = new SalesPersonTableAdapter();
public MainWindow() {
InitializeComponent();
// Binds the pivot grid to data.
pivotGridControl1.DataSource = salesPersonDataAdapter.GetData();
// Creates a PivotGridCustomTotal object that defines the Median Custom Total.
PivotGridCustomTotal medianCustomTotal = new PivotGridCustomTotal();
medianCustomTotal.SummaryType = FieldSummaryType.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 = "{0} Median";
// Adds the Median Custom Total for the Sales Person field.
fieldSalesPerson.CustomTotals.Add(medianCustomTotal);
// Creates a PivotGridCustomTotal object that defines the Quartiles Custom Total.
PivotGridCustomTotal quartileCustomTotal = new PivotGridCustomTotal();
quartileCustomTotal.SummaryType = FieldSummaryType.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 = "{0} Quartiles";
// Adds the Quartiles Custom Total for the Sales Person field.
fieldSalesPerson.CustomTotals.Add(quartileCustomTotal);
// Enables the Custom Totals to be displayed instead of Automatic Totals.
fieldSalesPerson.TotalsVisibility = FieldTotalsVisibility.CustomTotals;
pivotGridControl1.RowTotalsLocation = FieldRowTotalsLocation.Far;
}
// Handles the CustomCellValue event.
// Fires for each data cell. If the processed cell is a Custom Total,
// provides an appropriate Custom Total value.
private void pivotGridControl1_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), GetMedian(part2));
}
}
}
Imports Microsoft.VisualBasic
Imports System.Windows
Imports DXPivotGrid_MultipleCustomTotals.DataSet1TableAdapters
Imports DevExpress.Xpf.PivotGrid
Imports System.Collections
Namespace DXPivotGrid_MultipleCustomTotals
Partial Public Class MainWindow
Inherits Window
Private salesPersonDataAdapter As New SalesPersonTableAdapter()
Public Sub New()
InitializeComponent()
' Binds the pivot grid to data.
pivotGridControl1.DataSource = salesPersonDataAdapter.GetData()
' Creates a PivotGridCustomTotal object that defines the Median Custom Total.
Dim medianCustomTotal As New PivotGridCustomTotal()
medianCustomTotal.SummaryType = FieldSummaryType.Custom
' Specifies a unique PivotGridCustomTotal.Tag property value
' that will be used to distinguish between two Custom Total rows.
medianCustomTotal.Tag = "Median"
' Specifies formatting settings that will be used to display Custom Total row headers.
medianCustomTotal.Format = "{0} Median"
' Adds the Median Custom Total row for the Category Name field.
fieldSalesPerson.CustomTotals.Add(medianCustomTotal)
' Creates a PivotGridCustomTotal object that defines the Quartiles Custom Total.
Dim quartileCustomTotal As New PivotGridCustomTotal()
quartileCustomTotal.SummaryType = FieldSummaryType.Custom
' Specifies a unique PivotGridCustomTotal.Tag property value
' that will be used to distinguish between two Custom Total rows.
quartileCustomTotal.Tag = "Quartiles"
' Specifies formatting settings that will be used to display Custom Total row headers.
quartileCustomTotal.Format = "{0} Quartiles"
' Adds the Quartiles Custom Total row for the Category Name field.
fieldSalesPerson.CustomTotals.Add(quartileCustomTotal)
' Enables the Custom Totals to be displayed instead of Automatic Totals.
fieldSalesPerson.TotalsVisibility = FieldTotalsVisibility.CustomTotals
pivotGridControl1.RowTotalsLocation = FieldRowTotalsLocation.Far
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.
Private Sub pivotGridControl1_CustomCellValue(ByVal sender As Object, _
ByVal e As PivotCellValueEventArgs) _
Handles pivotGridControl1.CustomCellValue
' 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 CLng(CDec(values(values.Count \ 2 - 1)) + CDec(values(values.Count \ 2))) \ 2
Else
Return CDec(values(values.Count \ 2))
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), _
GetMedian(part2))
End Function
End Class
End Namespace