Back to home

INTERNAL - Bepoz Help Guides

End-User | Reporting, Data Analysis & Security

Custom Reports

44 | PROMO REPORT (Custom Report)

Learn how to demonstrates an example of a Custom Promo Report. Disclaimer: This report may need modifications or alterations base on each system setup and configuration.

This article demonstrates an example of a Custom Promo 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:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<iViewID>679</iViewID>

<iListType>25</iListType>

<dtDateUpdated>2016-06-20T10:05:59</dtDateUpdated>

<iOperatorID>0</iOperatorID>

<sName>PROMO REPORT</sName>

<sViewXML><?xml version="1.0" encoding="utf-16"?><cCustomReport xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><sReportName>Promotion Report</sReportName><sSQL>Select Till, Sum(cnt), Gross, Sum(IsNull([Price Promo],0)+ Isnull([MultiBuy Promo],0) + IsNull([Voucher Promo],0)) as DiscTotal,

Promoname, DiscountNum,

isnull([Price Promo],0), isNUll([Multibuy Promo],0), isNull([Voucher Promo],0)

From(

Select D.Name as till, Sum(A.DiscAmt_1) as disc, B.Name as promoname, Sum(A.Gross) as gross,

Case When B.PromoType = 0 then 'Price Promo'

When B.PromoType = 1 then 'Multibuy Promo'

When B.PromoType = 2 then 'Voucher Promo'

Else '' end as promotype, Count(Distinct A.Transactionid) as cnt, B.DiscountNum

From Translines A

Join ProdPromo B on b.ProdPromoID = A.DiscPromoID and B.DiscountNum = A.DiscNum_1

Join Transactions C on C.Transactionid = A.Transactionid

Join Workstation D on D.Workstationid = C.TillID

Join Store E on E.StoreID = D.StoreID

Where C.ShiftId &gt;= {ShiftFrom} and C.ShiftID &lt;= {Shiftto}

and E.VenueID like'{VenueId}'

and E.StoreID like '{Storeid}'

Group by D.Name, B.Name, B.PromoType, B.DiscountNum

union all

Select D.Name as till, Sum(A.DiscAmt_2) as disc, B.Name as promoname, Sum(A.Gross) as gross,

Case When B.PromoType = 0 then 'Price Promo'

When B.PromoType = 1 then 'Multibuy Promo'

When B.PromoType = 2 then 'Voucher Promo'

Else '' end as promotype, Count(Distinct A.Transactionid) as cnt, B.DiscountNum

From Translines A

Join ProdPromo B on b.ProdPromoID = A.DiscPromoID and B.DiscountNum = A.DiscNum_2

Join Transactions C on C.Transactionid = A.Transactionid

Join Workstation D on D.Workstationid = C.TillID

Join Store E on E.StoreID = D.StoreID

Where C.ShiftId &gt;= {ShiftFrom} and C.ShiftID &lt;= {Shiftto}

and E.VenueID like'{VenueId}'

and E.StoreID like '{Storeid}'

Group by D.Name, B.Name, B.PromoType, B.DiscountNum

union all

Select D.Name as till, Sum(A.DiscAmt_3) as disc, B.Name as promoname, Sum(A.Gross) as gross,

Case When B.PromoType = 0 then 'Price Promo'

When B.PromoType = 1 then 'Multibuy Promo'

When B.PromoType = 2 then 'Voucher Promo'

Else '' end as promotype, Count(Distinct A.Transactionid) as cnt, B.DiscountNum

From Translines A

Join ProdPromo B on b.ProdPromoID = A.DiscPromoID and B.DiscountNum = A.DiscNum_3

Join Transactions C on C.Transactionid = A.Transactionid

Join Workstation D on D.Workstationid = C.TillID

Join Store E on E.StoreID = D.StoreID

Where C.ShiftId &gt;= {ShiftFrom} and C.ShiftID &lt;= {Shiftto}

and E.VenueID like'{VenueId}'

and E.StoreID like '{Storeid}'

Group by D.Name, B.Name, B.PromoType, B.DiscountNum) sel

pivot

(

Sum(disc)

For promotype in ([Price Promo], [Multibuy Promo], [Voucher Promo])

) as piv

