Subreport guidance
I’m just learning FastReports, and having some difficulty determining the best way to satisfy a requirement. In very simple terms, my data looks like this:
CustomerNum, Timestamp, CallType, Duration, PhoneNumber
I want to produce a report that shows all calls in chronological order for each customer in its own group, followed by a summary for each customer showing the number of calls and total time in the group footer. I also want to show the top 10 most frequently called numbers (for outbound calls) and the top 10 most frequent callers.
Customer 100 (group header)
2023-03-01 10:00:00AM INBOUND 100 9085551000
2023-03-01 10:05:00AM OUTBOUND 105 9085551001
2023-03-01 10:10:00AM INBOUND 110 9085551000
2023-03-01 10:15:00AM OUTBOUND 115 9085551001
2023-03-01 10:20:00AM INBOUND 120 9085551000
Customer 100 totals: 5 calls, 450 seconds (group footer)
Top 10 frequently called numbers (first sub-report)
Number Avg Duration Calls
9085551001 110 2
…
Top 10 frequent callers (second subreport)
Number Avg Duration Calls
9085551000 110 3
…
The detail and totals are simple. I thought of using a subreport for the frequently called / frequent callers – these two datasets would be simple SQL Queries – but I don’t see how you link the Customer Number in subreport queries to the Customer Number being reported in the group footer.
Can anyone confirm if subreports are the best solution to this requirement, and if so, how to have the parameters for the queries automatically updated so only the current customer is included? Or is there a better approach?
Thanks!
Comments
Resolved the issue as follows:
In the group footer, add a handler for the OnBeforePrint event:
procedure GroupFooter1OnBeforePrint(Sender: TfrxComponent);
begin
SetSubreportQuery (<frxDBDataset1."customerID">);
end;
Create a child band in the group footer, and add the subreport to the child.
In delphi code, write the handler for this function and have it refresh the subreport query after setting the CustomerID
procedure TForm1.SetSubreportQuery (ACustomer: string);
begin
SetQueryClient (ACustomer);
end;
procedure TForm1.SetQuery2Customer (ALinkValue: string);
begin
with FDQuery2 do begin
try
active := false;
Sql.Clear;
Sql.Add (lscSubreportSQL);
// use same date/time range as main report
ParamByName ('startDateTime').asDateTime := FStartingDateTime;
ParamByName ('endDateTime').asDateTime := FEndingDateTime;
// This ensures sub-report query only returns data for
// the current custsomer
ParamByName ('masterLinkValue').AsString := ALinkValue;
Prepare;
Open;
except
on e: exception do begin
ShowMessage ('Error on subreport query: ' + e.Message);
end;
end;
end;
end;
See the Fast Reports documentation for linking the Delphi procedure to the report.