Distinct Count in fastreport

2
votes

Does anyone know how to make a distinct count in fastreport? Example I have the report:

 Name   sex
 João    m
 João    m
 Maria   f

In the normal count, the result would be 3, but I want one that takes only the number of rows that do not repeat the field name . In this case, the result would be 2. Can anyone help me? That's just an example. I can not do a group by in SQL because I have several fields.

2
delphifastreport
You have SQL Server? – Ilyes
No... Firebird... – sounobre

2 Answers

1
votes

I'm not skilled in using FastReport but I've found this page on the FastReport's official forum.

I think you can change the example by adapting it to your scenario (Note that the syntax could require some adjustments).

Bands:

GroupHeader1 <Sex> 
MasterData1 [Name, Sex, ...] 
GroupFooter1 [GetDistinctCount]

Script (Only working with dataset sorted by the field to count):

var 
  LastValue : string;
  DistinctCount : integer;

//create this event by double-clicking the event from the Object Inspector
procedure OnGroupHeader1.OnBeforePrint;
begin
  if LastValue <> (<Datasetname."Sex">) then
    Inc(DinstinctCount);

  LastValue := <Datasetname."Sex">
end;

function GetDistinctCount: string;
begin
  Result := IntToStr(DistinctCount);
end;

The base idea is that the DistinctCount variable is incremented each time the field value changes.

Script (Should works also with unsorted dataset):

var 
  FoundValues : array of string;


(* !!IMPORTANT!!
You need to initialize FoundValues array before to start counting: *)
SetLength(FoundValues, 0);


function IndexOf(AArray : array of string; const AValue : string) : integer;
begin
  Result := 0;
  while(Result < Length(AArray)) do
  begin
    if(AArray[Result] = AValue) then
      Exit;
    Inc(Result);
  end;
  Result := -1;
end;

//create this event by double-clicking the event from the Object Inspector
procedure OnGroupHeader1.OnBeforePrint;
begin
  if(IndexOf(FoundValues, <Datasetname."Sex">) = -1) then
  begin
    SetLength(FoundValues, Length(FoundValues) + 1);
    FoundValues[Length(FoundValues) - 1] := <Datasetname."Sex">;
  end;
end;

function GetDistinctCount: string;
begin
  Result := IntToStr(Length(FoundValues));
end;

The base idea is that each different value found is added to the FoundValues array.

0
votes

You can do it in Firebird without GROUP BY as:

DECLARE @T TABLE (ID INT IDENTITY (1,1), Name NVARCHAR(25) , Sex CHAR(1));

INSERT INTO @T VALUES
('Sami','M'),
('Sami','M'),
('Maria','F');

SELECT DISTINCT Name , Sex FROM @T

You can also create a View , and then use it in your report.

If you really need to do that in FastReport, you have to use a GroupHeader and a GroupFooter to that.

How ?

You have to write your script in OnBeforePrint event.

procedure OnGroupHeader1.OnBeforePrint;

Create this one by double-clicking in the event in the object inspector.