Delphi form

This is an example of report similar to Northwind database Invoice report. The report displays data from several tables in an output document.

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 is TADOQuery component. Its SQL property contains the final select for the report. EkRTF1 is TEkRTF component. The report template is saved in NW_Invoice.rtf file. The name of the report template is set to EkRTF1.InFile property before executing.


Report template

All data fields in the report are linked to the Qr dataset. There is also a report variable Freight, declared at the beginning of the page. This variables stores the value of Qr.Freight field. We use this variable at the total block of the report.

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,
          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,
          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
                     [Order Details] ON Orders.OrderID = [Order Details].OrderID) INNER JOIN
                     Employees ON Orders.EmployeeID = Employees.EmployeeID) INNER JOIN
                     Products ON [Order Details].ProductID = Products.ProductID)
         WHERE     (Orders.OrderID = :OrderID)
          ORDER BY Orders.CustomerID, Orders.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;
  EkRtf1.OutFile := 'EK_RTF_NW_Invoice_' + Orders.FieldByName('OrderID').AsString + '.doc';
  EkRtf1.ExecuteOpen([Qr],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.