Word Report Template - Monthly Sales 2
Function and SQL
/*********************************************************
Sales Summary
*********************************************************/
/* Calculate the sales amount */
@F1_1=REPORT(table=Report1 type=fix cell=D2,E2)
SELECT Year(o.OrderDate)*100 + MONTH(o.OrderDate)
, Sum(d.UnitPrice * d.Quantity * (1-d.Discount))
FROM Orders o
,OrderDetails d
WHERE o.OrderID = d.OrderID
AND o.OrderDate >= DateAdd('m',-5,'$ReportMonth-01')
AND o.OrderDate < DateAdd('m',1,'$ReportMonth-01')
GROUP BY Year(o.OrderDate)*100 + MONTH(o.OrderDate)
ORDER BY 1 DESC
/* Calculate the number of customers and the number of orders */
@F1_2=REPORT(table=Report1 type=fix cell=B3,B5)
SELECT COUNT(CustomerID)
, COUNT(OrderID)
FROM Orders
WHERE YEAR(OrderDate) = YEAR('$ReportMonth-01')
AND MONTH(OrderDate) = MONTH('$ReportMonth-01')
/* Calculate the quantity of products */
@F1_3=REPORT(table=Report1 type=fix cell=B7)
SELECT SUM(d.Quantity)
FROM Orders o
,OrderDetails d
WHERE o.OrderID = d.OrderID
AND YEAR(o.OrderDate) = YEAR('$ReportMonth-01')
AND MONTH(o.OrderDate) = MONTH('$ReportMonth-01')
/* For chart */
@F1_4=CHART(chart=1 cell=A0)
SELECT Year(o.OrderDate)*100 + MONTH(o.OrderDate)
, Sum(d.UnitPrice * d.Quantity * (1-d.Discount))
FROM Orders o
,OrderDetails d
WHERE o.OrderID = d.OrderID
AND o.OrderDate >= DateAdd('m',-5,'$ReportMonth-01')
AND o.OrderDate < DateAdd('m',1,'$ReportMonth-01')
GROUP BY Year(o.OrderDate)*100 + MONTH(o.OrderDate)
ORDER BY 1 DESC
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)