Grouping and Aggregates

You've already seen that a ClientDataSet can have an index different from the order in which the data is stored in the file. Once you define an index, you can group the data by that index. In practice, a group is defined as a list of consecutive records (according to the index) for which the value of the indexed field doesn't change. For example, if you have an index by state, all the addresses within that state will fall in the group.

Grouping

The CdsCalcs example has a ClientDataSet component that extracts its data from the familiar Country dataset. The group is obtained, along with the definition of an index, by specifying a grouping level for the index:

object ClientDataSet1: TClientDataSet
  IndexDefs = <
    item
      Name = 'ClientDataSet1Index1'
      Fields = 'Continent'
      GroupingLevel = 1
    end>
  IndexName = 'ClientDataSet1Index1'

When a group is active, you can make it obvious to the user by displaying the grouping structure in the DBGrid, as shown in Figure 13.16. All you have to do is handle the OnGetText event for the grouped field (the Continent field in the example) and show the text only if the record is the first in the group:

procedure TForm1.ClientDataSet1ContinentGetText(Sender: TField;
  var Text: String; DisplayText: Boolean);
begin
  if gbFirst in ClientDataSet1.GetGroupState (1) then
    Text := Sender.AsString
  else
    Text := '';
end;

Figure 13.16: The CdsCalcs example demon-strates that by writing a little code, you can have the DBGrid control visually show the grouping defined in the ClientDataSet.

Defining Aggregates

Another feature of the ClientDataSet component is support for aggregates. An aggregate is a calculated value based on multiple records, such as the sum or average value of a field for the entire table or a group of records (defined with the grouping logic I just discussed). Aggregates are maintained; that is, they are recalculated immediately if one of the records changes. For example, the total of an invoice can be maintained automatically while the user types in the invoice items.

Note

Aggregates are maintained incrementally, not by recalculating all the values every time one value changes. Aggregate updates take advantage of the deltas tracked by the ClientDataSet. For example, to update a sum when a field is changed, the ClientDataSet subtracts the old value from the aggregate and adds the new value. Only two calculations are needed, even if there are thousands of rows in that aggregate group. For this reason, aggregate updates are instantaneous.

There are two ways to define aggregates. You can use the Aggregates property of the ClientDataSet, which is a collection; or you can define aggregate fields using the Fields Editor. In both cases, you define the aggregate expression, give it a name, and connect it to an index and a grouping level (unless you want to apply it to the entire table). Here is the Aggregates collection of the CdsCalcs example:

object ClientDataSet1: TClientDataSet
  Aggregates = <
    item
      Active = True
      AggregateName = 'Count'
      Expression = 'COUNT (NAME)'
      GroupingLevel = 1
      IndexName = 'ClientDataSet1Index1'
      Visible = False
    end
    item
      Active = True
      AggregateName = 'TotalPopulation'
      Expression = 'SUM (POPULATION)'
      Visible = False
    end>
  AggregatesActive = True

Notice in the last line of the previous code snippet that you must activate the support for aggregates, in addition to activating each specific aggregate you want to use. Disabling aggregates is important, because having too many of them can slow down a program.

The alternative approach, as I mentioned, is to use the Fields Editor, select the New Field command from its shortcut menu, and choose the Aggregate option (available, along with the InternalCalc option, only in a ClientDataSet). This is the definition of an aggregate field:

object ClientDataSet1: TClientDataSet
  object ClientDataSet1TotalArea: TAggregateField
    FieldName = 'TotalArea'
    ReadOnly = True
    Visible = True
    Active = True
    DisplayFormat = '###,###,###'
    Expression = 'SUM(AREA)'
    GroupingLevel = 1
    IndexName = 'ClientDataSet1Index1'
  end

The aggregate fields are displayed in the Fields Editor in a separate group, as you can see in Figure 13.17. The advantage of using an aggregate field, compared to a plain aggregate, is that you can define the display format and hook the field directly to a data-aware control, such as a DBEdit in the CdsCalcs example. Because the aggregate is connected to a group, as soon as you select a record from a different group, the output is automatically updated. Also, if you change the data, the total immediately shows the new value.

Figure 13.17: The bottom portion of a ClientDataSet's Fields Editor displays aggregate fields.

To use plain aggregates, you have to write a little code, as in the following example (notice that the Value of the aggregate is a variant):

procedure TForm1.Button1Click(Sender: TObject);
begin
  Label1.Caption :=
    'Area: ' + ClientDataSet1TotalArea.DisplayText + #13'Population : '
    + FormatFloat ('###,###,###', ClientDataSet1.Aggregates [1].Value) +
    #13'Number : ' + IntToStr (ClientDataSet1.Aggregates [0].Value);
end;