This example demonstrates different approaches to calculate a custom summary.
The task is to calculate the percentage of units that cost over $50 and show that value in the total columns.
The following approaches are shown:
The CustomSummary event. A custom summary is calculated for the “Unit Price” field. The field’s SummaryType property is set to PivotSummaryType.Custom. The ASPxPivotGrid.CustomSummary event is handled to count the records whose total sum exceeds $50. The ratio of these records to all the records is assigned to the e.CustomValue parameter.
An unbound expression. A field is an unbound field with an expression that calculates the ratio.
<dx:ASPxPivotGridID="ASPxPivotGrid1"runat="server"DataSourceID="AccessDataSource1"Theme="Office365"><OptionsDataDataProcessingEngine="Optimized" /><Fields><dx:PivotGridFieldID="fieldProductName"Area="RowArea"AreaIndex="0"Caption="Product Name"FieldName="ProductName"></dx:PivotGridField><dx:PivotGridFieldID="fieldExtendedPriceNew"Area="DataArea"AreaIndex="0"Caption="Percentage of Orders over $500"><DataBindingSerializable><dx:ExpressionDataBindingExpression="ToDecimal(Sum(iif([Extended_Price]>=500,1,0)))/Count()" /></DataBindingSerializable><CellFormatFormatString="p"FormatType="Numeric"></CellFormat></dx:PivotGridField></Fields></dx:ASPxPivotGrid>
<dx:ASPxPivotGridID="ASPxPivotGrid1"runat="server"DataSourceID="AccessDataSource1"Theme="Office365"><OptionsDataDataProcessingEngine="Optimized" /><Fields><dx:PivotGridFieldID="fieldProductName"Area="RowArea"AreaIndex="0"Caption="Product Name"FieldName="ProductName"></dx:PivotGridField><dx:PivotGridFieldID="fieldExtendedPriceNew"Area="DataArea"AreaIndex="0"Caption="Percentage of Orders over $500"UnboundExpression="ToDecimal(Sum(iif([Extended_Price]>=500,1,0)))/Count()"UnboundType="Decimal"UnboundFieldName="fieldExtendedPriceNew"><CellFormatFormatString="p"FormatType="Numeric"></CellFormat></dx:PivotGridField></Fields></dx:ASPxPivotGrid>
<dx:ASPxPivotGridID="ASPxPivotGrid1"runat="server"DataSourceID="AccessDataSource1"OnCustomSummary="ASPxPivotGrid1_CustomSummary"Theme="DevEx"><Fields><dx:PivotGridFieldID="fieldProductName"Area="RowArea"AreaIndex="0"Caption="Product Name"FieldName="ProductName"></dx:PivotGridField><dx:PivotGridFieldID="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>
usingDevExpress.Web.ASPxPivotGrid;
usingDevExpress.XtraPivotGrid;
namespaceASPxPivotGrid_CustomSummary
{
publicpartialclassCustomSummaryEventExample : System.Web.UI.Page {
staticint minSum = 500;
protectedvoidASPxPivotGrid1_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 for 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;
}
}
}
}
ImportsDevExpress.Web.ASPxPivotGridImportsDevExpress.XtraPivotGridNamespace ASPxPivotGrid_CustomSummary
PartialPublicClass CustomSummaryEventExample
Inherits System.Web.UI.Page
PrivateShared minSum AsInteger = 500ProtectedSub ASPxPivotGrid1_CustomSummary(ByVal sender AsObject, ByVal e As PivotGridCustomSummaryEventArgs)
If e.DataField IsNot fieldExtendedPrice ThenReturnEndIf' A variable which counts the number of orders whose sum exceeds $500.Dim order500Count AsInteger = 0' Get the record set for the current cell.Dim ds As PivotDrillDownDataSource = e.CreateDrillDownDataSource()
' Iterate through the records and count the orders.For i AsInteger = 0To ds.RowCount - 1Dim row As PivotDrillDownDataRow = ds(i)
' Get the order's total sum.Dim orderSum AsDecimal = DirectCast(row(fieldExtendedPrice), Decimal)
If orderSum >= minSum Then
order500Count += 1EndIfNext i
' Calculate the percentage.If ds.RowCount > 0Then
e.CustomValue = CDec(order500Count) / ds.RowCount
EndIfEndSubEndClassEndNamespace
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.