Printing a Group Sum in the Group Header
Printing a Group Sum in the Group Header
This method is used quite often and requires the use of a script because the value of a group sum is only known after all the records in the group have been processed. To display the sum in the group header (i.e. before the group is output to the report) the following algorithm is used:
- turn on the two-pass report option (“Report > Options...” menu item)
- in the first pass, calculate the sums for each group and save them in an array
- in the second pass, extract the values from the array and display them in the group headers
Let's show two methods for performing this task. First create a clone of report '147 - Stock as of Date by Warehouses and Items' that be found in program menu 'Goods | Stock Reports | Current Stock'.
Enable double-pass in the report's settings (“Report > Options...” menu item). In the “GroupHeader” band's editor, enter the tbIzpisTrenZaloga."acWarehouse" data field. Connect the data band to the “Group” data source and then arrange some objects in the following way:
To display the sum we use the colored objects in the design. We named them "Sum1" and "Sum2".
In object named "Sum1" result of the first method will be displayed and in in object named "Sum2" result of the second method will be displayed.
The first method.
We will use the "TStringList" class as an array for storing the sums - we will be storing the numeric values as strings. The first item in the StringList will correspond to the sum of the first group, as so on. An integer variable (which we will increment after printing each group) is used to calculate the group's index number.
So our script will look like this:
var
List: TStringList;
i: Integer;
procedure OnStartReport(Sender: TfrxComponent);
begin
List := TStringList.Create;
end;
procedure OnStopReport(Sender: TfrxComponent);
begin
List.Free;
end;
procedure Page1OnBeforePrint(Sender: TfrxComponent);
begin
i := 0;
end;
procedure hSkladOnBeforePrint(Sender: TfrxComponent);
begin
if Engine.FinalPass then
Sum1.Text := 'Sum: ' + List[i];
end;
procedure fSkladOnBeforePrint(Sender: TfrxComponent);
begin
if not Engine.FinalPass then
List.Add(FormatFloat('#,##0.00',SUM(<tbIzpisTrenZaloga."ZALVREDNOST">, Detail)));
Inc(i);
end;
begin
end.
The procedure names in the script show which events we have used. They are: “Report.OnStartReport”, “Report.OnStopReport”, "Page1.OnBeforePrint", "hSkladHeader1.OnBeforePrint" and "fSklad.OnBeforePrint". The first two events are called at the beginning and the end of the report respectively. To create handlers for these two events select the “Report” object in the object inspector's drop-down list and its properties will appear in the object inspector. Then switch to the object inspector's “Events” tab and create the handlers.
Why didn't we create the “List” variable in the script's main procedure? We created it in the "OnStartReport" event because dynamically created variables should be destroyed after the report has been finished. It is logical to create dynamic variables in the “OnStartReport” event and destroy them in the “OnStopReport” event. In other cases (when memory does not need to be freed on completion of the script) one can use the script's main procedure for initialization of variables.
The creation and destruction of the “List” variable is straight forward. Now let's see how the script works. At the start of the page the counter for the current group (the variable “i”) is reset to zero and it is incremented after each group has been printed (in the “GroupFooter1.OnBeforePrint” event). The calculated sum is added to “List” in this event before the counter is incremented. The “GroupHeader1.OnBeforePrint” event does nothing during the first pass (If “Engine.FinalPass” condition) but during the second pass (when “List” has been filled with values) the sum corresponding to the current group is retrieved from “List” and is output to the "Sum1" object to display the sum in the group header. In the finished report, it appears as follows:
This algorithm is quite straight forward. However, it can be simplified.
The second method.
We will use the collection of report variables as an array for storing the group sums. Remember that report variables are accessed via the “Get” and “Set” functions. Using these functions also saves us from having to explicitly create and destroy these variables. Our script will look as follows:
procedure hSkladOnBeforePrint(Sender: TfrxComponent);
begin
if Engine.FinalPass then
Sum2.Text := 'Sum: ' + FormatFloat('#,##0.00', Get(<tbIzpisTrenZaloga."acWarehouse">));
end;
procedure GroupFooter1OnBeforePrint(Sender: TfrxComponent);
begin
Set(<tbIzpisTrenZaloga."acWarehouse">, SUM(<tbIzpisTrenZaloga."ZALVREDNOST">, Detail));
end;
begin
end.
As you can see, this script is somewhat simpler. Code in the “fSklad.OnBeforePrint” handler sets the value of a variable having a name derived from the client number (or any other identifier which unambiguously identifies the client could be used, for example <tbIzpisTrenZaloga."acWarehouse">). If there isn't a variable with that name already existing then the script automatically creates it; otherwise if it does exist then its value is updated. In the “hSklad.OnBeforePrint” handler the value of the appropriate variable is retrieved.
The finished report appears as follows:
The second method does not work for multiple grouping and for displaying multiple sums of fields in the group header.
An example of several sums of fields in the header of the group is on the report '32A - Late Payments - By Linked Documents' that is in the program menu 'Financials | Reports | Late Payments'.