How to Provide Custom Grouping Values

• Jul 30, 2021
• 2 minutes to read

This topic demonstrates how to include the year into grouping values when grouping by months.

By default, the year isn’t taken into account when date values are grouped by months (i.e. the GroupInterval property of a row field or column field is set to giMonth). To arrange field values into groups so that they represent the months of particular years, you need to implement custom grouping. This can be done by setting the field’s GroupInterval property to giCustom and handling its OnGetGroupValue and OnGetGroupValueDisplayText events in order to include the year in the grouping values and provide corresponding display values for them, respectively.

Consider the Orders table that is shipped with the ExpressPivotGrid demos and contains order information. The following code snippet demonstrates the OnGetGroupValue and OnGetGroupValueDisplayText event handlers of the pgfMonth field bound to the PurchaseDate field in the underlying data source.

Delphi

uses
  ..., DateUtils;
// ...
procedure <TForm>.DBPivotGridPurchaseDateGetGroupValue(
  Sender: TcxPivotGridField; ARecordIndex: Integer; var AValue: Variant);
var
  ADateTime: TDateTime;
begin
  ADateTime := VarToDateTime(AValue);
  // The grouping value is determined as the first day of the month.
  AValue := EncodeDate(YearOf(ADateTime), MonthOf(ADateTime), 1);
end;
procedure <TForm>.DBPivotGridPurchaseDateGetGroupValueDisplayText(
  Sender: TcxPivotGridField; const AGroupValue: Variant; var AText: String);
begin
  // The display text represents both the month and the year of the date.
  AText := LongMonthNames[MonthOf(AGroupValue)] + ' ' + IntToStr(YearOf(AGroupValue));
end;

C++

#pragma link "DateUtils"
// ...
void __fastcall <TForm>::pgfPurchaseDateGetGroupValue(
      TcxPivotGridField *Sender, int ARecordIndex, Variant &AValue)
{
  TDateTime ADateTime = VarToDateTime(AValue);
  // The grouping value is determined as the first day of the month.
  AValue = EncodeDate(YearOf(ADateTime), MonthOf(ADateTime), 1);
}
void __fastcall <TForm>::pgfPurchaseDateGetGroupValueDisplayText(
      TcxPivotGridField *Sender, const Variant &AGroupValue, AnsiString &AText)
{
  // The display text represents both the month and the year of the date.
  AText = LongMonthNames[MonthOf(AGroupValue)] + " " + IntToStr(YearOf(AGroupValue));
}

The resulting pivot grid displays field values grouped by years and months as shown in the image below.