Calculated percentage in Delphi DBGrid

Question

I'm maintaining a Delphi application since a few days. The customer wants to add a percentage column to a DBGrid, now showing a "Quantity" column. Of course the percentage would be the Row quantity / Total Quantity * 100

I cannot modify the underlying TADOTable since it is used elsewhere in the code, but I tried to add a calculated field to the TADOTable - but is seems the calculated field cannot have a value based on an aggregate value (i.e. sum of quantity).

I succeeded in adding an empty column to the DBGrid, but is there any way to populate the % values in each row ?

Answer 1

What you want is straightforward to do, but you need to be careful how you do it.

Leaving the gui aspect aside for a moment, what you want to do is to add a calculated column to your TAdoDataSet and initialise it in its OnCalcFields event. However, what you DON'T want to do is to calculate the TotalQuantity in that event. because a) the OnCalcFields event will be called for each row in the dataset AND b) doing anything inside the OnCalcFields event which moves the dataset's cursor - like traversing the dataset as suggested in another answer - will recursively call the OnCalcFields event.

The way to avoid this recursion problem, and to avoid doing any more work than is necessary, is to only calculate the TotalQuantity when the table is first opened and anytime its value could change, that is, when a row is edited, inserted or deleted, and then save the result in a field of your form or datamodule. There are two main ways you could do this calculation: 1) using a TAdoQuery to execute Sql like "SELECT SUM(Quantity) FROM MyTable" or 2) using a second instance of TAdoDataSet opened on your table. Preferably this second instance should not have any gui controls connected to it, so it can be traversed as quickly as possible without having to use DisableControls and EnableControls.

To add the Percentage field to your AdoDataSet, double click it to pop up the Fields Editor, right-click in it and select New field. Make sure you set its Type to Calculated.

Once you have your GetTotalQuantity procedure set up, you need to set event handlers to call it from your AdoDataSet's BeforeOpen, BeforeInsert, BeforeEdit and AfterDelete events.

Then in your OnCalcFields event, calculate and assign the value of your Percentage calculated to it.

The code required to do all this is pretty trivial, something like

procedure TForm1.GetTotalQuantity;
begin
  //  AdoQuery1 contains Sql to obtain the sum of the AdoDataSet's
  if AdoQuery1.Active then
    AdoQuery1.Close;
  AdoQuery1.Open;
  try
    TotalQuantity := AdoQuery1.Fields[0].AsFloat;  //  TotalQuantity is a field of your for, (or datamodule)
  finally
    AdoQuery1.Close;
  end;
end;

Or

procedure TForm1.GetTotalQuantity;
begin
  //  Note: AdoDataSet2 is a second instance of TAdoDataSet set up to access the same
  //  db table as the one connected to the OP's DBGrid
  if AdoDataSet2.Active then
    AdoDataSet2.Close;
  AdoDataSet2.Open;
  try
    TotalQuantity := 0;
    while not AdoDataSet2.Eof do begin
      TotalQuantity :=  TotalQuantity + AdoDataSet2Quantity.AsFloat; // AdoDataSet2.Quantity.AsFloat;
      AdoDataSet2.Next;
    end;
  finally
    AdoDataSet2.Close;
  end;
end;

OnCalcFields event:

procedure TForm1.AdoDataSet1CalcFields(DataSet : TDataSet);
begin
  if TotalQuantity > 0 then
    AdoDataSet1Percentage.AsFloat := AdoDataSet1Quantity.AsFloat / Total Quantity * 100;
end;

Once you've added your Percentage calculated field to your AdoDataSet and set up the OnCalcFields event for the dataset, your DBGrid will be happy to display it, just like any other field of the dataset.

Answer 2

Have you tried calculating the percentage in the OnCalcFields event? Not 100% sure about the following but this example might help you:

procedure TClass.DataSetCalcFields(DataSet: TDataSet);
var
  Bookmark: String;
  TotalQuantity: Double;
begin
    // Save current position
    Bookmark := Dataset.Bookmark;

    // Calculate the total quantity in a while loop through the dataset:
    Dataset.First;
    while not Dataset.Eof do
    begin
      TotalQuantity := TotalQuantity + Dataset.FieldByName('QUANTITY').AsFloat;
      Dataset.Next;
    end;

    // Load current position
    Dataset.Bookmark := Bookmark;

    // Calculate the percentage:
    if TotalQuantity > 0 then
    begin
      Dataset.FieldByName('PERCENTAGE').AsFloat := Dataset.FieldByName('QUANTITY').AsFloat / TotalQuantity * 100;
    end
    else
    begin
      Dataset.FieldByName('PERCENTAGE').AsFloat := 0;
    end;
  end;
end;