Word Report Template - Invoice
Function and SQL
@F1=EXECSQL() DROP TABLE tmp_order /* Get sum amount by order */ @F2=EXECSQL() SELECT o.OrderID ,o.OrderDate ,o.CustomerID ,o.EmployeeID ,o.ShipVia ,o.ShipName ,o.ShipAddress ,o.ShipPostalCode ,o.RequiredDate ,o.ShippedDate ,o.ShipCityCode ,o.ShipCountryCode ,o.Freight ,SUM(d.UnitPrice * d.Quantity * (1-d.Discount)) AS SumAmt INTO tmp_order FROM Orders o, OrderDetails d WHERE o.OrderID = d.OrderID AND o.OrderDate Between #1995-04-20# And #1995-05-03# GROUP BY o.OrderID,o.OrderDate,o.CustomerID,o.EmployeeID ,o.ShipVia,o.ShipName,o.ShipAddress,o.ShipPostalCode ,o.RequiredDate,o.ShippedDate,o.ShipCityCode ,o.ShipCountryCode,o.Freight ; /* Invoice */ @F3=Report(table=1 cell=B1,B2,B3,B4,E1,E2,E3,E4,A7,B7,C7,D7,E7,F7,G7,G12,G13,G14,A10,B10,D10 group=1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18 grouprange=1:15 pagebreak = 1g) SELECT o.ShipName ,o.ShipAddress ,ci.CityName & ', ' & co.CountryName AS ShipCity ,o.ShipPostalCode ,c.CompanyName AS Customer ,c.Address ,cci.CityName & ', ' & cco.CountryName AS CustomerCity ,c.PostalCode ,o.OrderID ,o.CustomerID ,e.FirstName & ' ' & e.LastName AS Salesperson ,o.OrderDate ,o.RequiredDate ,o.ShippedDate ,s.CompanyName AS Shipper ,o.SumAmt ,o.Freight ,o.Freight + o.SumAmt ,d.ProductID ,p.ProductName ,d.Quantity ,d.UnitPrice ,d.Discount ,d.UnitPrice * d.Quantity * (1-d.Discount) AS Amount FROM tmp_order o, Cities ci, Countries co , Customers c, Cities cci, Countries cco , Employees e, Shippers s , OrderDetails d, Products p WHERE o.ShipCityCode = ci.CityCode AND o.ShipCountryCode = ci.CountryCode AND o.ShipCountryCode = co.CountryCode AND c.CityCode = cci.CityCode AND c.CountryCode = cci.CountryCode AND c.CountryCode = cco.CountryCode AND o.CustomerID = c.CustomerID AND o.EmployeeID = e.EmployeeID AND o.ShipVia = s.ShipperID AND o.OrderID = d.OrderID AND d.ProductID = p.ProductID ORDER BY o.OrderID, d.ProductID ;
These sample reports are generated by WDReportGen (Report Generator for Microsoft Word). The sample reports in Microsoft Word document format can be download for your report templates, and changed to adapt to your own needs. It is free.
Download the report samples in Microsoft Word document format DOC (472KB)