上QQ阅读APP看书，第一时间看更新

How to do it...

Let's look at the following steps:

Create a new VCL application by selecting File | New | VCL Forms Application.

Put a DBNavigator (aligned to the top), a DBGrid (aligned to the client), a DataSource, and a PopUpMenu into the form.
Set the DataSource property of DBGrid1 to DataSource1.
Select the EMPLOYEE connection in the Data Explorer and then drag and drop it on the form to generate the EmployeeConnection.
Put a TFDTable in the form and rename it to SalesTable.
The connection property of SalesTable is automatically set to EmployeeConnection.
Set the DataSet property of DataSource1 to SalesTable.
To choose the Table, you have to expand the Table property combobox and select SALES:

Figure 1.25: SalesTable in the Object Inspector

If you performed all the steps correctly, you should be in this situation:

Figure 1.26: Form at design time

Declare the CreateIndexes procedure under the private section of the form and implement it with the following code:

procedure TMainForm.CreateIndexes;
var
  LCustNoIndex: TFDIndex;
begin
  LCustNoIndex := SalesTable.Indexes.Add;
  LCustNoIndex.Name := 'MyCustNoIdx';
  LCustNoIndex.Fields := 'Cust_No';
  LCustNoIndex.Active := true;
end;

Declare the CreateAggregates procedure under the private section of the form and implement it with the following code:

procedure TMainForm.CreateAggregates;
begin
  with SalesTable.Aggregates.Add do
  begin
    Name := 'CustomerTotal';
    Expression := 'SUM(TOTAL_VALUE)';
    GroupingLevel := 1;
    Active := true;
    IndexName := 'MyCustNoIdx';

  end;

  with SalesTable.Aggregates.Add do
  begin
    Name := 'CustomerMax';
    Expression := 'MAX(TOTAL_VALUE)';
    GroupingLevel := 1;
    Active := true;
    IndexName := 'MyCustNoIdx';

  end;

  with SalesTable.Aggregates.Add do
  begin
    Name := 'CustomerLastDate';
    Expression := 'MAX(ORDER_DATE)';
    GroupingLevel := 1;
    Active := true;
    IndexName := 'MyCustNoIdx';

  end;
end;

Now, we are able to set up the SalesTable component. So, implement the OnCreate event handler for the form and include this code:

procedure TMainForm.FormCreate(Sender: TObject);
begin
  SalesTable.Active := false;
  CreateIndexes;
  CreateAggregates;
  SalesTable.IndexName := 'MyCustNoIdx';
  // index activated
  SalesTable.IndexesActive := true;
  // aggregates activated
  SalesTable.AggregatesActive := true;
  SalesTable.Active := true;
end;

Now, we have to implement DBGrid1TitleClick to perform the right sorting method when the user clicks on a specific title:

procedure TMainForm.DBGrid1TitleClick(Column: TColumn);
begin

  // if reset the column caption of LastColumnClickIndex, because index could be change...
  if FLastColumnClickIndex > 0 then
    DBGrid1.Columns[FLastColumnClickIndex].Title.Caption :=
      DBGrid1.Columns[FLastColumnClickIndex].FieldName;

  // if the order is descending set the IndexFieldNames to ''.
  if SalesTable.IndexFieldNames = (Column.Field.FieldName + ':D') then
  begin
    Column.Title.Caption := Column.Field.FieldName;
    SalesTable.IndexFieldNames := '';
  end
  // if the order is ascending set it to descending
  else if SalesTable.IndexFieldNames = Column.Field.FieldName then
  begin
    SalesTable.IndexFieldNames := Column.Field.FieldName + ':D';
    Column.Title.Caption := Column.Field.FieldName + ' ▼';
  end
  // if no order is specified I'll use ascending one
  else
  begin
    SalesTable.IndexFieldNames := Column.Field.FieldName;
    Column.Title.Caption := Column.Field.FieldName + ' ▲';
  end;

  // set last column index
  FLastColumnClickIndex := Column.Index;

end;

It's time to insert the aggregates. The goal is to show some aggregated information through a simple ShowMessage procedure. Add a new menu item to PopupMenu1, rename it to Customer Info, and implement the OnClick event with the following code:

procedure TMainForm.CustomerInfoClick(Sender: TObject);
var
  LOldIndexFieldNames: string;
begin

  // i use LOldIndexFieldNames to reset the index to last user choice
  LOldIndexFieldNames := SalesTable.IndexFieldNames;
  DBGrid1.Visible := false;
  // the right index for aggregate
  SalesTable.IndexName := 'MyCustNoIdx';

  // show some customer info
  ShowMessageFmt('The total value of order of this customer is %m. ' +
    'The max value order of this customer is %m. ' + 'Last order on %s ',
    [StrToFloat(SalesTable.Aggregates[0].Value),
    StrToFloat(SalesTable.Aggregates[1].Value),
    DateTimeToStr(SalesTable.Aggregates[2].Value)]);

  SalesTable.IndexFieldNames := LOldIndexFieldNames;
  DBGrid1.Visible := true;
end;

Run the application by hitting F9 (or by going to Run | Run):

Figure 1.27: Amazing FDTable at startup

Click on the Total Value column twice in the descending order:

Figure 1.28: Descending order on total_value field

Right-click on the first record to bring up the pop-up menu, then click on Customer Info:

Figure 1.29: Aggregates in action