Word Report Template - Account Statement
Function and SQL
/*********************************************************
Parameter1:
Name1: $BeginOID
Value Sample: 10470
Parameter2:
Name1: $EndOID
Value Sample: 10490
*********************************************************/
@F1=EXECSQL()
DROP TABLE tmp_order
@F2=EXECSQL()
SELECT o.OrderDate
,o.OrderID
,o.CustomerID
,SUM(d.UnitPrice * d.Quantity * (1-d.Discount)) AS Amount
,o.Freight
INTO tmp_order
FROM Orders o, OrderDetails d
WHERE o.OrderID = d.OrderID
AND o.OrderID BETWEEN $BeginOID AND $EndOID
GROUP BY o.OrderDate, o.OrderID, o.CustomerID, o.Freight
;
@F3=EXECSQL()
DROP TABLE tmp_customer_sum
@F4=EXECSQL()
SELECT c.CustomerID
,SUM(o.Amount + o.Freight) AS TotalAmt
INTO tmp_customer_sum
FROM tmp_order o, Customers c
WHERE o.CustomerID = c.CustomerID
GROUP BY c.CustomerID
;
/* Account Statement */
@F5=REPORT(type=form
cell=Customer,Address,City,PostalCode,TotalAmt
, OrderDate,InvoiceID,OrderID,DueDate,Amount,Freight
range=order group=1,2,3,4,5 NODATA=delrange)
SELECT c.CompanyName AS Customer
,c.Address
,ci.CityName & ', ' & co.CountryName AS City
,c.PostalCode
,t.TotalAmt
,o.OrderDate
,o.OrderID AS InvoiceID
,o.OrderID
,DateAdd('d',30,o.OrderDate) as DueDate
,o.Amount
,o.Freight
FROM Customers c, Cities ci, Countries co
, tmp_order o, tmp_customer_sum t
WHERE c.CityCode = ci.CityCode
AND c.CountryCode = ci.CountryCode
AND c.CountryCode = co.CountryCode
AND c.CustomerID = o.CustomerID
AND c.CustomerID = t.CustomerID
ORDER BY c.CustomerID, o.OrderID
;
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 (691KB)