Word Report Template - Monthly Sales 6
Function and SQL
/* Create table tmp_category_sales */
@F9_2=EXECSQL()
CREATE TABLE tmp_category_sales (
CategoryID INTEGER,
Quantity INTEGER,
Amount MONEY
);
/* Get the sales amount by categories in the current month */
@F9_3=EXECSQL()
INSERT INTO tmp_category_sales (CategoryID, Quantity, Amount)
SELECT p.CategoryID
, SUM(d.Quantity)
, Sum(d.UnitPrice * d.Quantity * (1-d.Discount))
FROM Orders o
,OrderDetails d
,Products p
WHERE o.OrderID = d.OrderID
AND d.ProductID = p.ProductID
AND YEAR(o.OrderDate) = YEAR('$ReportMonth-01')
AND MONTH(o.OrderDate) = MONTH('$ReportMonth-01')
GROUP BY p.CategoryID
;
/* Show the sales amount by categories in the current month */
@F9_4=REPORT(table=9 type=var cell=A3 copyrange=3:3)
SELECT c.CategoryName
, IIF(IsNull(t.Quantity),0,t.Quantity)
, IIF(IsNull(t.Amount),0,t.Amount)
FROM Categories c LEFT JOIN tmp_category_sales t
ON c.CategoryID = t.CategoryID
ORDER BY c.CategoryName
;
/* Delete from table tmp_category_sales */
@F9_5=EXECSQL()
DELETE FROM tmp_category_sales;
/* Get the sales amount by categories in the last month */
@F9_6=EXECSQL()
INSERT INTO tmp_category_sales (CategoryID, Quantity, Amount)
SELECT p.CategoryID
, SUM(d.Quantity)
, Sum(d.UnitPrice * d.Quantity * (1-d.Discount))
FROM Orders o
,OrderDetails d
,Products p
WHERE o.OrderID = d.OrderID
AND d.ProductID = p.ProductID
AND o.OrderDate >= DateAdd('m',-1,#$ReportMonth-01#)
AND o.OrderDate < #$ReportMonth-01#
GROUP BY p.CategoryID
;
/* Show the sales amount by categories in the last month */
@F9_7=REPORT(table=9 type=fix cell=D3)
SELECT IIF(IsNull(t.Quantity),0,t.Quantity)
, IIF(IsNull(t.Amount),0,t.Amount)
FROM Categories c LEFT JOIN tmp_category_sales t
ON c.CategoryID = t.CategoryID
ORDER BY c.CategoryName
;
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 (691KB)