This article demonstrates an example of a Custom CB1-MemberPoints Sum Report. Disclaimer: This report may need modifications or alterations base on each system setup and configuration.
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>2015-02-24T13:33:08</dtDateUpdated>
<iOperatorID>0</iOperatorID>
<sName>CB1</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>Members Points Summary</sReportName><sSQL>Select A.Accountid, D.FirstName, D.LastName, 0 as OpeningBal,Sum(A.POSPointsEarned) AS Earned, SUM(A.POspointsRedeemed) as Redeemed,
SUM(A.POSPointsEarned)/2500 as DollarEarned, SUM(A.POspointsRedeemed)/2500 as DollarRedeemed, 0 as Closingbal, Sum(A.PointsExpired) As PointsExpired
into #POSPoints From AccActivity A
Join Transactions B on B.TransactionID=A.TransactionID
Join Workstation C on C.WorkstationID=B.TillID
Join Account D on D.Accountid=A.Accountid
Where DateTimeActivity &gt;= {DateFrom} and DateTimeActivity &lt; {Dateto}
and (A.POSPointsEarned)+(A.POspointsRedeemed) != 0
and D.status = 0
and d.ParentID = 1603
Group By A.Accountid, D.LastName, D.FirstName
Insert into #POSPoints(Accountid, FirstName, LastName, OpeningBal, Earned, Redeemed, DollarEarned, DollarRedeemed, Closingbal, POintsExpired)
Select A.Accountid, B.FirstName, B.Lastname as LastName, 0, 0, SUM(POspointsRedeemed) as Redeemed,
0 as DollarEarned ,SUM(POspointsRedeemed)/2500 as DollarRedeemed, 0 as ClosingBal, Sum(A.PointsExpired)
From AccActivity A
Join Account b on b.accountid = A.accountid
Where DateTimeActivity &gt;= {DateFrom} and DateTimeActivity &lt; {Dateto}
and TransactionID = 0
and PospointsRedeemed &gt;= 0
and b.status = 0
and b.ParentID = 1603
Group by A.Accountid, B.Lastname, B.FirstName
Insert into #POSPoints(Accountid, FirstName, LastName, OpeningBal, Earned, Redeemed, DollarEarned, DollarRedeemed, Closingbal, POintsExpired)
Select A.Accountid, B.FirstName, B.Lastname as LastName, 0, 0 as Earned, (0-SUM(POSPointsEarned)) as Redeemed,
0 as DollarEarned ,(0-SUM(POspointsEarned)/2500) as DollarRedeemed, 0 as ClosingBal, Sum(A.PointsExpired)
From AccActivity A
Join Account b on b.accountid = A.accountid
Where DateTimeActivity &gt;= {DateFrom} and DateTimeActivity &lt; {Dateto}
and TransactionID = 0
and PospointsEarned &lt;0
and b.status = 0
and b.ParentID = 1603
Group by A.Accountid, B.Lastname, B.FirstName
Insert into #POSPoints(Accountid, FirstName, LastName, OpeningBal, Earned, Redeemed, DollarEarned, DollarRedeemed, Closingbal, POintsExpired)
Select A.Accountid, B.FirstName, B.Lastname as LastName, 0, SUM(POSPointsEarned) as Earned, 0 as Redeemed,
SUM(POSPointsEarned)/2500 as DollarEarned ,0 as DollarRedeemed, 0 as ClosingBal, Sum(A.PointsExpired)
From AccActivity A
Join Account b on b.accountid = A.accountid
Where DateTimeActivity &gt;= {DateFrom} and DateTimeActivity &lt; {Dateto}
and TransactionID = 0
and POSPointsEarned &gt; 0
and b.status = 0
and b.ParentID = 1603
Group by A.Accountid, B.Lastname, B.FirstName
Insert into #POSPoints(Accountid, FirstName, LastName, OpeningBal, Earned, Redeemed, DollarEarned, DollarRedeemed, Closingbal, PointsExpired)
Select AccountID, FirstName, LastName, 0,0,0,0,0,0,0
From Account
Where AccountID not in (Select Accountid from #POSPoints )
and status = 0
and isparent = 0
and ParentID = 1603
;
WITH CTENonOpeningBal AS
(
SELECT
Accountid, PointsEnd,
ROW_NUMBER() OVER
(PARTITION BY Accountid ORDER BY DateTimeActivity desc, Accactivityid Desc) 'RowNum'
FROM Accactivity
where DateTimeActivity &lt; {Dateto}
)
Update #POSPoints
Set OpeningBal = B.PointsStart, Closingbal = B.PointsStart
From #POSPoints A
Inner Join
(
Select Accountid, (PointsEnd) as PointsStart
From CTENonOpeningBal
where rownum = 1
) B
on b.Accountid = A.AccountID
;
WITH CTEOpeningBal AS
(
SELECT
Accountid, PointsStart,
ROW_NUMBER() OVER
(PARTITION BY Accountid ORDER BY DateTimeActivity asc, Accactivityid asc) 'RowNum'
FROM Accactivity
Where DateTimeActivity &gt;= {DateFrom} and DateTimeActivity &lt; {Dateto}
and (POSPointsEarned !=0 or POSPointsRedeemed != 0 or PointsExpired !=0)
)
Update #POSPoints
Set OpeningBal = B.PointsStart
From #POSPoints A
Inner Join
(
Select Accountid, (PointsStart) as PointsStart
From CTEOpeningBal
where rownum = 1
) B
on b.Accountid = A.AccountID
;
WITH CTEClosingBal AS
(
SELECT
Accountid, PointsEnd,
ROW_NUMBER() OVER
(PARTITION BY Accountid ORDER BY DateTimeActivity DESC, Accactivityid Desc) 'RowNum'
FROM Accactivity
Where DateTimeActivity &gt;= {DateFrom} and DateTimeActivity &lt; {Dateto}
and (POSPointsEarned !=0 or POSPointsRedeemed != 0 or PointsExpired !=0)
)
Update #POSPoints
Set ClosingBal = B.PointsEnd
From #POSPoints A
Inner Join
(
Select Accountid, (PointsEnd) as PointsEnd
From CTEClosingBal
where rownum = 1
) B
on b.Accountid = A.AccountID
Select a.FirstName, A.LastName, A.OpeningBal, (A.OpeningBal)/2500, Sum(A.Earned) as ern, Sum(A.Redeemed),Sum(A.PointsExpired) as expr, Sum(A.DollarEarned), Sum(A.DollarRedeemed), A.ClosingBal,
(a.Closingbal)/2500, B.PArentid, ((A.OpeningBal + Sum(A.Earned) - Sum(A.Redeemed) - Sum(A.PointsExpired))- A.Closingbal) as PointsCheck
From #POSPoints A
Join Account B On B.Accountid = A.Accountid
Group by A.Accountid,A.LastName, A.Firstname, A.OpeningBal, A.ClosingBal, B.Parentid, b.PointsEarned, b.PointsExpired, b.PointsRedeemed
order by A.lastname,a.FirstName
drop Table #pospoints
</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;FirstName&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;Last 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;Opening Balance&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;Dec2Int64&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;$$ Opening Bal&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;CurrInt64&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;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;Dec2Int64&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;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;Dec2Int64&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;Points Expired&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;Dec2Int64&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;$$ 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;CurrInt64&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;$$ 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;CurrInt64&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;Closing Balance&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;Dec2Int64&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;$$ Closing Bal&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;CurrInt64&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;Group&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;Account&lt;/eList&gt;&lt;/cColInfo&gt;</value></item><item><key>12</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;12&lt;/sKey&gt;&lt;sText&gt;Points Balance Check&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;Int&lt;/eFormat&gt;&lt;eList&gt;None&lt;/eList&gt;&lt;/cColInfo&gt;</value></item></dictionary></oColumnDefs></cCustomReport></sViewXML>
<UpdateAll>false</UpdateAll>
</cViews>