PowerPoint Report Template - Sales Detail

See Microsoft ® PowerPoint ® Report

Function and SQL

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

/* BeginDate and EndDate */
@F1=DOCVARIABLE(NAME=BeginDate, EndDate)
SELECT DateSerial(Year('$ReportMonth-01')
,MONTH('$ReportMonth-01'),1)
,DateAdd('d',-1,DateAdd('m',1,DateSerial(Year('$ReportMonth-01')
,MONTH('$ReportMonth-01'),1)))
;

/* 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(slide=2 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:12 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 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)