Back to home

INTERNAL - Bepoz Help Guides

End-User | Reporting, Data Analysis & Security

Custom Reports

55 | SMBS (Custom Report)

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

This article demonstrates an example of a Custom SMBS 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>2014-01-22T16:40:47</dtDateUpdated>

<iOperatorID>0</iOperatorID>

<sName>SMBS</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>Stock Movement by Store</sReportName><sSQL>DECLARE @tempA Table ( StoreID int, Name varchar(30), CostOnHandEx decimal, Adjustments float)

DECLARE @tempB Table ( StoreID int, Name varchar(30), CostOnHandEx float, Adjustments float)

DECLARE @tempC Table ( StoreID int, Name varchar(30), CostOnHandEx float, Adjustments float)

DECLARE @tempD Table ( StoreID int, Name varchar(30), Purchases float, Xfers float, Other float)

/* This is to retrieve all the Stores*/

INSERT INTO @tempC

select Store.StoreID, Store.Name, 0, 0 from Store

/* This is the Stock Adjustments Total*/

INSERT INTO @tempA

SELECT Store.StoreID, Store.Name, 0 AS Expr1,

SUM(StockSummaries.CostStockSoldEx + StockSummaries.CostLossEx + StockSummaries.CostReceivedEx + StockSummaries.CostXFerInEx + StockSummaries.CostXFerOutEx + StockSummaries.CostStkTakeEx

+ StockSummaries.CostAdjustEx + StockSummaries.CostCorrectionEx) AS Adjustment

FROM StockSummaries INNER JOIN

Store ON StockSummaries.StoreID = Store.StoreID

WHERE (StockSummaries.ShiftID &gt;= {ShiftFrom})

GROUP BY Store.StoreID, Store.Name

/* This is the Stock Summaries Totals for Purchases, Xfers, Others Total*/

INSERT INTO @TempD

SELECT Store.StoreID, Store.Name,

SUM(StockSummaries.CostReceivedEx) as 'Purchases', sum(StockSummaries.CostXFerInEx + StockSummaries.CostXFerOutEx) as 'XferIn/Out', sum(StockSummaries.CostStkTakeEx +

+ StockSummaries.CostAdjustEx + StockSummaries.CostCorrectionEx + StockSummaries.CostStockSoldEx + StockSummaries.CostLossEx) AS 'OtherAdjustments'

FROM StockSummaries INNER JOIN

Store ON StockSummaries.StoreID = Store.StoreID

WHERE (StockSummaries.ShiftID &gt;= {ShiftFrom} and StockSummaries.ShiftID &lt;= {ShiftTo})

GROUP BY Store.StoreID, Store.Name

/* This is the CostExOnHand Totals*/

INSERT INTO @tempB

SELECT Store.StoreID, Store.Name, sum(ProdStore.CostExonHand) as OnHand, 0

FROM ProdStore INNER JOIN

Store ON ProdStore.StoreID = Store.StoreID

GROUP BY Store.StoreID, Store.Name

select c.Name, ISNULL(b.CostOnHandEx - a.Adjustments,b.CostOnHandEx) as 'OpeningValue', ISNULL(d.Purchases,0) as 'Purchases', ISNULL( d.Xfers,0) as 'Transfers In/Out', ISNULL( d.Other,0) as 'Other Adjustments',

ISNULL(b.CostOnHandEx - a.Adjustments + d.Purchases + d.Xfers + d.Other ,b.CostOnHandEx) as 'ClosingValue'

from @tempC c LEFT OUTER JOIN @tempA a ON c.StoreID = a.StoreID LEFT OUTER JOIN @tempb b ON b.StoreID = c.StoreID LEFT OUTER JOIN @tempD d ON d.StoreID = c.StoreID

</sSQL><iFlags>0</iFlags><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;Store 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>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;Opening Value&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;CurrDbl&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;Purchases&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;CurrDbl&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;Transfers In/Out&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;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;Other Adjustments&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;CurrDbl&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;Closing Value&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;CurrDbl&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

