Custom Reports
      Back to home
      1. INTERNAL - Bepoz Help Guides
      2. End-User | Reporting, Data Analysis & Security
      3. Custom Reports

      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>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;cCustomReport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;&lt;sReportName&gt;Sales Report&lt;/sReportName&gt;&lt;sSQL&gt;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 &amp;lt;&amp;gt; 0 and Transactions.ShiftID &amp;gt;=  {ShiftFrom} and Transactions.ShiftID &amp;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&lt;/sSQL&gt;&lt;oColumnDefs&gt;&lt;dictionary /&gt;&lt;/oColumnDefs&gt;&lt;/cCustomReport&gt;</sViewXML>

      </cViews>

       

       

       

      Other Parameter Settings:

      This file needs to be saved as a *.REP file using a document editor