Back to home

INTERNAL - Bepoz Help Guides

End-User | Reporting, Data Analysis & Security

Custom Reports

41 | PRODPROMOSUMMARY (Custom Report)

This article demonstrates an example of a Custom ProdPromo Summary Report. Disclaimer: This report may need modifications or alterations base on each system setup and configuration.

This article demonstrates an example of a Custom ProdPromo Summary Report.

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>2012-07-20T11:06:47</dtDateUpdated>

<iOperatorID>0</iOperatorID>

<sName>PROMO</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>Product Promotion Summary</sReportName><sSQL>SELECT ProdPromo.Name AS [Promotion Name], Product.Name AS [Product name],

CASE WHEN TransLines.Size = 1 THEN Product .SizeName_1 WHEN TransLines.Size = 2 THEN Product .SizeName_2 WHEN TransLines.Size = 3 THEN Product .SizeName_3

WHEN TransLines.Size = 4 THEN Product .SizeName_4 WHEN TransLines.Size = 5 THEN Product .SizeName_5 WHEN TransLines.Size = 6 THEN Product .SizeName_6

WHEN TransLines.Size = 7 THEN Product .SizeName_7 WHEN TransLines.Size = 8 THEN Product .SizeName_8 WHEN TransLines.Size = 9 THEN Product .SizeName_9

WHEN TransLines.Size = 10 THEN Product .SizeName_10 ELSE 'UNDEFINED' END AS 'Size', SUM(TransLines.UnitqtySold)as UnitsQtySold, SUM (TransLines.Gross) as Gross, SUM(TransLines.Nett) as Nett,

SUM (TransLines.DiscAmt_1 + TransLines.DiscAmt_2 + TransLines.DiscAmt_3) as DiscAmount, SUM(TransLines.CostTotal)as CostTotal

FROM TransLines INNER JOIN

Product ON TransLines.ProductId = Product.ProductID INNER JOIN

Venue ON TransLines.VenueID = Venue.VenueID INNER JOIN

ProdPromo ON TransLines.DiscPromoID = ProdPromo.ProdPromoID INNER JOIN

Transactions ON TransLines.TransactionID = Transactions.TransactionID INNER JOIN

Workstation ON Transactions.TillID = Workstation.WorkstationID

where transactions.shiftid &gt;= {ShiftFrom} and transactions.shiftid &lt;= {ShiftTo} and discPromoID &lt;&gt; 0

group by ProdPromo.Name, Product.Name, TransLines.Size,

Product.SizeName_1, Product.SizeName_2, Product.SizeName_3, Product.SizeName_4, Product.SizeName_5, Product.SizeName_6,Product.SizeName_7, Product.SizeName_8,Product.SizeName_9,Product.SizeName_10

