PowerPoint Report Template - Monthly Sales 5

See Microsoft ® PowerPoint ® Report

Function and SQL

/* Calculate the sales amount */
@F5_2=EXECSQL()
SELECT Sum(d.UnitPrice * d.Quantity * (1-d.Discount)) AS amount
INTO tmp_amount
FROM Orders o
	,OrderDetails d
WHERE o.OrderID = d.OrderID
AND YEAR(o.OrderDate) = YEAR('$ReportMonth-01')
AND MONTH(o.OrderDate) = MONTH('$ReportMonth-01')

/*********************************************************
Top N Customers for Sales
*********************************************************/
@F6_1=REPORT(slide=6 type=fix cell=B2)
SELECT TOP 5 c.CompanyName
	, SUM(d.Quantity)
	, Sum(d.UnitPrice * d.Quantity * (1-d.Discount)) AS SalesAmount
	, SalesAmount / (SELECT amount FROM tmp_amount)
FROM Orders o
	,OrderDetails d
	,Products p
	,Customers c
WHERE o.OrderID = d.OrderID
AND d.ProductID = p.ProductID
AND o.CustomerID = c.CustomerID
AND YEAR(o.OrderDate) = YEAR('$ReportMonth-01')
AND MONTH(o.OrderDate) = MONTH('$ReportMonth-01')
GROUP BY c.CompanyName
ORDER BY 3 DESC

/* For chart */
@F6_2=CHART(slide=6 cell=A0)
SELECT TOP 5 c.CompanyName
	, Sum(d.UnitPrice * d.Quantity * (1-d.Discount))
FROM Orders o
	,OrderDetails d
	,Products p
	,Customers c
WHERE o.OrderID = d.OrderID
AND d.ProductID = p.ProductID
AND o.CustomerID = c.CustomerID
AND YEAR(o.OrderDate) = YEAR('$ReportMonth-01')
AND MONTH(o.OrderDate) = MONTH('$ReportMonth-01')
GROUP BY c.CompanyName
ORDER BY 2 DESC

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)