Subreport guidance

abhart

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!

abhart

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.