ClientDataset, Aggregates & GroupingLevels

delphi

Solution

It should work with the second index. The following program seems to work in D2007 (I don't have D2006):

program cdsagg;

{$APPTYPE CONSOLE}

uses
  SysUtils, Classes, DB, DBClient;

procedure AppendRecord(DataSet: TClientDataSet; AType, AGroup, AValue: Integer);
begin
  DataSet.Append;
  try
    DataSet.FieldByName('TYPE').AsInteger := AType;
    DataSet.FieldByName('GROUP').AsInteger := AGroup;
    DataSet.FieldByName('VALUE').AsInteger := AValue;
    DataSet.Post;
  except
    DataSet.Cancel;
    raise;
  end;
end;

procedure Main;
var
  DataSet: TClientDataSet;
begin
  DataSet := TClientDataSet.Create(nil);
  try
    DataSet.FieldDefs.Add('TYPE', ftInteger);
    DataSet.FieldDefs.Add('GROUP', ftInteger);
    DataSet.FieldDefs.Add('VALUE', ftInteger);
    DataSet.IndexDefs.Add('MyIndex', 'TYPE;GROUP', []);
    DataSet.IndexName := 'MyIndex';
    with DataSet.Aggregates.Add do
    begin
      AggregateName := 'AGG1';
      Expression := 'SUM(VALUE)';
      GroupingLevel := 0;
      IndexName := 'MyIndex';
      Active := True;
    end;
    with DataSet.Aggregates.Add do
    begin
      AggregateName := 'AGG2';
      Expression := 'SUM(VALUE)';
      GroupingLevel := 1;
      IndexName := 'MyIndex';
      Active := True;
    end;
    with DataSet.Aggregates.Add do
    begin
      AggregateName := 'AGG3';
      Expression := 'SUM(VALUE)';
      GroupingLevel := 2;
      IndexName := 'MyIndex';
      Active := True;
    end;
    DataSet.AggregatesActive := True;
    DataSet.CreateDataSet;
    DataSet.LogChanges := False;
    AppendRecord(DataSet, 0, 0, 10);
    AppendRecord(DataSet, 0, 0, 60);
    AppendRecord(DataSet, 0, 1, 20);
    AppendRecord(DataSet, 1, 0, 30);
    AppendRecord(DataSet, 1, 1, 40);
    AppendRecord(DataSet, 1, 1, 10);

    DataSet.First;
    while not DataSet.EOF do
    begin
      Writeln(Format('GROUP:'#9'%d'#9'TYPE:'#9'%d'#9'VALUE:'#9'%d'#9'AGG1:'#9'%d'#9'AGG2:'#9'%d'#9'AGG3:'#9'%d',
        [
          DataSet.FieldByName('GROUP').AsInteger,
          DataSet.FieldByName('TYPE').AsInteger,
          DataSet.FieldByName('VALUE').AsInteger,
          Integer(DataSet.Aggregates[0].Value),
          Integer(DataSet.Aggregates[1].Value),
          Integer(DataSet.Aggregates[2].Value)
        ]
      ));

      DataSet.Next;
    end;
  finally
    DataSet.Free;
  end;
end;

begin
  try
    Main;
  except
    on E: Exception do
    begin
      ExitCode := 1;
      Writeln(Format('[%s] %s', [E.ClassName, E.Message]));
    end;
  end;
end.

The produced output:

GROUP:  0       TYPE:   0       VALUE:  10      AGG1:   170     AGG2:   90      AGG3:   70
GROUP:  0       TYPE:   0       VALUE:  60      AGG1:   170     AGG2:   90      AGG3:   70
GROUP:  1       TYPE:   0       VALUE:  20      AGG1:   170     AGG2:   90      AGG3:   20
GROUP:  0       TYPE:   1       VALUE:  30      AGG1:   170     AGG2:   80      AGG3:   30
GROUP:  1       TYPE:   1       VALUE:  40      AGG1:   170     AGG2:   80      AGG3:   50
GROUP:  1       TYPE:   1       VALUE:  10      AGG1:   170     AGG2:   80      AGG3:   50

Problem

Original source