How to Calculate Custom Summaries

• Jul 30, 2021
• 2 minutes to read

Assume that the ExpressPivotGrid control is bound to the Orders table that is shipped with the ExpressPivotGrid demos. This table contains order information (order id, car name, payment amount, etc). A data field, which displays the ratio of orders over $50,000, is to be created.

In this example the custom summary is calculated for the pgfRatio data field bound to the PaymentAmount field in the underlying data source. To represent its data, set the data field’s DataBinding.FieldName property to PaymentAmount. To specify that custom summaries will be calculated for this data field, set its SummaryType property to stCustom and its Caption to ‘Percentage of Orders over $50,000’. The OnCalculateCustomSummary is handled for each data cell to count only those orders whose amount exceeds 50000. The ratio of these orders to all the orders represents a custom summary value and therefore is assigned to the Custom property of the event’s ASummary parameter.

To display summaries as percentage values, set the data field’s DisplayFormat property to ‘0.00%’.

The following image shows the result of the custom summary calculation.

The following code snippet represents the OnCalculateCustomSummary event handler of the created data field.

Delphi

var
  I: Integer;
  APaymentAmount, AOrderCount: Variant;
begin
  // Used to store the count of the orders with amount greater than $50,000.
  AOrderCount := 0;
  with ASummary do
  begin
    // Iterate all the records that correspond to a data cell.
    for I := 0 to Records.Count - 1 do
    begin
      APaymentAmount := GetValue(Records[I]);
      if APaymentAmount > 50000 then
        Inc(AOrderCount);
    end;
    if Count > 0 then
      Custom := AOrderCount / Count * 100;
  end;
end;

C++

void __fastcall <TForm>::pgfPaymentAmountCalculateCustomSummary(
      TcxPivotGridField *Sender, TcxPivotGridCrossCellSummary *ASummary)
{
  // Used to store the count of the orders with amount greater than $50,000.
  Variant AOrderCount = 0;
  Variant APaymentAmount = 0;
  // Iterate all the records that correspond to a data cell.
  for(int I = 0; I < ASummary->Records->Count; I++) {
    APaymentAmount = ASummary->GetValue(ASummary->Records->Items[I]);
    if(APaymentAmount > 50000)
      AOrderCount = AOrderCount + 1;
  }
  if(ASummary->Count > 0)
    ASummary->Custom = AOrderCount / ASummary->Count * 100;
}