Firedac dataset aggregations

Home → Knowledgebase → Delphi → Firedac dataset aggregations

Did you know that in a Firedac query or table, you can work with runtime aggregations at the dataset level? Displaying a total amount of all orders loaded, or a total of all orders yet to be shipped can be done very easily without the need for a separate query.

Maybe you were already familiar with an aggregated field, but that is limited to row level. However, with the property Aggegrates of a TFDDataSet, you can also totalise at dataset level.

As an example, I have a demo program in which I retrieve all orders from a database into a table. I can filter these by store id. At the top right, you can see the number of orders yet to be sent. This number is calculated by an aggregated field of the query.

To start with, I define an aggregation field in the aggregation property of the query. The most important field here is Expression. This is where we define the aggregation we want to perform. Possibly with a condition in it. The standard Firedac expression syntax is used for this.

In the above example, I count the number of records (COUNT) of the field Shipped_Date with the condition that this field is counted only if the value is empty. For this, the function IIF
can be used. Other simple examples you can use here are:

SUM(order_amount)

MIN(order_date)

As you can see in the properties, you can set a field to active. If you don’t, it will obviously not be calculated. A name can also be useful, to be able to find the aggregation in the code.

Unfortunately, you cannot link an aggregation as a database field to a data-aware control. So rendering has to be handled in code. I created a separate procedure for this, so I can easily call it when a refresh is needed. I call this procedure in the AfterOpen event of the query and when filtering on a store.

procedure TfrmDemoApp.qryOrdersAfterOpen(DataSet: TDataSet);
begin
  ShowNotShippedAggregation;
end;

procedure TfrmDemoApp.ShowNotShippedAggregation;
begin
  var NotShipped := qryOrders.Aggregates.Items[0].Value;

  if NotShipped = Null then
     lblNotShipped.Caption := '0'
  else
    lblNotShipped.Caption := NotShipped;
end;

On the query, remember to set the property AggegratesActive to True, otherwise the aggregations will not be calculated. Another point to bear in mind: the aggregates operate on the data retrieved in the dataset. By default, Firedac retrieves 50 records. If you want a total which is based on all records go to the FetchOptions in the query properties and set the Mode to fmAll.

See below the result in the demo application.