Excel Report Template - Invoice

See Microsoft ® Excel ® Report

Function and SQL

@F1=Report(sheet="Invoice" 
  cell=B7,B8,B9,B10,E7,E8,E9,E10,A13,B13,C13,D13,E13,F13,G13,G19,A16,B16,D16 
  group=1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16
  grouprange=7:21 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.Freight
,d.ProductID
,p.ProductName
,d.Quantity
,d.UnitPrice
,d.Discount
,d.UnitPrice * d.Quantity * (1-d.Discount) AS Amount 
FROM Orders 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
AND o.OrderDate Between #1995-4-20# And #1995-5-3# 
ORDER BY o.OrderID, d.ProductID
;

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 (301KB)