9 | COGS CostofGoods (Custom Report)

This article demonstrates an example of a Custom COGS 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-11T11:28:55</dtDateUpdated>

<iOperatorID>0</iOperatorID>

<sName>COGS</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>Cost of Goods Detail</sReportName><sSQL>SELECT CASE WHEN Store.Name IS NULL THEN (store_1.name) ELSE (store.name) END AS Store,

CASE WHEN Product_1.ParentId IS NULL THEN Product.ParentId else product_1.ParentId END as Parentid,

CASE WHEN Product_1.Name is null then product.Name else product_1.Name end AS [Product Name],

CASE WHEN SUM(stocksummaries.CostReceivedEx) IS NULL THEN 0 ELSE SUM(StockSummaries.CostReceivedEx) END AS 'Purchased',

CASE WHEN SUM(StockSummaries.CostXFerInEx) IS NULL THEN 0 ELSE SUM(StockSummaries.CostXFerInEx) END AS 'Transfer In',

CASE WHEN SUM(StockSummaries.CostXFerOutEx) IS NULL THEN 0 ELSE SUM(StockSummaries.CostXFerOutEx) END AS 'Transfer Out',

CASE WHEN SUM(StockSummaries.CostCorrectionEx) IS NULL THEN 0 ELSE SUM(StockSummaries.CostCorrectionEx) END AS 'Cost Correction',

CASE WHEN SUM(StockSummaries.CostStkTakeEx) IS NULL THEN 0 ELSE SUM(StockSummaries.CostStkTakeEx) END AS 'Discrepancy Lost/Found',

CASE WHEN SUM(StockSummaries.CostLossEx) + sum(StockSummaries.CostAdjustEx) IS NULL

THEN 0 ELSE SUM(StockSummaries.CostLossEx) + sum(StockSummaries.CostAdjustEx) END AS 'Wastage',

CASE WHEN SUM(SalesSummaries.CostSalesEx) IS NULL THEN 0 ELSE SUM(SalesSummaries.CostSalesEx) END AS 'Cost of Sales Ex',

CASE WHEN SUM(salessummaries.NettTotal) IS NULL THEN 0 ELSE SUM(salessummaries.NettTotal) END AS Nett,

CASE WHEN SUM(salessummaries.NettTotal) - SUM(SalesSummaries.CostSalesEx) IS NULL THEN 0 ELSE SUM(salessummaries.NettTotal) - SUM(SalesSummaries.CostSalesEx) END AS 'Profit Amount',

CASE WHEN (SUM(salessummaries.NettTotal) - SUM(SalesSummaries.CostSalesEx)) / nullif((SUM(salessummaries.NettTotal)),0) * 10000 IS NULL

THEN 0 ELSE (SUM(salessummaries.NettTotal) - SUM(SalesSummaries.CostSalesEx)) / (SUM(salessummaries.NettTotal)) * 10000 END AS 'Profit %',

CASE

WHEN SUM(StockSummaries.CostLossEx) IS NULL THEN SUM(salessummaries.NettTotal) - SUM(SalesSummaries.CostSalesEx)

WHEN SUM(StockSummaries.CostAdjustEx) IS NULL THEN SUM(salessummaries.NettTotal) - SUM(SalesSummaries.CostSalesEx)

WHEN SUM(StockSummaries.CostStkTakeEx)IS NULL THEN SUM(salessummaries.NettTotal) - SUM(SalesSummaries.CostSalesEx)

WHEN SUM(StockSummaries.CostCorrectionEx) IS NULL THEN SUM(salessummaries.NettTotal) - SUM(SalesSummaries.CostSalesEx)

WHEN SUM(salessummaries.NettTotal)IS NULL THEN 0

WHEN SUM(SalesSummaries.CostSalesEx) IS NULL THEN 0

ELSE SUM(salessummaries.NettTotal) - (SUM(SalesSummaries.CostSalesEx)- (SUM(StockSummaries.CostLossEx)+ SUM(StockSummaries.CostAdjustEx)+ SUM (StockSummaries.CostStkTakeEx) + SUM(StockSummaries.CostCorrectionEx)))

END AS 'Profit Amount Adjusted',

CASE

WHEN (SUM(salessummaries.NettTotal) - (SUM(SalesSummaries.CostSalesEx)- (SUM(StockSummaries.CostLossEx)+ SUM(StockSummaries.CostAdjustEx)+ SUM (StockSummaries.CostStkTakeEx) + SUM(StockSummaries.CostCorrectionEx)))) / nullif((SUM(salessummaries.NettTotal)),0) * 10000 IS NULL

