Excel Report Template - Account Statement

See Microsoft ® Excel ® Report

Function and SQL

/*********************************************************
Parameter1:
Name1: $BeginOID
Value Sample: 10470
Parameter2:
Name1: $EndOID
Value Sample: 10490
*********************************************************/

/* Account Statement */
@F1=REPORT(type=var sheet="Account Statement" 
cell=B5,B6,B7,B8,B17,C17,D17,E17,F17,G17
group=1,2,3,4 grouprange=4:23 pagebreak=1g nodata=delrange)
SELECT c.CompanyName AS Customer
,c.Address
,ci.CityName & ', ' & co.CountryName AS City
,c.PostalCode
,o.OrderDate
,o.OrderID AS InvoiceID
,o.OrderID
,DateAdd('d',30,o.OrderDate) as DueDate
,SUM(d.UnitPrice * d.Quantity * (1-d.Discount)) AS Amount 
,o.Freight
FROM Orders o, Cities ci, Countries co
, Customers c, OrderDetails d
WHERE c.CityCode = ci.CityCode 
AND c.CountryCode = ci.CountryCode 
AND c.CountryCode = co.CountryCode 
AND o.CustomerID = c.CustomerID 
AND o.OrderID = d.OrderID 
AND o.OrderID BETWEEN $BeginOID AND $EndOID
GROUP BY c.CompanyName,c.Address,ci.CityName,co.CountryName
,c.PostalCode,o.OrderDate,o.OrderID,o.Freight
ORDER BY c.CompanyName, o.OrderID
;

These sample reports are generated by XLReportGen (Report Generator for Microsoft Excel). The sample reports in Microsoft Excel spreadsheet 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 Excel spreadsheet format  XLS (293KB)