</sSQL><oColumnDefs><dictionary><item><key>0</key><type>Xfer.cColInfo</type><value>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;cColInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;&lt;sKey&gt;0&lt;/sKey&gt;&lt;sText&gt;Promotion Name&lt;/sText&gt;&lt;Hidden&gt;No&lt;/Hidden&gt;&lt;Read_only&gt;Yes&lt;/Read_only&gt;&lt;Total&gt;No&lt;/Total&gt;&lt;FixedColumn&gt;No&lt;/FixedColumn&gt;&lt;ParentCol&gt;No&lt;/ParentCol&gt;&lt;eFormat&gt;Text&lt;/eFormat&gt;&lt;eList&gt;None&lt;/eList&gt;&lt;/cColInfo&gt;</value></item><item><key>1</key><type>Xfer.cColInfo</type><value>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;cColInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;&lt;sKey&gt;1&lt;/sKey&gt;&lt;sText&gt;Product Name&lt;/sText&gt;&lt;Hidden&gt;No&lt;/Hidden&gt;&lt;Read_only&gt;Yes&lt;/Read_only&gt;&lt;Total&gt;No&lt;/Total&gt;&lt;FixedColumn&gt;No&lt;/FixedColumn&gt;&lt;ParentCol&gt;No&lt;/ParentCol&gt;&lt;eFormat&gt;Text&lt;/eFormat&gt;&lt;eList&gt;None&lt;/eList&gt;&lt;/cColInfo&gt;</value></item><item><key>2</key><type>Xfer.cColInfo</type><value>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;cColInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;&lt;sKey&gt;2&lt;/sKey&gt;&lt;sText&gt;Size&lt;/sText&gt;&lt;Hidden&gt;No&lt;/Hidden&gt;&lt;Read_only&gt;Yes&lt;/Read_only&gt;&lt;Total&gt;No&lt;/Total&gt;&lt;FixedColumn&gt;No&lt;/FixedColumn&gt;&lt;ParentCol&gt;No&lt;/ParentCol&gt;&lt;eFormat&gt;Text&lt;/eFormat&gt;&lt;eList&gt;None&lt;/eList&gt;&lt;/cColInfo&gt;</value></item><item><key>3</key><type>Xfer.cColInfo</type><value>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;cColInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;&lt;sKey&gt;3&lt;/sKey&gt;&lt;sText&gt;Units Qty Sold&lt;/sText&gt;&lt;Hidden&gt;No&lt;/Hidden&gt;&lt;Read_only&gt;Yes&lt;/Read_only&gt;&lt;Total&gt;Yes&lt;/Total&gt;&lt;FixedColumn&gt;No&lt;/FixedColumn&gt;&lt;ParentCol&gt;No&lt;/ParentCol&gt;&lt;eFormat&gt;Dec2Int&lt;/eFormat&gt;&lt;eList&gt;None&lt;/eList&gt;&lt;/cColInfo&gt;</value></item><item><key>4</key><type>Xfer.cColInfo</type><value>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;cColInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;&lt;sKey&gt;4&lt;/sKey&gt;&lt;sText&gt;Gross&lt;/sText&gt;&lt;Hidden&gt;No&lt;/Hidden&gt;&lt;Read_only&gt;Yes&lt;/Read_only&gt;&lt;Total&gt;Yes&lt;/Total&gt;&lt;FixedColumn&gt;No&lt;/FixedColumn&gt;&lt;ParentCol&gt;No&lt;/ParentCol&gt;&lt;eFormat&gt;CurrInt&lt;/eFormat&gt;&lt;eList&gt;None&lt;/eList&gt;&lt;/cColInfo&gt;</value></item><item><key>5</key><type>Xfer.cColInfo</type><value>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;cColInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;&lt;sKey&gt;5&lt;/sKey&gt;&lt;sText&gt;Nett&lt;/sText&gt;&lt;Hidden&gt;No&lt;/Hidden&gt;&lt;Read_only&gt;Yes&lt;/Read_only&gt;&lt;Total&gt;Yes&lt;/Total&gt;&lt;FixedColumn&gt;No&lt;/FixedColumn&gt;&lt;ParentCol&gt;No&lt;/ParentCol&gt;&lt;eFormat&gt;CurrInt&lt;/eFormat&gt;&lt;eList&gt;None&lt;/eList&gt;&lt;/cColInfo&gt;</value></item><item><key>6</key><type>Xfer.cColInfo</type><value>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;cColInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;&lt;sKey&gt;6&lt;/sKey&gt;&lt;sText&gt;Discount Amount&lt;/sText&gt;&lt;Hidden&gt;No&lt;/Hidden&gt;&lt;Read_only&gt;Yes&lt;/Read_only&gt;&lt;Total&gt;Yes&lt;/Total&gt;&lt;FixedColumn&gt;No&lt;/FixedColumn&gt;&lt;ParentCol&gt;No&lt;/ParentCol&gt;&lt;eFormat&gt;CurrInt&lt;/eFormat&gt;&lt;eList&gt;None&lt;/eList&gt;&lt;/cColInfo&gt;</value></item><item><key>7</key><type>Xfer.cColInfo</type><value>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;cColInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;&lt;sKey&gt;7&lt;/sKey&gt;&lt;sText&gt;Cost Total&lt;/sText&gt;&lt;Hidden&gt;No&lt;/Hidden&gt;&lt;Read_only&gt;Yes&lt;/Read_only&gt;&lt;Total&gt;Yes&lt;/Total&gt;&lt;FixedColumn&gt;No&lt;/FixedColumn&gt;&lt;ParentCol&gt;No&lt;/ParentCol&gt;&lt;eFormat&gt;CurrDbl&lt;/eFormat&gt;&lt;eList&gt;None&lt;/eList&gt;&lt;/cColInfo&gt;</value></item></dictionary></oColumnDefs></cCustomReport></sViewXML>

</cViews>

Other Parameter Settings:

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

41 | PRODPROMOSUMMARY (Custom Report)

This article demonstrates an example of a Custom ProdPromo Summary Report. Disclaimer: This report may need modifications or alterations base on each system setup and configuration.

Disclaimer:

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>2012-07-20T11:06:47</dtDateUpdated>

<iOperatorID>0</iOperatorID>

<sName>PROMO</sName>

CASE WHEN TransLines.Size = 1 THEN Product .SizeName_1 WHEN TransLines.Size = 2 THEN Product .SizeName_2 WHEN TransLines.Size = 3 THEN Product .SizeName_3

WHEN TransLines.Size = 4 THEN Product .SizeName_4 WHEN TransLines.Size = 5 THEN Product .SizeName_5 WHEN TransLines.Size = 6 THEN Product .SizeName_6

WHEN TransLines.Size = 7 THEN Product .SizeName_7 WHEN TransLines.Size = 8 THEN Product .SizeName_8 WHEN TransLines.Size = 9 THEN Product .SizeName_9

WHEN TransLines.Size = 10 THEN Product .SizeName_10 ELSE 'UNDEFINED' END AS 'Size', SUM(TransLines.UnitqtySold)as UnitsQtySold, SUM (TransLines.Gross) as Gross, SUM(TransLines.Nett) as Nett,

SUM (TransLines.DiscAmt_1 + TransLines.DiscAmt_2 + TransLines.DiscAmt_3) as DiscAmount, SUM(TransLines.CostTotal)as CostTotal

FROM TransLines INNER JOIN

Product ON TransLines.ProductId = Product.ProductID INNER JOIN

Venue ON TransLines.VenueID = Venue.VenueID INNER JOIN

ProdPromo ON TransLines.DiscPromoID = ProdPromo.ProdPromoID INNER JOIN

Transactions ON TransLines.TransactionID = Transactions.TransactionID INNER JOIN

Workstation ON Transactions.TillID = Workstation.WorkstationID

where transactions.shiftid &amp;gt;= {ShiftFrom} and transactions.shiftid &amp;lt;= {ShiftTo} and discPromoID &amp;lt;&amp;gt; 0

group by ProdPromo.Name, Product.Name, TransLines.Size,

Product.SizeName_1, Product.SizeName_2, Product.SizeName_3, Product.SizeName_4, Product.SizeName_5, Product.SizeName_6,Product.SizeName_7, Product.SizeName_8,Product.SizeName_9,Product.SizeName_10

</cViews>

Other Parameter Settings:

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

where transactions.shiftid >= {ShiftFrom} and transactions.shiftid <= {ShiftTo} and discPromoID <> 0