Group By Till, PromoName, Gross, [Price Promo], [Multibuy Promo], [Voucher Promo], Discountnum</sSQL><iFlags>11</iFlags><bShowShifts>0</bShowShifts><iShiftFrom>0</iShiftFrom><iShiftTo>0</iShiftTo><bShowDates>0</bShowDates><dtDateFrom>0001-01-01T00:00:00</dtDateFrom><dtDateTo>0001-01-01T00:00:00</dtDateTo><bShowVenue>0</bShowVenue><isNegVenueID>0</isNegVenueID><bShowStore>0</bShowStore><iNegStoreID>0</iNegStoreID><bShowProducts>0</bShowProducts><iProductID>0</iProductID><bShowOperators>0</bShowOperators><iOperatorID>0</iOperatorID><bShowAccounts>0</bShowAccounts><iAccountID>0</iAccountID><bShowWorkStations>0</bShowWorkStations><iWorkstationID>0</iWorkstationID><bShowSuppliers>0</bShowSuppliers><iSupplierID>0</iSupplierID><bShowVoucherSetups>0</bShowVoucherSetups><iVoucherSetupID>0</iVoucherSetupID><bShowTableGroups>0</bShowTableGroups><iTableGroupID>0</iTableGroupID><bchkSubItems>0</bchkSubItems><bchkInactive>0</bchkInactive><bchkExtra1>0</bchkExtra1><oColumnDefs><dictionary><item><key>0</key><type>Xfer.cColInfo</type><value>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;cColInfo xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;&lt;sKey&gt;0&lt;/sKey&gt;&lt;sText&gt;Till 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:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;&lt;sKey&gt;1&lt;/sKey&gt;&lt;sText&gt;Qty of Trans&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;Int&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:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;&lt;sKey&gt;2&lt;/sKey&gt;&lt;sText&gt;Gross Sales&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>3</key><type>Xfer.cColInfo</type><value>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;cColInfo xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;&lt;sKey&gt;3&lt;/sKey&gt;&lt;sText&gt;Total Discounts&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>4</key><type>Xfer.cColInfo</type><value>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;cColInfo xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;&lt;sKey&gt;4&lt;/sKey&gt;&lt;sText&gt;Promo 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>5</key><type>Xfer.cColInfo</type><value>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;cColInfo xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;&lt;sKey&gt;5&lt;/sKey&gt;&lt;sText&gt;Disc Totaliser&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;DiscItemNum&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:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;&lt;sKey&gt;6&lt;/sKey&gt;&lt;sText&gt;Price Promo&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:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;&lt;sKey&gt;7&lt;/sKey&gt;&lt;sText&gt;Multibuy Promo&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>8</key><type>Xfer.cColInfo</type><value>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;cColInfo xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;&lt;sKey&gt;8&lt;/sKey&gt;&lt;sText&gt;Voucher Promo&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></dictionary></oColumnDefs></cCustomReport></sViewXML>

44 | PROMO REPORT (Custom Report)

Learn how to demonstrates an example of a Custom Promo 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:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<iViewID>679</iViewID>

<iListType>25</iListType>

<dtDateUpdated>2016-06-20T10:05:59</dtDateUpdated>

<iOperatorID>0</iOperatorID>

<sName>PROMO REPORT</sName>

Promoname, DiscountNum,

isnull([Price Promo],0), isNUll([Multibuy Promo],0), isNull([Voucher Promo],0)

From(

Select D.Name as till, Sum(A.DiscAmt_1) as disc, B.Name as promoname, Sum(A.Gross) as gross,

Case When B.PromoType = 0 then 'Price Promo'

When B.PromoType = 1 then 'Multibuy Promo'

When B.PromoType = 2 then 'Voucher Promo'

Else '' end as promotype, Count(Distinct A.Transactionid) as cnt, B.DiscountNum

From Translines A

Join ProdPromo B on b.ProdPromoID = A.DiscPromoID and B.DiscountNum = A.DiscNum_1

Join Transactions C on C.Transactionid = A.Transactionid

Join Workstation D on D.Workstationid = C.TillID

Join Store E on E.StoreID = D.StoreID

Where C.ShiftId &amp;gt;= {ShiftFrom} and C.ShiftID &amp;lt;= {Shiftto}

and E.VenueID like'{VenueId}'

and E.StoreID like '{Storeid}'

Group by D.Name, B.Name, B.PromoType, B.DiscountNum

union all

Select D.Name as till, Sum(A.DiscAmt_2) as disc, B.Name as promoname, Sum(A.Gross) as gross,

Case When B.PromoType = 0 then 'Price Promo'

When B.PromoType = 1 then 'Multibuy Promo'

When B.PromoType = 2 then 'Voucher Promo'

Else '' end as promotype, Count(Distinct A.Transactionid) as cnt, B.DiscountNum

From Translines A

Join ProdPromo B on b.ProdPromoID = A.DiscPromoID and B.DiscountNum = A.DiscNum_2

Join Transactions C on C.Transactionid = A.Transactionid

Join Workstation D on D.Workstationid = C.TillID

Join Store E on E.StoreID = D.StoreID

Where C.ShiftId &amp;gt;= {ShiftFrom} and C.ShiftID &amp;lt;= {Shiftto}

and E.VenueID like'{VenueId}'

and E.StoreID like '{Storeid}'

Group by D.Name, B.Name, B.PromoType, B.DiscountNum

union all

Select D.Name as till, Sum(A.DiscAmt_3) as disc, B.Name as promoname, Sum(A.Gross) as gross,

Case When B.PromoType = 0 then 'Price Promo'

When B.PromoType = 1 then 'Multibuy Promo'

When B.PromoType = 2 then 'Voucher Promo'

Else '' end as promotype, Count(Distinct A.Transactionid) as cnt, B.DiscountNum

From Translines A

Join ProdPromo B on b.ProdPromoID = A.DiscPromoID and B.DiscountNum = A.DiscNum_3

Join Transactions C on C.Transactionid = A.Transactionid

Join Workstation D on D.Workstationid = C.TillID

Join Store E on E.StoreID = D.StoreID

Where C.ShiftId &amp;gt;= {ShiftFrom} and C.ShiftID &amp;lt;= {Shiftto}

and E.VenueID like'{VenueId}'

and E.StoreID like '{Storeid}'

Group by D.Name, B.Name, B.PromoType, B.DiscountNum) sel

pivot

(

Sum(disc)

For promotype in ([Price Promo], [Multibuy Promo], [Voucher Promo])

) as piv

<UpdateAll>false</UpdateAll>

<ID_Set>0</ID_Set>

</cViews>

Other Parameter Settings:

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

Where C.ShiftId >= {ShiftFrom} and C.ShiftID <= {Shiftto}

Where C.ShiftId >= {ShiftFrom} and C.ShiftID <= {Shiftto}

Where C.ShiftId >= {ShiftFrom} and C.ShiftID <= {Shiftto}