How to: Calculate Multiple Custom Totals with Custom Summary Type
- 10 minutes to read
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 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.
Note
A complete sample project is available at https://github.com/DevExpress-Examples/how-to-calculate-multiple-custom-totals-with-summarytype-set-to-custom-e3815
using System;
using System.Collections;
using System.Windows.Forms;
using DevExpress.Data.PivotGrid;
using DevExpress.XtraPivotGrid;
namespace XtraPivotGrid_MultipleCustomTotals {
public partial class Form1 : Form {
public Form1() {
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e) {
// Binds the pivot grid to data.
nwindDataSetTableAdapters.ProductReportsTableAdapter adapter =
new nwindDataSetTableAdapters.ProductReportsTableAdapter();
pivotGridControl1.DataSource = adapter.GetData();
// 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.
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).ToString("c2"),
GetMedian(part2).ToString("c2"));
}
}
}