Back to home

INTERNAL - Bepoz Help Guides

End-User | Reporting, Data Analysis & Security

Custom Reports

69 | VoucherUsage (Custom Report)

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

This article demonstrates an example of a Custom Voucher Usage 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">

<iListType>25</iListType>

<dtDateUpdated>2016-02-02T16:34:05</dtDateUpdated>

<iOperatorID>0</iOperatorID>

<sName>VchUse</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>Voucher Usage</sReportName><sSQL>select

case when a.AccountID = t.AccountID

then a.AccNumber

else 'N/A'

end,

case when a.AccountID = t.AccountID

then a.firstname + ' ' + a.lastname

else 'Non Member Account'

end,

s.name,

w.name,

vs.name,

v.VoucherID,

t.datetimetrans,

t.TransactionID,

case v.VoucherType

when 1 then convert(varchar,v.AmountIssued / 100) + ' ' + 'Ticket(s)'

when 2 then convert(varchar,v.AmountIssued / 100) + ' ' + 'Ticket(s)'

when 3 then convert(varchar,v.AmountIssued / 100) + ' ' + 'Ticket(s)'

when 6 then convert(varchar,v.AmountIssued / 100) + '%' + ' ' + '+' + ' ' + 'Max Discount:' + ' ' + convert(varchar,cast(v.MaximumDiscount as money) / 100)

else convert(varchar,cast(v.amountissued as money)/100)

end,

case v.VoucherType

when 1 then convert(varchar,cast(tp.adjustment as money) / 100)

when 2 then convert(varchar,tp.adjustment / 100) + ' ' + 'Ticket(s)'

when 3 then convert(varchar,tp.adjustment / 100) + ' ' + 'Ticket(s)'

when 6 then convert(varchar,cast(tp.amount as money) / 100)

else convert(varchar,cast(tp.adjustment as money) / 100)

end,

case v.VoucherType

when 1 then convert(varchar,v.AmountLeft / 100) + ' ' + 'Ticket(s)'

when 2 then convert(varchar,v.AmountLeft / 100) + ' ' + 'Ticket(s)'

when 3 then convert(varchar,v.AmountLeft / 100) + ' ' + 'Ticket(s)'

else convert(varchar,cast(v.AmountLeft as money)/100)

end,

v.UsedCount

from transactions t join TransPayments tp on t.TransactionID = tp.TransactionID

left join Account a on a.accountid = t.AccountID

join voucher v on v.VoucherID = tp.RefID

join VoucherSetup vs on vs.VoucherSetupID = v.VoucherSetupID

join Workstation w on w.WorkstationID = t.TillID

join store s on s.storeid = w.StoreID

where tp.PaymentType in (22,65537) and tp.Amount &gt;= 0 and (t.shiftid &gt;= {SHIFTFROM} and t.shiftid &lt;= {SHIFTTO})</sSQL><iFlags>15</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;Account Number&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;Account 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: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;Redeem Store&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: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;Redeem Till&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>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;Voucher 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;VoucherID&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>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;Redeem Date&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;DateOnly&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;TransactionID&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>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;Issued Voucher Value \ Uses&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;Text&lt;/eFormat&gt;&lt;eList&gt;None&lt;/eList&gt;&lt;/cColInfo&gt;</value></item><item><key>9</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;9&lt;/sKey&gt;&lt;sText&gt;Value \ Vouchers Used&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;Text&lt;/eFormat&gt;&lt;eList&gt;None&lt;/eList&gt;&lt;/cColInfo&gt;</value></item><item><key>10</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;10&lt;/sKey&gt;&lt;sText&gt;Value \ Vouchers Remaining&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;Text&lt;/eFormat&gt;&lt;eList&gt;None&lt;/eList&gt;&lt;/cColInfo&gt;</value></item><item><key>11</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;11&lt;/sKey&gt;&lt;sText&gt;Used Count&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></dictionary></oColumnDefs></cCustomReport></sViewXML>

69 | VoucherUsage (Custom Report)

Learn how to demonstrates an example of a Custom Voucher Usage 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">

<iListType>25</iListType>

<dtDateUpdated>2016-02-02T16:34:05</dtDateUpdated>

<iOperatorID>0</iOperatorID>

<sName>VchUse</sName>

<sViewXML>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;cCustomReport xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;&lt;sReportName&gt;Voucher Usage&lt;/sReportName&gt;&lt;sSQL&gt;select

case when a.AccountID = t.AccountID

then a.AccNumber

else 'N/A'

end,

case when a.AccountID = t.AccountID

then a.firstname + ' ' + a.lastname

else 'Non Member Account'

end,

s.name,

w.name,

vs.name,

v.VoucherID,

t.datetimetrans,

t.TransactionID,

case v.VoucherType

when 1 then convert(varchar,v.AmountIssued / 100) + ' ' + 'Ticket(s)'

when 2 then convert(varchar,v.AmountIssued / 100) + ' ' + 'Ticket(s)'

when 3 then convert(varchar,v.AmountIssued / 100) + ' ' + 'Ticket(s)'

when 6 then convert(varchar,v.AmountIssued / 100) + '%' + ' ' + '+' + ' ' + 'Max Discount:' + ' ' + convert(varchar,cast(v.MaximumDiscount as money) / 100)

else convert(varchar,cast(v.amountissued as money)/100)

end,

case v.VoucherType

when 1 then convert(varchar,cast(tp.adjustment as money) / 100)

when 2 then convert(varchar,tp.adjustment / 100) + ' ' + 'Ticket(s)'

when 3 then convert(varchar,tp.adjustment / 100) + ' ' + 'Ticket(s)'

when 6 then convert(varchar,cast(tp.amount as money) / 100)

else convert(varchar,cast(tp.adjustment as money) / 100)

end,

case v.VoucherType

when 1 then convert(varchar,v.AmountLeft / 100) + ' ' + 'Ticket(s)'

when 2 then convert(varchar,v.AmountLeft / 100) + ' ' + 'Ticket(s)'

when 3 then convert(varchar,v.AmountLeft / 100) + ' ' + 'Ticket(s)'

else convert(varchar,cast(v.AmountLeft as money)/100)

end,

v.UsedCount

from transactions t join TransPayments tp on t.TransactionID = tp.TransactionID

left join Account a on a.accountid = t.AccountID

join voucher v on v.VoucherID = tp.RefID

join VoucherSetup vs on vs.VoucherSetupID = v.VoucherSetupID

join Workstation w on w.WorkstationID = t.TillID

join store s on s.storeid = w.StoreID

<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

<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>Voucher Usage</sReportName><sSQL>select