Microsoft Word Reporting Tool
 

Word Report Template - Sales Detail

See Microsoft ® Word ® Report

Word Report Template - Sales Detail

Function and SQL

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

/* DROP Table tmp_sales_detail */
@F2=EXECSQL()
DROP TABLE tmp_sales_detail
;
/* DROP Table tmp_sales_sum1 */
@F3=EXECSQL()
DROP TABLE tmp_sales_sum1
;
/* DROP Table tmp_sales_sum2 */
@F4=EXECSQL()
DROP TABLE tmp_sales_sum2
;


/* Get sales detail information */
@F5=EXECSQL()
SELECT o.CustomerID
,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
INTO tmp_sales_detail
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')
;


/* Execulte summary amount by customer, order */
@F6=EXECSQL()
SELECT CustomerID
, OrderID
, SUM(Amount) AS SumAmt
INTO tmp_sales_sum1
FROM tmp_sales_detail
GROUP BY CustomerID, OrderID
;


/* Execulte summary amount by customer */
@F7=EXECSQL()
SELECT CustomerID
, SUM(Amount) AS SumAmt
INTO tmp_sales_sum2
FROM tmp_sales_detail
GROUP BY CustomerID
;


/* Sales Detail Report */
@F8=REPORT(table=1 
	cell=D1,D2,G2,C6,D6,E6,F6,G6,H6,C9,D9,E9,F9,G9,H9,H11,H1 
	group=1,2,3,17 grouprange=1:13 
	group=4,5,6,7,8,9,16 grouprange=4:12 pagebreak = 1g)
SELECT d.Customer
,d.CustomerCity
,d.ContactName
,d.OrderID
,d.Salesperson
,d.OrderDate
,d.RequiredDate
,d.ShippedDate
,d.Shipper
,d.ProductID
,d.ProductName
,d.Quantity
,d.UnitPrice
,d.Discount
,d.Amount
,s1.SumAmt
,s2.SumAmt
FROM tmp_sales_detail d, tmp_sales_sum1 s1, tmp_sales_sum2 s2
WHERE d.CustomerID = s1.CustomerID
AND d.OrderID = s1.OrderID
AND d.CustomerID = s2.CustomerID
ORDER BY d.CustomerID, d.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)

 


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