Back to home

INTERNAL - Bepoz Help Guides

End-User | Reporting, Data Analysis & Security

Custom Reports

25 | PointsbyStore (Custom Report)

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

This article demonstrates an example of a Custom Points by Store 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>2011-07-22T16:48:34</dtDateUpdated>

<iOperatorID>0</iOperatorID>

<sName>1</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>Points Given/ Redeemed</sReportName><sSQL>Select D.Name,Sum(A.POSPointsEarned) AS Earned, SUM(A.POspointsRedeemed) as Redeemed,

SUM(A.POSPointsEarned)/100 as DollarEarned, SUM(A.POspointsRedeemed)/100 as DollarRedeemed

Into #PosPoints From AccActivity A

Join Transactions B on B.TransactionID=A.TransactionID

Join Workstation C on C.WorkstationID=B.TillID

Join Store D on D.StoreID=C.StoreID

Where DateTimeActivity &gt;= {DateFrom} and DateTimeActivity &lt; {DateTo}

Group By D.Name

Insert into #POSPoints(Name, Earned, Redeemed, DollarEarned, DollarRedeemed)

Select 'Clubnet', SUM(POSPointsEarned), SUM(POspointsRedeemed ), SUM(POSPointsEarned)/100,SUM(POspointsRedeemed)/100

From AccActivity

Where DateTimeActivity &gt;= {DateFrom} and DateTimeActivity &lt; {DateTo}

and TransactionID = 0

Select * From #POSPoints

Drop Table #POSPoints</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;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>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;POS Points Earned&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>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;POS Points Redeemd&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>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;$$$ Points Earned&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: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;$$$ Points Redeemed&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>

</cViews>

Other Parameter Settings:

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

25 | PointsbyStore (Custom Report)

This article demonstrates an example of a Custom Points by Store 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>2011-07-22T16:48:34</dtDateUpdated>

<iOperatorID>0</iOperatorID>

<sName>1</sName>

SUM(A.POSPointsEarned)/100 as DollarEarned, SUM(A.POspointsRedeemed)/100 as DollarRedeemed

Into #PosPoints From AccActivity A

Join Transactions B on B.TransactionID=A.TransactionID

Join Workstation C on C.WorkstationID=B.TillID

Join Store D on D.StoreID=C.StoreID

Where DateTimeActivity &amp;gt;= {DateFrom} and DateTimeActivity &amp;lt; {DateTo}

Group By D.Name

Insert into #POSPoints(Name, Earned, Redeemed, DollarEarned, DollarRedeemed)

Select 'Clubnet', SUM(POSPointsEarned), SUM(POspointsRedeemed ), SUM(POSPointsEarned)/100,SUM(POspointsRedeemed)/100

From AccActivity

Where DateTimeActivity &amp;gt;= {DateFrom} and DateTimeActivity &amp;lt; {DateTo}

and TransactionID = 0

Select * From #POSPoints

</cViews>

Other Parameter Settings:

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

Where DateTimeActivity >= {DateFrom} and DateTimeActivity < {DateTo}

Where DateTimeActivity >= {DateFrom} and DateTimeActivity < {DateTo}