How To...Count Distinct Records

Question 

"How do I count only the distinct records in my dataset using ReportBuilder?" 

Solution

Keep track of the distinct records using a TStringList object.

Download: CountDistinct.zip

Sample Delphi code:

procedure TForm1.FormCreate(Sender: TObject);
begin
  FDistincts := TStringList.Create;

end;

procedure TForm1.FormDestroy(Sender: TObject);
begin
  FDistincts.Free;

end;

procedure TForm1.ppReport1StartFirstPass(Sender: TObject);
begin
  FDistincts.Clear;

end;

procedure TForm1.ppDetailBand1BeforePrint(Sender: TObject);
var
  liIndex: Integer;
begin

  if ppReport1.FirstPass then
    begin
      //Check for a distinct value
      liIndex := FDistincts.IndexOf(plOrders['CustNo']);

      if (liIndex = -1) then
        FDistincts.Add(plOrders['CustNo']);
    end;

end;

procedure TForm1.ppSummaryBand1BeforePrint(Sender: TObject);
begin
  ppLabel5.Caption := IntToStr(FDistincts.Count);

end;