12 | DOMSALES (Custom Report)
This article demonstrates an example of a Custom DOMSALES Report. Disclaimer: This report may need modifications or alterations base on each system setup and configuration.
Disclaimer:
This report may need modifications or alterations base on each system setup and configuration.
Report Query:
<?xml version="1.0" encoding="utf-8"?>
<cViews xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<iListType>25</iListType>
<dtDateUpdated>2013-03-14T17:45:55</dtDateUpdated>
<iOperatorID>0</iOperatorID>
<sName>DOMSALES</sName>
<sViewXML><?xml version="1.0" encoding="utf-16"?><cCustomReport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><sReportName>Sales Report</sReportName><sSQL>SELECT Store.Name AS StoreName,
CASE
WHEN Store.StoreGroup = 0 THEN Global.StoreGroup0
WHEN Store.StoreGroup = 1 THEN Global.StoreGroup1
WHEN Store.StoreGroup = 2 THEN Global.StoreGroup2
WHEN Store.StoreGroup = 3 THEN Global.StoreGroup3
WHEN Store.StoreGroup = 4 THEN Global.StoreGroup4
WHEN Store.StoreGroup = 5 THEN Global.StoreGroup5
WHEN Store.StoreGroup = 6 THEN Global.StoreGroup6
WHEN Store.StoreGroup = 7 THEN Global.StoreGroup7
ELSE NULL END AS 'StoreGroup',
CONVERT(varchar,Transactions.DateTimeTrans,103) AS 'Date',
convert(decimal(10,2),SUM(TransLines.Gross)/100) AS 'GrossSale',
convert(decimal(10,2),SUM(TransLines.DiscAmt_1 + TransLines.DiscAmt_2 + TransLines.DiscAmt_3)/100) AS 'ItemDisc',
convert(decimal(10,2),SUM(TransLines.Nett)/100) AS 'Nett',
sum(CASE
WHEN Translines.TaxType = 1 THEN
convert(decimal(10,2),(TransLines.Nett)/100)/11
ELSE convert(decimal(10,2),(TransLines.Nett)/100) END) AS 'GST',
sum(CASE
WHEN Translines.TaxType = 1 THEN
convert(decimal(10,2),(TransLines.DiscAmt_1 + TransLines.DiscAmt_2 + TransLines.DiscAmt_3)/100)/11
ELSE convert(decimal(10,2),(TransLines.DiscAmt_1 + TransLines.DiscAmt_2 + TransLines.DiscAmt_3)/100) END) AS 'ItemDisc GST',
sum(CASE
WHEN Translines.TaxType = 1 THEN
convert(decimal(10,2),((Translines.Nett)/100)- convert(decimal(10,2),((TransLines.Nett)/100))/11)
ELSE convert(decimal(10,2),(TransLines.Nett)/100) END) AS 'NettEx',
sum(CASE
WHEN Translines.TaxType = 1 THEN
convert(decimal(10,2),((TransLines.DiscAmt_1 + TransLines.DiscAmt_2 + TransLines.DiscAmt_3)/100)- convert(decimal(10,2),((TransLines.DiscAmt_1 + TransLines.DiscAmt_2 + TransLines.DiscAmt_3)/100))/11)
ELSE convert(decimal(10,2),(TransLines.DiscAmt_1 + TransLines.DiscAmt_2 + TransLines.DiscAmt_3)/100) END) AS 'ItemDisc Ex'
FROM Transactions INNER JOIN
TransLines ON Transactions.TransactionID = TransLines.TransactionID INNER JOIN
Workstation INNER JOIN
Store ON Workstation.StoreID = Store.StoreID ON Transactions.TillID = Workstation.WorkstationID CROSS JOIN
Global
WHERE transactions.transtype &lt;&gt; 0 and Transactions.ShiftID &gt;= {ShiftFrom} and Transactions.ShiftID &lt;= {ShiftTo}
GROUP BY Transactions.ShiftID, Store.Name, Store.StoreGroup, CONVERT(varchar,Transactions.DateTimeTrans,103), Transactions.ShiftID, Global.StoreGroup0, Global.StoreGroup1, Global.StoreGroup2, Global.StoreGroup3, Global.StoreGroup4, Global.StoreGroup5, Global.StoreGroup6, Global.StoreGroup7</sSQL><oColumnDefs><dictionary /></oColumnDefs></cCustomReport></sViewXML>
</cViews>