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.