THEN 0 ELSE (SUM(salessummaries.NettTotal) - (SUM(SalesSummaries.CostSalesEx)- (SUM(StockSummaries.CostLossEx)+ SUM(StockSummaries.CostAdjustEx)+ SUM (StockSummaries.CostStkTakeEx) + SUM(StockSummaries.CostCorrectionEx)))) / (SUM(salessummaries.NettTotal)) * 10000 END AS 'Profit % Adjusted'

FROM Store AS Store_1 INNER JOIN

SalesSummaries ON Store_1.StoreID = SalesSummaries.StoreID INNER JOIN

Product ON SalesSummaries.ProductID = Product.ProductID FULL OUTER JOIN

StockSummaries INNER JOIN

Store ON StockSummaries.StoreID = Store.StoreID INNER JOIN

Product AS Product_1 ON StockSummaries.ProductID = Product_1.ProductID ON SalesSummaries.ShiftID = StockSummaries.ShiftID AND

SalesSummaries.ProductID = StockSummaries.ProductID AND SalesSummaries.StoreID = StockSummaries.StoreID

WHERE stocksummaries.STOREID LIKE '{STOREID}' and stocksummaries.shiftid &gt;= {ShiftFrom} and stocksummaries.shiftid &lt;= {ShiftTo}

GROUP BY Store.Name, Store_1.Name, Product_1.Name, Product_1.ParentId, Product.ParentID, Product.Name

order by store.name, product.ParentId

</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 Location&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;Group 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;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: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;Product 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;Int64&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;Purchased&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: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;Xfer In&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:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;&lt;sKey&gt;5&lt;/sKey&gt;&lt;sText&gt;Xfer 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>6</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;6&lt;/sKey&gt;&lt;sText&gt;Cost Correction&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>7</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;7&lt;/sKey&gt;&lt;sText&gt;Discrepancy Lost/Found&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>8</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;8&lt;/sKey&gt;&lt;sText&gt;Wastage&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>9</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;9&lt;/sKey&gt;&lt;sText&gt;Cost Of Sales EX&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>10</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;10&lt;/sKey&gt;&lt;sText&gt;Nett Total Ex&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>11</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;11&lt;/sKey&gt;&lt;sText&gt;Profit Amt&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>12</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;12&lt;/sKey&gt;&lt;sText&gt;Profit %&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;Perc2Int&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:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;&lt;sKey&gt;13&lt;/sKey&gt;&lt;sText&gt;Profit Amt Adjusted&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>14</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;14&lt;/sKey&gt;&lt;sText&gt;Profit % Adjusted&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;Perc2Int&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

9 | COGS CostofGoods (Custom Report)

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

This article demonstrates an example of a Custom COGS 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">

<iListType>25</iListType>

<dtDateUpdated>2013-04-11T11:28:55</dtDateUpdated>

<iOperatorID>0</iOperatorID>

<sName>COGS</sName>

CASE WHEN Product_1.ParentId IS NULL THEN Product.ParentId else product_1.ParentId END as Parentid,

CASE WHEN Product_1.Name is null then product.Name else product_1.Name end AS [Product Name],

CASE WHEN SUM(stocksummaries.CostReceivedEx) IS NULL THEN 0 ELSE SUM(StockSummaries.CostReceivedEx) END AS 'Purchased',

CASE WHEN SUM(StockSummaries.CostXFerInEx) IS NULL THEN 0 ELSE SUM(StockSummaries.CostXFerInEx) END AS 'Transfer In',

CASE WHEN SUM(StockSummaries.CostXFerOutEx) IS NULL THEN 0 ELSE SUM(StockSummaries.CostXFerOutEx) END AS 'Transfer Out',

CASE WHEN SUM(StockSummaries.CostCorrectionEx) IS NULL THEN 0 ELSE SUM(StockSummaries.CostCorrectionEx) END AS 'Cost Correction',

CASE WHEN SUM(StockSummaries.CostStkTakeEx) IS NULL THEN 0 ELSE SUM(StockSummaries.CostStkTakeEx) END AS 'Discrepancy Lost/Found',

CASE WHEN SUM(StockSummaries.CostLossEx) + sum(StockSummaries.CostAdjustEx) IS NULL

THEN 0 ELSE SUM(StockSummaries.CostLossEx) + sum(StockSummaries.CostAdjustEx) END AS 'Wastage',

