| Delphi Clinic | C++Builder Gate | Training & Consultancy | Delphi Notes Weblog | Dr.Bob's Webshop |
 |
 |
 |
| Delphi Clinic | C++Builder Gate | Training & Consultancy | Delphi Notes Weblog | Dr.Bob's Webshop |
|
|
|
| ||||||
ClientDataSet and Aggregates
In this paper, I demonstrate how to add aggregate fields to ClientDataSets, a technique which can be used in stand-alone TClientDataSets but also in combination with dbExpress or DataSnap.
TClientDataSet
The TClientDataSet component plays an important role in combination with dbExpress and DataSnap components, but can also be used in a stand-alone setting.
The latter will be used in this little article.
To play along, create a new VCL application and place a TClientDataSet component on your form (or data module if you want), calling it cdsBiolife.
The example local MyBase dataset that I want to use is the infamous biolife.xml file, which can be found in the Program Files\Common Files\Borland Shared\Data directory.
If you right-click on the TClientDataSet component, the Fields Editor appears:
Here, we can add persistent fields, in the case of the Biolife table, consisting of the Species No, Category, Common_Nmae, Species Name, Length (cm), Length_In, Notes and Graphic fields.
This time, however, I'm not interested in those fields, but I want to create another type of field.
So right-click in the Fields Editor and select the New Field option, which gives the following dialog:
Note the five types of fields that we can create: Data fields (for normal fields that correspond to actual data fields in the database table), Calculated fields (for which we need to write some code in the OnCalcFields handler), Lookup fields (that need to be hooked up using the Lookup definition fields at the bottom of the dialog), InternalCalc fields (calculated fields which are stored in memory instead of being calculated in the OnCalcFields event handler), and finally Aggregate fields (the topic of this article).
For an example Aggregate field, we can specify the name MaxLength (resulting in the Component name cdsBiolifeMaxLength for the new field), as type Aggregate.
Unlike the other field types, an Aggregate field does not extend a record, but rather the entire dataset. This is illustrated by the Fields Editor itself, which will show the Aggregate fields in a separate section, as can be seen below:
Note that the MaxLength Aggregate field is the only explicitly defined field in the Fields editor.
Normally, this would mean that no other fields are available.
However, that rule only applies to "record"-fields, like the Data fields, Calculated fields, Lookup fields or the InternalCalc fields.
Since Aggregate fields are "dataset"-fields, they do not affect this, and as a result all fields from the Biolife table are still available to use at design-time.
The Aggregate field itself, however, has to be used somewhat differently.
By default, it won't show up in a TDBGrid for example, which only shows the "record"-fields:
Also, if we add all data fields to the Fields editor, you may notice in the Structure Pane that the MaxLength Aggregate field has an index of -1 in the Fields collection:
In order to display the Aggregate field, we have to use a TDBText control (which is read-only - it doesn't make any sense to update an aggregate field anyway).
However, it you place a TDBText control on the form, connect its DataSource property and open up the drop-down combobox for the DataField property, you'll see all field names, except for the Aggregate field name.
I don't know if this is a bug or a feature, but fortunately, you can just enter the name of the Aggregate field in the DataField property editbox (in this case, that's MaxLength).
After you've set the DataField property to MaxLength, you may notice that the DBText control shrinks down (since it shows no text at all).
That's not surprising, since we defined the Aggregate field, and even managed to connect it to the DBText control, but we never specified the actual aggregate expression.
The aggregate expression can be expressed in the expression property of the Aggregate field.
For our example, the MaxLength Aggregate field should probably have an expression of the form:
Max(Length_In)Note that it's a bit more difficult to express the Max on the "Length (cm)" field, but that can be done using square brackets (I still wonder who designed this biolife table using incredible fieldnames with spaces and all kinds of nasty characters inside):
Max([Length (cm)])Unfortunately, that will still not show the Aggregate values inside the Aggregate field. We have to set two more properties: to activate the Aggregate field(s), and have to do this at the Aggregate field(s) level, as well as at the ClientDataSet level (to active or deactivate all Aggregate fields all at once). For the AggregateField, we need to set the Active property to True, and then for the ClientDataSet we need to set the AggregatesActive property to True. If either of these is set to False, then the Aggregate value is not calculated and not shown!
In the above example, the Aggregate field is using the entire dataset to calculate the Aggregate values. However, it's also possible to limit the records on which to calculate these values, by grouping the records logically. For this, we can use the GroupingLevel property, but that's a story for another day...
