Delphi ClientDataset Aggregates & Index 分組合計

 

procedure TForm1.FormCreate(Sender: TObject);
var inxIndex:TIndexDef;
  aggAggregate:TAggregate;
begin
  with ClientDataSet1 do
  begin
    //Create Dataset
    FieldDefs.Add('Field1', ftString, 20);
    FieldDefs.Add('Field2', ftString, 20);
    FieldDefs.Add('Qty', ftInteger);
    CreateDataSet;
    //Append Data

    AppendRecord(['1', '1', 10]);
    AppendRecord(['1', '1', 30]);
    AppendRecord(['1', '2', 50]);
    AppendRecord(['2', '2', 70]);
    AppendRecord(['2', '1', 90]);
  end;

  //Create Index
  inxIndex := ClientDataSet1.IndexDefs.AddIndexDef;
  inxIndex.Name := 'Index1';
  inxIndex.Fields := 'Field1;Field2';
  inxIndex.GroupingLevel := 2; //群組的層級，取決於Fields的數量
  ClientDataset1.IndexName := inxIndex.Name;

  //Create Aggregates
  aggAggregate := ClientDataset1.Aggregates.Add;
  aggAggregate.AggregateName := 'Aggregate0';
  aggAggregate.Expression := 'Sum(Qty)';

  //針對指定的群組層級做統計. 0:全部, Dataset可以不指定IndexName
  aggAggregate.GroupingLevel := 0;
  aggAggregate.IndexName := inxIndex.Name;
  aggAggregate.Active := True;

  aggAggregate := ClientDataset1.Aggregates.Add;
  aggAggregate.AggregateName := 'Aggregate1';
  aggAggregate.Expression := 'Sum(Qty)';
  //針對指定的群組層級做統計, Ex: Gropup by Field1
  aggAggregate.GroupingLevel := 1;
  aggAggregate.IndexName := inxIndex.Name;
  aggAggregate.Active := True;

  aggAggregate := ClientDataset1.Aggregates.Add;
  aggAggregate.AggregateName := 'Aggregate2';
  aggAggregate.Expression := 'Sum(Qty)';
  //針對指定的群組層級做統計, Ex: Group by Field1, Field2
  aggAggregate.GroupingLevel := 2;
  aggAggregate.IndexName := inxIndex.Name;
  aggAggregate.Active := True;

  aggAggregate := ClientDataset1.Aggregates.Add;
  aggAggregate.AggregateName := 'Aggregate3';
  aggAggregate.Expression := 'Max(Qty)';
  //針對指定的群組層級做統計, Ex: Group by Field1
  aggAggregate.GroupingLevel := 1;
  aggAggregate.IndexName := inxIndex.Name;
  aggAggregate.Active := True;

  Memo1.Clear;
  Memo1.Lines.Add('Aggregate0 (Sum(Qty)) = '+VarToStr(ClientDataSet1.Aggregates.Find('Aggregate0').Value));
  Memo1.Lines.Add('Aggregate1 (Sum(Qty) Group by Field1) = '+VarToStr(ClientDataSet1.Aggregates.Find('Aggregate1').Value));
  Memo1.Lines.Add('Aggregate2 (Sum(Qty) Group by Field1, Field2) = '+VarToStr(ClientDataSet1.Aggregates.Find('Aggregate2').Value));
  Memo1.Lines.Add('Aggregate3 (Max(Qty) Group by Field1) = '+VarToStr(ClientDataSet1.Aggregates.Find('Aggregate3').Value));
end;