ClientDataset, Aggregates & GroupingLevels

Question

I've got a table that looks like this:

TYPE     GROUP     VALUE
-----    -----     -----
0        0         10
0        0         60
0        1         20
1        0         30
1        1         40
1        1         10

I want totals by TYPE and TYPE;GROUP. Created indexes on TYPE & TYPE;GROUP.

object ClientDataSet1: TClientDataSet
  IndexDefs = <
    item
      Name = 'ClientDataSet1Index1'
      Fields = 'TYPE'
      GroupingLevel = 1
    end
    item
      Name = 'ClientDataSet1Index2'
      Fields = 'TYPE;GROUP'
      GroupingLevel = 2
    end>
  IndexName = 'ClientDataSet1Index1'

And created two aggregates

object ClientDataSet1: TClientDataSet
  Aggregates = <
    item
      Active = True
      AggregateName = 'Agg1'
      Expression = 'SUM(VALUE)'
      GroupingLevel = 1
      IndexName = 'ClientDataSet1Index1'
    end
    item
      Active = True
      AggregateName = 'Agg2'
      Expression = 'SUM(VALUE)'
      GroupingLevel = 2
      IndexName = 'ClientDataSet1Index2'
    end>
  AggregatesActive = True

Agg2 will not compute because ClientDataset index is set to ClientDataSet1Index1. if ClientDataset.IndexName = ClientDataSet1Index2, Agg2 works, but Agg1 doesn't :( It doesn't seems to allow multiple groupinglevels since I can only specify one clientdataset index at a time.

Am I missing something ?

The workaround is to clone my clientdataset and create the other aggregate in there. Bit of an inconvenience.

[Using D2006]

Thanks

Answer 1

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