26 | PointsSummary (Custom Report)

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

<iListType>25</iListType>

<dtDateUpdated>2013-04-22T20:18:55</dtDateUpdated>

<iOperatorID>0</iOperatorID>

<sName>Points</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 Summary</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(Points100), 0, Cast(Sum(points100) as float)/100 ,0

From Clubnet

Where Date &gt;= {datefrom} and Date &lt; {dateto}

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

Select 'Door Terminal', SUM(POSPointsEarned), SUM(POspointsRedeemed ), SUM(POSPointsEarned)/100,SUM(POspointsRedeemed)/100

From AccActivity

Where DateTimeActivity &gt;= {datefrom} and DateTimeActivity &lt; {dateto}

and TransactionID = 0

Update #pospoints

Set earned = (earned -(Select SUM(points*100) from Clubnet where date &gt;= {datefrom} and Date &lt; {dateto}))

From #pospoints

where name = 'Door Terminal'

Update #pospoints

Set dollarearned = (Dollarearned-(Select SUM(points) from Clubnet where date &gt;= {datefrom} and Date &lt; {dateto}))

From #pospoints

where name = 'Door Terminal'

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 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;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>

<UpdateAll>false</UpdateAll>

</cViews>

Other Parameter Settings:

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

26 | PointsSummary (Custom Report)

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

<iListType>25</iListType>

<dtDateUpdated>2013-04-22T20:18:55</dtDateUpdated>

<iOperatorID>0</iOperatorID>

<sName>Points</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(Points*100), 0, Cast(Sum(points*100) as float)/100 ,0

From Clubnet

Where Date &amp;gt;= {datefrom} and Date &amp;lt; {dateto}

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

Select 'Door Terminal', 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

Update #pospoints

Set earned = (earned -(Select SUM(points*100) from Clubnet where date &amp;gt;= {datefrom} and Date &amp;lt; {dateto}))

From #pospoints

where name = 'Door Terminal'

Update #pospoints

Set dollarearned = (Dollarearned-(Select SUM(points) from Clubnet where date &amp;gt;= {datefrom} and Date &amp;lt; {dateto}))

From #pospoints

where name = 'Door Terminal'

Select * From #POSPoints

Drop Table #POSPoints

<UpdateAll>false</UpdateAll>

</cViews>

Other Parameter Settings:

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

Where DateTimeActivity >= {datefrom} and DateTimeActivity < {dateto}

Select 'Clubnet', SUM(Points100), 0, Cast(Sum(points100) as float)/100 ,0

Where Date >= {datefrom} and Date < {dateto}

Where DateTimeActivity >= {datefrom} and DateTimeActivity < {dateto}

Set earned = (earned -(Select SUM(points*100) from Clubnet where date >= {datefrom} and Date < {dateto}))

Set dollarearned = (Dollarearned-(Select SUM(points) from Clubnet where date >= {datefrom} and Date < {dateto}))