Back to home

INTERNAL - Bepoz Help Guides

End-User | Reporting, Data Analysis & Security

Custom Reports

10 | Cost Variance New (Custom Report)

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

This article demonstrates an example of a Custom Cost Variance 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>678</iViewID>

<iListType>25</iListType>

<dtDateUpdated>2016-07-04T08:37:24</dtDateUpdated>

<iOperatorID>0</iOperatorID>

<sName>Cost Variance New</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>Cost Variance New</sReportName><sSQL>Select A.LongName,A.ParentID, A.StockTypeQty, Selc.ReceivedEachEx, SelC.ReceivedUnits, B.Name, Selc.SuppInvoiceNum, D.Name, Selc.PurchaseDate,

Selc.BRecEx, Selc.BRecU, C.Name, Selc.BPurchDate, selc.BInvNum, E.Name, Round((Selc.ReceivedEachEx- Selc.BRecEx),4) as diff

From

(

Select SelA.ProductID, SelA.PurchaseDate, SelA.SuppinvoiceNum,SelA.StockTypeQty, SelA.ReceivedUnits,SelA.ReceivedEachEx, SelA.Supplierid, SelA.Storeid,

SelB.ProductID as BProductID, SelB.PurchaseDate as BPurchDate, SelB.SuppinvoiceNum as BInvNum,

SelB.StockTypeQty as BStockTQ, SelB.ReceivedUnits as BRecU,SelB.ReceivedEachEx as BRecEx, Selb.Supplierid as BSuppid, Selb.Storeid as BStore

From

(

Select * From

(

Select A.ProductID, A.Receivedeachex, A.StockTypeQty, A.ReceivedUnits, C.SuppInvoiceNum, C.PurchaseDAte, C.Supplierid,a.Storeid,

ROW_Number() over (Partition by A.ProductID order by C.PurchaseDate desc) as rn

From POline A

Join POHeader C on C.POHeaderid = A.POHeaderID

Where C.VenueID like '{VenueID}'

and A.StoreID like '{Storeid}'

and A.ProductID &lt;&gt;0

and A.ReceivedEachEx &gt;0

) sel

where rn =1

) SelA

Full Join

(Select * From

(

Select A.ProductID, A.Receivedeachex, A.StockTypeQty, A.ReceivedUnits, C.SuppInvoiceNum, C.PurchaseDAte, C.Supplierid, a.Storeid,

ROW_Number() over (Partition by A.ProductID order by C.PurchaseDate desc) as rn

From POline A

Join POHeader C on C.POHeaderid = A.POHeaderID

Where C.VenueID like '{VenueID}'

and A.ProductID &lt;&gt;0

and A.ReceivedEachEx &gt;0

) sel

where rn =2

) SelB

on SelB.Productid = SelA.ProductiD

) Selc

Join Product A on A.ProductID = Selc.ProductID

Join Supplier B on B.SupplierID = Selc.Supplierid

Join Supplier C on C.Supplierid = Selc.BSuppid

Join Store D on D.Storeid = Selc.Storeid

Join Store E on E.StoreID = Selc.BStore

Where Selc.purchasedate &gt;= {DateFrom} and Selc.PurchaseDate&lt;= {DateTo}

and Cast((Selc.ReceivedEachEx- Selc.BRecEx) as decimal(10,2)) &lt;&gt; 0

order by A.PArentid, Selc.productid, purchaseDate Desc</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;Product&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;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;Text&lt;/eFormat&gt;&lt;eList&gt;Product&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;Volume&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;Last Cost&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;CurrDbl&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;Recv Units&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>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;Supplier&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;Inv 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>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;Recv 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>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;Inv 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>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;Cost Before Last&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;CurrDbl&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;Units CBL&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>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;Supplier CBL&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>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;Inv Number CBL&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>13</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;13&lt;/sKey&gt;&lt;sText&gt;Inv Date CBL&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>14</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;14&lt;/sKey&gt;&lt;sText&gt;Store CBL&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>15</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;15&lt;/sKey&gt;&lt;sText&gt;Cost Diff&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;CurrDbl&lt;/eFormat&gt;&lt;eList&gt;None&lt;/eList&gt;&lt;/cColInfo&gt;</value></item></dictionary></oColumnDefs></cCustomReport></sViewXML>

10 | Cost Variance New (Custom Report)

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

This article demonstrates an example of a Custom Cost Variance Report.

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>678</iViewID>

<iListType>25</iListType>

<dtDateUpdated>2016-07-04T08:37:24</dtDateUpdated>

<iOperatorID>0</iOperatorID>

<sName>Cost Variance New</sName>

Selc.BRecEx, Selc.BRecU, C.Name, Selc.BPurchDate, selc.BInvNum, E.Name, Round((Selc.ReceivedEachEx- Selc.BRecEx),4) as diff

From

(

Select SelA.ProductID, SelA.PurchaseDate, SelA.SuppinvoiceNum,SelA.StockTypeQty, SelA.ReceivedUnits,SelA.ReceivedEachEx, SelA.Supplierid, SelA.Storeid,

SelB.ProductID as BProductID, SelB.PurchaseDate as BPurchDate, SelB.SuppinvoiceNum as BInvNum,

SelB.StockTypeQty as BStockTQ, SelB.ReceivedUnits as BRecU,SelB.ReceivedEachEx as BRecEx, Selb.Supplierid as BSuppid, Selb.Storeid as BStore

From

(

Select * From

(

Select A.ProductID, A.Receivedeachex, A.StockTypeQty, A.ReceivedUnits, C.SuppInvoiceNum, C.PurchaseDAte, C.Supplierid,a.Storeid,

ROW_Number() over (Partition by A.ProductID order by C.PurchaseDate desc) as rn

From POline A

Join POHeader C on C.POHeaderid = A.POHeaderID

Where C.VenueID like '{VenueID}'

and A.StoreID like '{Storeid}'

and A.ProductID &amp;lt;&amp;gt;0

and A.ReceivedEachEx &amp;gt;0

) sel

where rn =1

) SelA

Full Join

(Select * From

(

Select A.ProductID, A.Receivedeachex, A.StockTypeQty, A.ReceivedUnits, C.SuppInvoiceNum, C.PurchaseDAte, C.Supplierid, a.Storeid,

ROW_Number() over (Partition by A.ProductID order by C.PurchaseDate desc) as rn

From POline A

Join POHeader C on C.POHeaderid = A.POHeaderID

Where C.VenueID like '{VenueID}'

and A.ProductID &amp;lt;&amp;gt;0

and A.ReceivedEachEx &amp;gt;0

) sel

where rn =2

) SelB

on SelB.Productid = SelA.ProductiD

) Selc

Join Product A on A.ProductID = Selc.ProductID

Join Supplier B on B.SupplierID = Selc.Supplierid

Join Supplier C on C.Supplierid = Selc.BSuppid

Join Store D on D.Storeid = Selc.Storeid

Join Store E on E.StoreID = Selc.BStore

Where Selc.purchasedate &amp;gt;= {DateFrom} and Selc.PurchaseDate&amp;lt;= {DateTo}

and Cast((Selc.ReceivedEachEx- Selc.BRecEx) as decimal(10,2)) &amp;lt;&amp;gt; 0

<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

and A.ProductID <>0

and A.ReceivedEachEx >0

and A.ProductID <>0

and A.ReceivedEachEx >0

Where Selc.purchasedate >= {DateFrom} and Selc.PurchaseDate<= {DateTo}

and Cast((Selc.ReceivedEachEx- Selc.BRecEx) as decimal(10,2)) <> 0