|
|
|||||
|
|||||
|
|
|
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 new project in Delphi, place “TQuery”, “TfrxReport” and “TfrxDBDataSet” components on the form and set them up as follows:
Query1:
DatabaseName = 'DBDEMOS'
SQL =
select * from customer, orders
where orders.CustNo = customer.CustNo
order by customer.CustNo, orders.OrderNo
frxDBDataSet1:
DataSet = Query1
UserName = 'Group'
Open the designer and connect the data source to the report. Enable double-pass in the report's settings (“Report > Options...” menu item). Add two bands to the report: “GroupHeader” and “MasterData”. In the “GroupHeader” band's editor, enter the Group.“CustNo” 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 arrowed object in the design (in our example it is named “Memo8”).
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:
PascalScript:
var
List: TStringList;
i: Integer;
procedure frReport1OnStartReport(Sender: TfrxComponent);
begin
List := TStringList.Create;
end;
procedure frReport1OnStopReport(Sender: TfrxComponent);
begin
List.Free;
end;
procedure Page1OnBeforePrint(Sender: TfrxComponent);
begin
i := 0;
end;
procedure GroupHeader1OnBeforePrint(Sender: TfrxComponent);
begin
if Engine.FinalPass then
Memo8.Text := 'Sum: ' + List[i];
end;
procedure GroupFooter1OnBeforePrint(Sender: TfrxComponent);
begin
if not Engine.FinalPass then
List.Add(FloatToStr(SUM(<Group."ItemsTotal">,MasterData1)));
Inc(i);
end;
begin
end.
C++ Script:
TStringList List;
int i;
void frReport1OnStartReport(TfrxComponent Sender)
{
List = TStringList.Create();
}
void frReport1OnStopReport(TfrxComponent Sender)
{
List.Free();
}
void Page1OnBeforePrint(TfrxComponent Sender)
{
i = 0;
}
void GroupHeader1OnBeforePrint(TfrxComponent Sender)
{
if (Engine.FinalPass)
Memo8.Text = "Sum: " + List[i];
}
void GroupFooter1OnBeforePrint(TfrxComponent Sender)
{
List.Add(FloatToStr(SUM(<Group."ItemsTotal">,MasterData1)));
i++;
}
{
}
The procedure names in the script show which events we have used. They are: “Report.OnStartReport”, “Report.OnStopReport”, “Page1.OnBeforePrint”, “GroupHeader1.OnBeforePrint” and “GroupFooter1.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 “Memo8” 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:
PascalScript:
procedure GroupHeader1OnBeforePrint(Sender: TfrxComponent);
begin
if Engine.FinalPass then
Memo8.Text := 'Sum: ' + Get(<Group."CustNo">);
end;
procedure GroupFooter1OnBeforePrint(Sender: TfrxComponent);
begin
Set(<Group."CustNo">,
FloatToStr(SUM(<Group."ItemsTotal">,MasterData1)));
end;
begin
end.
C++ Script:
void GroupHeader1OnBeforePrint(TfrxComponent Sender)
{
if (Engine.FinalPass)
Memo8.Text = "Sum:" + Get(<Group."CustNo">);
}
void GroupFooter1OnBeforePrint(TfrxComponent Sender)
{
Set(<Group."CustNo">,
FloatToStr(SUM(<Group."ItemsTotal">,MasterData1)));
}
{
}
As you can see, this script is somewhat simpler. Code in the “GroupFooter1.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 <Group."Company">). 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 “GroupHeader1.OnBeforePrint” handler the value of the appropriate variable is retrieved.
|
