[Solved] LazReport Aggregate COUNT() on group-band

[HGabor]

Hello,

I have the following problem. I have an SQL query (Zeos+MySQL) which gives me a single table, let's say costumers and their acquisitions:

CustCode, CustName, CustItem, CustItemPrice

Of course customers may have several items, so this is a kind of master-detail, but I'd like to handle it as a single table, being quite small.


The report has four main bands:

GroupHeader1:      CustCode, CustName
MasterData1:            Item.name CustItemPrice
GroupFooter1:       SUM([CustItemPrice],MasterData1)
ReportSummary1: COUNT([CustCode],GroupHeader1), COUNT([CustItem],MasterData1),
                               SUM([CustItemPrice],MasterData1)

The problem is that the COUNT() and SUM() from MasterData1 are working fine, while the value of COUNT() from GroupHeader1 is always zero.
The help says that the second parameter of COUNT() is a band. GroupHeader and GroupFooter are bands, so I think it should work. I tried to put something on the GroupFooter1 band and count it, but it doesn't work either. Nor does SUM, BTW.

I am using the latest release of Lazarus from SourceForge (I've downloaded and installed today) under Win XP SP3.

Any help highly appreciated. I wanted to ask first and then report this as a possible error.

Thanks in advance:

Gabor

[HGabor]

With the kind help of Zeljan Rikalo I could find a workaround. He told me that the aggregate functions are working only with databands, not with bands. So a possible workaround is the following:

As a rule, we declare that the Count() function simply counts the written lines. Being the GroupHeader one line (I mean it will be written only once per group change)  we can say that one count variable per group is enough.
We do not change the name of the GroupHeaders, so they will be GroupHeader1..GroupHeaderN. In this case we can use variables to count their lines [COUNT_1]..[COUNT_N].

In the report's OnBeginDoc event we create the necessary COUNT_? variables as frVariables. We can simply make them as  frVariables.Variable['COUNT_?'] := 0;   or in a loop, while frReport1.FindObject('GroupHeader?') <> nil.

Then, the OnBeginBand event looks like this:

Code: [Select]

procedure TForm1.frReport1BeginBand(Band: TfrBand);
var idx     : integer;
    varname : string;
    ghdr    : TfrBandView;

    function FindBandNumber(cnd:string): integer;
    var i  : integer;
        hd : TfrBandView;
    begin
      Result := -1;
      i := 1;
      hd := frReport1.FindObject('GroupHeader' + IntToStr(i)) as TfrBandView;
      while (Result < 1) and (hd <> nil) do
       begin
         if hd.GroupCondition = cnd
           then Result := i;
            Inc(i);
            hd := frReport1.FindObject('GroupHeader' + IntToStr(i)) as TfrBandView;
       end;
    end;

begin
    if (Band.Typ = btGroupHeader)
      then try
             idx := FindBandNumber(Band.GroupCondition);
             if idx > 0
               then varname := 'COUNT_' + IntToStr(idx);
             if (frVariables.IndexOf(varname) >= 0) and ((not frReport1.DoublePass) or (frReport1.DoublePass and (not frReport1.FinalPass)))
               then frVariables.Variable[varname] := frVariables.Variable[varname] + 1;
           finally
           end;
end;                     
 

The FindBandNumber() function is necessary because in my case the Band.Name
property always gives an empty string - even if it is GroupHeader1 in the ObjectInspector.
The first solution I could find to connect the band to its count variable was through its GroupCondition property.

I have one group only, so now it isn't confusing, but I think this workaround should work with several bands too.

I'm sure there can be some more elegant solution, but it seems working and unfortunately I do not have more time to mess around this problem.


Gabor

[zeljko]

@HGabor, you don't need to code anything in application. You can define variables and use OnBeforePrint of groupheaders,footers, databand etc ...

[HGabor]

Zeljko, unfortunately I couldn't find anything like this if I am using the DesignReport possibility to make a report.

Where can I find OnBeforePrint events on the DesignReport page? There are no events on the ObjectInspector, only properties.

I am making a common report generator using LazReport, that I am planning to use in all of my programs, so even if I could find the mentioned OnBeforePrint event for the bands  it is more comfortable to me to simply use a 'COUNT_1' variable on the page than code this feature in dozens of reports.

But probably sometimes it would be handy if I could use these events.

[zeljko]

ah ok then.

[HGabor]

Zeljko, if you could write me where to find those events, that would be great anyway. I don't know if my version (the one came with the last Win32 install) is wrong, or I am blind. 

Thinking twice those would be great when possibly I had to use nested groups, because with them the reinitialization of the variables would be much easier.

Thanks for your help.

[zeljko]

@HGabor, those events should be visible in report designer. eg. when you drop down an groupheader onto page in objectinspector should be events (OnBeforePrint ...) where you can edit your code (at least they're here, but I don't use LazReport, instead I use FastReport2.5 clx (ported to lazarus)).

[tcmdvm]

Unfortunately, there is no OnBeforePrint currently in Lazreport. It would be nice if there was it would make it easier to get some code to work.

It might be possible to make a variable and then right the code to increment the variable to get counts from a groupheader.

[HGabor]

I thought, there will be something like this.  I saw in the FastReport documentation that there are events, but theye're missing from LazReport.

Anyway, my approach works fine and must be coded only once and then load the reports into the frReport component.

If necessary, for nested groups a workaround may be that when the actual band in in the OnBeginBand event has a COUNT_N variable, we re-initialize it.