Monthly Sales 6

PowerPoint Report Template - Monthly Sales 6

Function and SQL

/*********************************************************
Compare with Last Month by Categories
*********************************************************/
/* Calculate the total in the current month */
@F8_1=REPORT(slide=8 type=fix cell=B4)
SELECT 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')

/* Calculate the total in the last month */
@F8_2=REPORT(slide=8 type=fix cell=D4)
SELECT 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#

/* Drop table tmp_category_sales */
@F8_3=EXECSQL()
DROP TABLE tmp_category_sales

/* Create table tmp_category_sales */
@F8_4=EXECSQL()
CREATE TABLE tmp_category_sales (
CategoryID INTEGER,
Quantity INTEGER,
Amount MONEY
)

/* Get the sales amount by categories in the current month */
@F8_5=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 */
@F8_6=REPORT(slide=8 type=var cell=A3)
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 */
@F8_7=EXECSQL()
DELETE FROM tmp_category_sales

/* Get the sales amount by categories in the last month */
@F8_8=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 */
@F8_9=REPORT(slide=8 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 PTReportGen (Report Generator for Microsoft PowerPoint). The sample reports in Microsoft PowerPoint 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 PowerPoint format PPT (468KB)