Microsoft Excel Reporting Tool
 

Excel Report Template - Sales Detail

See Microsoft ® Excel ® Report

Excel Report Template - Sales Detail

Function and SQL

/*********************************************************
Parameter1:
Name: $ReportMonth
Format: YYYY-MM
*********************************************************/

/* BeginDate and EndDate */
@F1=NAME(NAME=BeginDate, EndDate)
SELECT DateSerial(Year('$ReportMonth-01'), MONTH('$ReportMonth-01'),1)
,DateAdd('d',-1,DateAdd('m',1,DateSerial(
	Year('$ReportMonth-01'), MONTH('$ReportMonth-01'),1)))
;

/* Sales Detail */
@F2=REPORT(sheet="Sales Detail" 
	cell=D5,D6,G6,C10,D10,E10,F10,G10,H10,C13,D13,E13,F13,G13,H13 
	group=1,2,3 grouprange=5:17 group=4,5,6,7,8,9 
	grouprange=8:16 pagebreak = 1g)
SELECT c.CompanyName AS Customer
,cci.CityName & ', ' & cco.CountryName AS CustomerCity
,c.ContactName
,o.OrderID
,e.FirstName & ' ' & e.LastName AS Salesperson
,o.OrderDate
,o.RequiredDate
,o.ShippedDate
,s.CompanyName AS Shipper
,d.ProductID
,p.ProductName
,d.Quantity
,d.UnitPrice
,d.Discount
,d.UnitPrice * d.Quantity * (1-d.Discount) AS Amount
FROM Orders o
, Customers c, Cities cci, Countries cco
, Employees e, Shippers s
, OrderDetails d, Products p
WHERE o.CustomerID = c.CustomerID
AND c.CityCode = cci.CityCode
AND c.CountryCode = cci.CountryCode
AND c.CountryCode = cco.CountryCode
AND o.EmployeeID = e.EmployeeID
AND o.ShipVia = s.ShipperID
AND o.OrderID = d.OrderID
AND d.ProductID = p.ProductID
AND YEAR(o.OrderDate) = YEAR('$ReportMonth-01')
AND MONTH(o.OrderDate) = MONTH('$ReportMonth-01')
ORDER BY 1, 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 (293KB)

 


Copyright © 2004 - 2006 LJZsoft Corporation. All rights reserved.