Delphi form

This is modification of the previous example Invoice report. The report displays data from several tables in an output document. One SQL is divided on two queries - order and order details.

There are two TADOTable components on a Form1. First is connected to the table Customers. The second is connected to the table Orders. Current OrderID is input parameter for the report. Qr and Qr2 are TADOQuery components. Qr's SQL property contains the final select for the report. Qr2 SQL contains order details subquery. EkRTF1 is TEkRTF component. The report template is saved in NW_Invoice_2.rtf file. The name of the report template is set to EkRTF1.InFile property before executing.


Report template

Fields for ship information and order are linked to the Qr dataset. Order details block is linked to Qr2 fields.

There are also EK RTF commands \if\-\else\-\endif\ used in the address blocks. Commands \scan\-\endscan\ are used to print order details table. Fdtm, fcur and fnum are format functions.

Code to generate the report

SQL in Qr component is:

SELECT Customers.Address,
          Customers.City,
          Customers.Region,
          Customers.PostalCode,
          Customers.Country,
          Customers.CompanyName,
          Orders.OrderID,
          Orders.CustomerID,
          Orders.EmployeeID,
          Orders.OrderDate,
          Orders.RequiredDate,
          Orders.ShippedDate,
          Orders.ShipVia,
          Shippers.CompanyName AS Shp_CompanyName,
          Employees.LastName,
          Employees.FirstName,
          Orders.Freight,
          Orders.ShipName,
          Orders.ShipAddress,
          Orders.ShipCity,
          Orders.ShipRegion,
          Orders.ShipPostalCode,
          Orders.ShipCountry
        FROM         (((Orders INNER JOIN
                     Customers ON Orders.CustomerID = Customers.CustomerID) INNER JOIN
                     Shippers ON Orders.ShipVia = Shippers.ShipperID) INNER JOIN
                     Employees ON Orders.EmployeeID = Employees.EmployeeID)
         WHERE     (Orders.OrderID = :OrderID)
          ORDER BY Orders.CustomerID,
Orders.OrderID

SQL in Qr2 component is:

SELECT    Products.ProductName,
          [Order Details].ProductID,
          [Order Details].UnitPrice,
          [Order Details].Quantity,
          [Order Details].Discount,
          ROUND([Order Details].UnitPrice * [Order Details].Quantity *((1 - [Order Details].Discount)/100)*100,2) AS ExtPrice
        FROM     ([Order Details] INNER JOIN
                   Products ON [Order Details].ProductID = Products.ProductID)
         WHERE     [Order Details].OrderID = :OrderID
          ORDER BY  [Order Details].OrderID

Delphi code is:

procedure TForm1.Button2Click(Sender: TObject);
begin
(* check report template file *)
if not FileExists(EkRTF1.InFile) then
begin
if RTFDialog.Execute then begin
EkRTF1.InFile := RTFDialog.FileName;
end else exit;
end;
(* create report *)
Qr.Close;
Qr.Parameters.ParamByName('OrderID').Value := Orders.FieldByName('OrderID').AsInteger;
Qr.Open;
  Qr2.Close;
  Qr2.Parameters.ParamByName('OrderID').Value := Orders.FieldByName('OrderID').AsInteger;
  Qr2.Open;
  EkRtf1.OutFile := 'EK_RTF_NW_Invoice_2_' + Orders.FieldByName('OrderID').AsString + '.doc';
  EkRtf1.ExecuteOpen([Qr, Qr2],SW_SHOW);
end;

 

Result

Output format of date and currency fields depends on regional setting of operating system and may be different in other case.