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.

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;

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;

procedure TForm1.ppSummaryBand1BeforePrint(Sender: TObject);

begin

ppLabel5.Caption := IntToStr(FDistincts.Count);

end;