|
Grouping and AggregatesYou'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. GroupingThe 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: Defining AggregatesAnother 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.
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. 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; |
|
Copyright © 2004-2024 "Delphi Sources" by BrokenByte Software. Delphi Programming Guide |
|