How to: Calculate Multiple Custom Totals with Custom Summary Type
9 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.
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.
usingSystem.Collections;
usingDevExpress.Data.PivotGrid;
usingDevExpress.Web.ASPxPivotGrid;
usingDevExpress.XtraPivotGrid;
namespaceASPxPivotGrid_MultipleCustomTotals {
publicpartialclass _Default : System.Web.UI.Page {
protectedvoidASPxPivotGrid1_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.protectedvoidASPxPivotGrid1_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.privatestringGetCustomTotalName(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++) {
objectvalue = 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.privateobjectGetCustomTotalValue(ArrayList values, string customTotalName) {
// Returns a null value if the provided array is empty.if (values.Count == 0) {
returnnull;
}
// 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.returnnull;
}
// Calculates a median for the specified sorted sample.privatedecimalGetMedian(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.privatestringGetQuartiles(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);
}
returnstring.Format("({0}, {1})",
GetMedian(part1).ToString("c2"),
GetMedian(part2).ToString("c2"));
}
}
}
ImportsSystem.CollectionsImportsDevExpress.Data.PivotGridImportsDevExpress.Web.ASPxPivotGridImportsDevExpress.XtraPivotGridNamespace ASPxPivotGrid_MultipleCustomTotals
PartialPublicClass _Default
Inherits System.Web.UI.Page
ProtectedSub ASPxPivotGrid1_Load(ByVal sender AsObject, ByVal e As System.EventArgs)
If IsCallback OrElse IsPostBack ThenReturnEndIf' Creates a PivotGridCustomTotal object that defines the Median Custom Total.Dim medianCustomTotal AsNew 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 AsNew 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
EndSub' Handles the CustomCellValue event. ' Fires for each data cell. If the processed cell is a Custom Total,' provides an appropriate Custom Total value.ProtectedSub ASPxPivotGrid1_CustomCellValue(ByVal sender AsObject,
ByVal e As PivotCellValueEventArgs)
' Exits if the processed cell does not belong to a Custom Total.If e.ColumnCustomTotal IsNothingAndAlso e.RowCustomTotal IsNothingThenReturnEndIf' 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 AsString = GetCustomTotalName(e)
' Calculates the Custom Total value and assigns it to the Value event parameter.
e.Value = GetCustomTotalValue(summaryValues, customTotalName)
EndSub' Returns the Custom Total name.PrivateFunction GetCustomTotalName(ByVal e As PivotCellValueEventArgs) AsStringReturnIf(e.ColumnCustomTotal IsNotNothing,
e.ColumnCustomTotal.Tag.ToString(),
e.RowCustomTotal.Tag.ToString())
EndFunction' Returns a list of summary values against which' a Custom Total will be calculated.PrivateFunction GetSummaryValues(ByVal e As PivotCellValueEventArgs) As ArrayList
Dim values AsNew 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 AsInteger = 0To sds.RowCount - 1Dim value AsObject = sds.GetValue(i, e.DataField)
If value IsNothingThenContinueForEndIf
values.Add(value)
Next i
' Sorts summary values.
values.Sort()
' Returns the summary values array.Return values
EndFunction' Returns the Custom Total value by an array of summary values.PrivateFunction GetCustomTotalValue(ByVal values As ArrayList,
ByVal customTotalName AsString) AsObject' Returns a null value if the provided array is empty.If values.Count = 0ThenReturnNothingEndIf' If the Median Custom Total should be calculated,' calls the GetMedian method.If customTotalName = "Median"ThenReturn GetMedian(values)
EndIf' If the Quartiles Custom Total should be calculated,' calls the GetQuartiles method.If customTotalName = "Quartiles"ThenReturn GetQuartiles(values)
EndIf' Otherwise, returns a null value.ReturnNothingEndFunction' Calculates a median for the specified sorted sample.PrivateFunction GetMedian(ByVal values As ArrayList) AsDecimalIf (values.Count Mod2) = 0ThenReturn (DirectCast(values(values.Count \ 2 - 1), Decimal) +
DirectCast(values(values.Count \ 2), Decimal)) / 2ElseReturnDirectCast(values(values.Count \ 2), Decimal)
EndIfEndFunction' Calculates the first and third quartiles for the specified sorted sample' and returns them inside a formatted string.PrivateFunction GetQuartiles(ByVal values As ArrayList) AsStringDim part1 AsNew ArrayList()
Dim part2 AsNew ArrayList()
If (values.Count Mod2) = 0Then
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)
EndIfReturnString.Format("({0}, {1})", GetMedian(part1).ToString("c2"),
GetMedian(part2).ToString("c2"))
EndFunctionEndClassEndNamespace
Was this page helpful?
Thanks for your feedback!
How can we improve this help topic?
Additional comments/thoughts:
If you have any questions, submit a ticket to our Support Center.