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