CASE WHEN SUM(SalesSummaries.CostSalesEx) IS NULL THEN 0 ELSE SUM(SalesSummaries.CostSalesEx) END AS 'Cost of Sales Ex',

CASE WHEN SUM(salessummaries.NettTotal) IS NULL THEN 0 ELSE SUM(salessummaries.NettTotal) END AS Nett,

CASE WHEN SUM(salessummaries.NettTotal) - SUM(SalesSummaries.CostSalesEx) IS NULL THEN 0 ELSE SUM(salessummaries.NettTotal) - SUM(SalesSummaries.CostSalesEx) END AS 'Profit Amount',

CASE WHEN (SUM(salessummaries.NettTotal) - SUM(SalesSummaries.CostSalesEx)) / nullif((SUM(salessummaries.NettTotal)),0) * 10000 IS NULL

THEN 0 ELSE (SUM(salessummaries.NettTotal) - SUM(SalesSummaries.CostSalesEx)) / (SUM(salessummaries.NettTotal)) * 10000 END AS 'Profit %',

CASE

WHEN SUM(StockSummaries.CostLossEx) IS NULL THEN SUM(salessummaries.NettTotal) - SUM(SalesSummaries.CostSalesEx)

WHEN SUM(StockSummaries.CostAdjustEx) IS NULL THEN SUM(salessummaries.NettTotal) - SUM(SalesSummaries.CostSalesEx)

WHEN SUM(StockSummaries.CostStkTakeEx)IS NULL THEN SUM(salessummaries.NettTotal) - SUM(SalesSummaries.CostSalesEx)

WHEN SUM(StockSummaries.CostCorrectionEx) IS NULL THEN SUM(salessummaries.NettTotal) - SUM(SalesSummaries.CostSalesEx)

WHEN SUM(salessummaries.NettTotal)IS NULL THEN 0

WHEN SUM(SalesSummaries.CostSalesEx) IS NULL THEN 0

ELSE SUM(salessummaries.NettTotal) - (SUM(SalesSummaries.CostSalesEx)- (SUM(StockSummaries.CostLossEx)+ SUM(StockSummaries.CostAdjustEx)+ SUM (StockSummaries.CostStkTakeEx) + SUM(StockSummaries.CostCorrectionEx)))

END AS 'Profit Amount Adjusted',

CASE

WHEN (SUM(salessummaries.NettTotal) - (SUM(SalesSummaries.CostSalesEx)- (SUM(StockSummaries.CostLossEx)+ SUM(StockSummaries.CostAdjustEx)+ SUM (StockSummaries.CostStkTakeEx) + SUM(StockSummaries.CostCorrectionEx)))) / nullif((SUM(salessummaries.NettTotal)),0) * 10000 IS NULL

THEN 0 ELSE (SUM(salessummaries.NettTotal) - (SUM(SalesSummaries.CostSalesEx)- (SUM(StockSummaries.CostLossEx)+ SUM(StockSummaries.CostAdjustEx)+ SUM (StockSummaries.CostStkTakeEx) + SUM(StockSummaries.CostCorrectionEx)))) / (SUM(salessummaries.NettTotal)) * 10000 END AS 'Profit % Adjusted'

FROM Store AS Store_1 INNER JOIN

SalesSummaries ON Store_1.StoreID = SalesSummaries.StoreID INNER JOIN

Product ON SalesSummaries.ProductID = Product.ProductID FULL OUTER JOIN

StockSummaries INNER JOIN

Store ON StockSummaries.StoreID = Store.StoreID INNER JOIN

Product AS Product_1 ON StockSummaries.ProductID = Product_1.ProductID ON SalesSummaries.ShiftID = StockSummaries.ShiftID AND

SalesSummaries.ProductID = StockSummaries.ProductID AND SalesSummaries.StoreID = StockSummaries.StoreID

WHERE stocksummaries.STOREID LIKE '{STOREID}' and stocksummaries.shiftid &amp;gt;= {ShiftFrom} and stocksummaries.shiftid &amp;lt;= {ShiftTo}

GROUP BY Store.Name, Store_1.Name, Product_1.Name, Product_1.ParentId, Product.ParentID, Product.Name

order by store.name, product.ParentId

<UpdateAll>false</UpdateAll>

</cViews>

Other Parameter Settings:

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

WHERE stocksummaries.STOREID LIKE '{STOREID}' and stocksummaries.shiftid >= {ShiftFrom} and stocksummaries.shiftid <= {ShiftTo}