Calculated fields are the virtual fields, whose values are not fetched / stored in database. Instead they are calculated on the client. AnyDAC supports calculated fields of all TField.FieldKind types:

• fkCalculated - a simple calculated field. The value is calculated in TDataSet.OnCalcFields event handler.
• fkInternalCalc - an advanced calculated field. The value may be assigned as to regular fields and is stored in the dataset records cache. The value is calculated in TDataSet.OnCalcFields event handler or using expression specified by TField.DefaultExpression.
• fkLookup - a lookup fields. The value is calculated automatically, providing a value from a lookup dataset for a key value from this dataset.
• fkAggregate - an aggregate calculating field. The values is calculated using expression specified by TAggregateField.Expression, which includes COUNT, SUM, MIN, MAX, AVG aggregate functions.

Only fkInternalCalc and fkAggregate fields may be used in filtering, sorting or locating. Also they will be stored with other dataset fields into persistent stream or file. The calculated field values cannot be posted to a database in automatic mode. 

fkCalculated and fkInternalCalc calculated field values may be assigned by the TDataSet.OnCalcFields event handler. A calculated field may be defined:

• at design-time using the dataset "Fields Editor ..." menu item;
• at run-time using the code. For example, to create a calculated field, that contains upper cased name:

procedure TForm1.Form1CalcFields(ADataSet: TDataSet);
begin
  ADataSet.FieldByName('UName').AsString := UpperCase(ADataSet.FieldByName('Name').AsString);
end;

var
  oField: TField;
  i: Integer;
...
ADQuery1.FieldDefs.Updated := False;
ADQuery1.FieldDefs.Update;
for i := 0 to ADQuery1.FieldDefs.Count - 1 do
  ADQuery1.FieldDefs[i].CreateField(Self);

oField := TStringField.Create(ADQuery1);
oField.Size := 50;
oField.FieldName := 'UName';
oField.FieldKind := fkInternalCalc; // or fkCalculated
oField.DataSet := ADQuery1;

ADQuery1.OnCalcFields := Form1CalcFields;
ADQuery1.Open;

fiInternalCalc fields may be calculated automatically by an expression specified by the TField.DefaultExpression. The OnCalcFields event handler and explicit value assignment is not needed then. The expression cannot be changed when the dataset is active. For example: 

var
  oField: TField;
  i: Integer;
...
ADQuery1.FieldDefs.Updated := False;
ADQuery1.FieldDefs.Update;
for i := 0 to ADQuery1.FieldDefs.Count - 1 do
  ADQuery1.FieldDefs[i].CreateField(Self);

oField := TStringField.Create(ADQuery1);
oField.Size := 50;
oField.FieldName := 'UName';
oField.FieldKind := fkInternalCalc;
oField.DefaultExpression := 'UPPER(Name)';
oField.DataSet := ADQuery1;

ADQuery1.Open;

fkAggregate aggregate fields management is similar to the expression calculated fields. AnyDAC calculates aggregate fields when TADDataSet.AggregatesActive is True, by default it is False. The aggregate expression cannot be changed when the dataset is active. For example to create an aggregated field: 

var
  oField: TAggregateField;
  i: Integer;
...
ADQuery1.FieldDefs.Updated := False;
ADQuery1.FieldDefs.Update;
for i := 0 to ADQuery1.FieldDefs.Count - 1 do
  ADQuery1.FieldDefs[i].CreateField(Self);

oField := TAggregateField.Create(ADQuery1);
oField.FieldName := 'Total';
oField.Expression := 'SUM((ItemPrice + ItemTaxes) * ItemCount)';
oField.DataSet := ADQuery1;

ADQuery1.AggregatesActive := True;
ADQuery1.Open;

An aggregated field may define grouping. Then a value is calculated for records with the same index field values, instead of all records. To define grouping set:

• TAggregateField.IndexName to a name of the index to use for grouping. By default - the current index is used.
• TAggregateField.GroupingLevel to a number of indexed fields to use for grouping. By default - 0 (no fields and no grouping).

Note, aggregated field always return Null value, when a dataset is in dsInsert state. 

Also, AnyDAC application may use TADDataSet.Aggregates collection to define aggregated values. They are more light-weighted than fields and may define at any time, including when dataset is active. For example: 

with ADQuery1.Aggregates.Add do begin
  Name := 'Total';
  Expression := 'SUM((ItemPrice + ItemTaxes) * ItemCount)';
  Active := True;
end;
ADQuery1.AggregatesActive := True;
...
Label1.Caption := VarToStr(ADQuery1.Aggregates[0].Value);

Sorting Records, Filtering Records, Writing Expressions