55 | SMBS (Custom Report)

This article demonstrates an example of a Custom SMBS 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>2014-01-22T16:40:47</dtDateUpdated>

<iOperatorID>0</iOperatorID>

<sName>SMBS</sName>

DECLARE @tempB Table ( StoreID int, Name varchar(30), CostOnHandEx float, Adjustments float)

DECLARE @tempC Table ( StoreID int, Name varchar(30), CostOnHandEx float, Adjustments float)

DECLARE @tempD Table ( StoreID int, Name varchar(30), Purchases float, Xfers float, Other float)

/* This is to retrieve all the Stores*/

INSERT INTO @tempC

select Store.StoreID, Store.Name, 0, 0 from Store

/* This is the Stock Adjustments Total*/

INSERT INTO @tempA

SELECT Store.StoreID, Store.Name, 0 AS Expr1,

SUM(StockSummaries.CostStockSoldEx + StockSummaries.CostLossEx + StockSummaries.CostReceivedEx + StockSummaries.CostXFerInEx + StockSummaries.CostXFerOutEx + StockSummaries.CostStkTakeEx

+ StockSummaries.CostAdjustEx + StockSummaries.CostCorrectionEx) AS Adjustment

FROM StockSummaries INNER JOIN

Store ON StockSummaries.StoreID = Store.StoreID

WHERE (StockSummaries.ShiftID &amp;gt;= {ShiftFrom})

GROUP BY Store.StoreID, Store.Name

/* This is the Stock Summaries Totals for Purchases, Xfers, Others Total*/

INSERT INTO @TempD

SELECT Store.StoreID, Store.Name,

SUM(StockSummaries.CostReceivedEx) as 'Purchases', sum(StockSummaries.CostXFerInEx + StockSummaries.CostXFerOutEx) as 'XferIn/Out', sum(StockSummaries.CostStkTakeEx +

+ StockSummaries.CostAdjustEx + StockSummaries.CostCorrectionEx + StockSummaries.CostStockSoldEx + StockSummaries.CostLossEx) AS 'OtherAdjustments'

FROM StockSummaries INNER JOIN

Store ON StockSummaries.StoreID = Store.StoreID

WHERE (StockSummaries.ShiftID &amp;gt;= {ShiftFrom} and StockSummaries.ShiftID &amp;lt;= {ShiftTo})

GROUP BY Store.StoreID, Store.Name

/* This is the CostExOnHand Totals*/

INSERT INTO @tempB

SELECT Store.StoreID, Store.Name, sum(ProdStore.CostExonHand) as OnHand, 0

FROM ProdStore INNER JOIN

Store ON ProdStore.StoreID = Store.StoreID

GROUP BY Store.StoreID, Store.Name

select c.Name, ISNULL(b.CostOnHandEx - a.Adjustments,b.CostOnHandEx) as 'OpeningValue', ISNULL(d.Purchases,0) as 'Purchases', ISNULL( d.Xfers,0) as 'Transfers In/Out', ISNULL( d.Other,0) as 'Other Adjustments',

ISNULL(b.CostOnHandEx - a.Adjustments + d.Purchases + d.Xfers + d.Other ,b.CostOnHandEx) as 'ClosingValue'

from @tempC c LEFT OUTER JOIN @tempA a ON c.StoreID = a.StoreID LEFT OUTER JOIN @tempb b ON b.StoreID = c.StoreID LEFT OUTER JOIN @tempD d ON d.StoreID = c.StoreID

<UpdateAll>false</UpdateAll>

</cViews>

Other Parameter Settings:

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

WHERE (StockSummaries.ShiftID >= {ShiftFrom})

WHERE (StockSummaries.ShiftID >= {ShiftFrom} and StockSummaries.ShiftID <= {ShiftTo})