Back to home

INTERNAL - Bepoz Help Guides

End-User | Reporting, Data Analysis & Security

Custom Reports

42 | Product Size by OperatorV3.5 (Custom Report)

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

This article demonstrates an example of a Custom Product Size by Operator 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-07-14T14:15:32</dtDateUpdated>

<iOperatorID>0</iOperatorID>

<sName>Product Size by OperatorV3.5</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>Product size count by operV3.5</sReportName><sSQL>Select

(SELECT FirstName FROM Operator WHERE Operator.OperatorID = Transactions.OperatorID) AS FirstName,

(SELECT LastName FROM Operator WHERE Operator.OperatorID = Transactions.OperatorID) AS LastName,

(SELECT Name FROM Product WHERE Product.ProductID = Translines.ProductId) AS ProductName,

sum(Translines.UnitQtySold/100) As TotalCount,

CAST(sum(CASE Translines.Size WHEN 1 then Translines.UnitQtySold end)/100 AS VARCHAR) + ' x ' + (SELECT Product.SizeName_1 FROM Product WHERE Product.ProductID = Translines.ProductID) as Size1,

CAST(sum(CASE Translines.Size WHEN 2 then Translines.UnitQtySold end)/100 AS VARCHAR) + ' x ' + (SELECT Product.SizeName_2 FROM Product WHERE Product.ProductID = Translines.ProductID) as Size2,

CAST(sum(CASE Translines.Size WHEN 3 then Translines.UnitQtySold end)/100 AS VARCHAR) + ' x ' + (SELECT Product.SizeName_3 FROM Product WHERE Product.ProductID = Translines.ProductID) as Size3,

CAST(sum(CASE Translines.Size WHEN 4 then Translines.UnitQtySold end)/100 AS VARCHAR) + ' x ' + (SELECT Product.SizeName_4 FROM Product WHERE Product.ProductID = Translines.ProductID) as Size4,

CAST(sum(CASE Translines.Size WHEN 5 then Translines.UnitQtySold end)/100 AS VARCHAR) + ' x ' + (SELECT Product.SizeName_5 FROM Product WHERE Product.ProductID = Translines.ProductID) as Size5,

CAST(sum(CASE Translines.Size WHEN 6 then Translines.UnitQtySold end)/100 AS VARCHAR) + ' x ' + (SELECT Product.SizeName_6 FROM Product WHERE Product.ProductID = Translines.ProductID) as Size6,

Sum(Translines.Gross) as Gross,

Sum(Translines.Nett) as Nett,

Sum(Translines.CostTotal)*100 as Cost,

(Sum(Translines.Nett/1.1)) - (Sum(Translines.CostTotal)*100) AS Profit,

CASE WHEN Sum(Translines.Nett/1.1) = 0 THEN 0 ELSE (Sum(Translines.Nett/1.1) - Sum(Translines.CostTotal)100) / Sum(Translines.Nett/1.1)10000 END as ProfitPer

FROM Transactions

INNER JOIN Translines WITH (NOLOCK)

ON Transactions.TransactionID = Translines.TransactionID AND Transactions.SHIFTID &gt;= {SHIFTFROM}

INNER JOIN Store St WITH (NOLOCK)

ON Translines.StockStoreID = St.StoreID

AND Translines.StockStoreID LIKE '{STOREID}'

GROUP BY Transactions.OperatorID, Translines.ProductId

</sSQL><iFlags>8</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;First 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;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;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;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;Total Count&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><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;Size 1 Count&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>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;Size 2 Count&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;Size 3 Count&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;Size 4 Count&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;Size 5 Count&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>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;Size 6 Count&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>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;Gross&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>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;Nett&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>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;Cost&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>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;Profit&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>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;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></dictionary></oColumnDefs></cCustomReport></sViewXML>

42 | Product Size by OperatorV3.5 (Custom Report)

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

Disclaimer:

<?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-07-14T14:15:32</dtDateUpdated>

<iOperatorID>0</iOperatorID>

<sName>Product Size by OperatorV3.5</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>Product size count by operV3.5</sReportName><sSQL>Select

(SELECT FirstName FROM Operator WHERE Operator.OperatorID = Transactions.OperatorID) AS FirstName,

(SELECT LastName FROM Operator WHERE Operator.OperatorID = Transactions.OperatorID) AS LastName,

(SELECT Name FROM Product WHERE Product.ProductID = Translines.ProductId) AS ProductName,

sum(Translines.UnitQtySold/100) As TotalCount,

CAST(sum(CASE Translines.Size WHEN 1 then Translines.UnitQtySold end)/100 AS VARCHAR) + ' x ' + (SELECT Product.SizeName_1 FROM Product WHERE Product.ProductID = Translines.ProductID) as Size1,

CAST(sum(CASE Translines.Size WHEN 2 then Translines.UnitQtySold end)/100 AS VARCHAR) + ' x ' + (SELECT Product.SizeName_2 FROM Product WHERE Product.ProductID = Translines.ProductID) as Size2,

CAST(sum(CASE Translines.Size WHEN 3 then Translines.UnitQtySold end)/100 AS VARCHAR) + ' x ' + (SELECT Product.SizeName_3 FROM Product WHERE Product.ProductID = Translines.ProductID) as Size3,

CAST(sum(CASE Translines.Size WHEN 4 then Translines.UnitQtySold end)/100 AS VARCHAR) + ' x ' + (SELECT Product.SizeName_4 FROM Product WHERE Product.ProductID = Translines.ProductID) as Size4,

CAST(sum(CASE Translines.Size WHEN 5 then Translines.UnitQtySold end)/100 AS VARCHAR) + ' x ' + (SELECT Product.SizeName_5 FROM Product WHERE Product.ProductID = Translines.ProductID) as Size5,

CAST(sum(CASE Translines.Size WHEN 6 then Translines.UnitQtySold end)/100 AS VARCHAR) + ' x ' + (SELECT Product.SizeName_6 FROM Product WHERE Product.ProductID = Translines.ProductID) as Size6,

Sum(Translines.Gross) as Gross,

Sum(Translines.Nett) as Nett,

Sum(Translines.CostTotal)*100 as Cost,

(Sum(Translines.Nett/1.1)) - (Sum(Translines.CostTotal)*100) AS Profit,

CASE WHEN Sum(Translines.Nett/1.1) = 0 THEN 0 ELSE (Sum(Translines.Nett/1.1) - Sum(Translines.CostTotal)100) / Sum(Translines.Nett/1.1)10000 END as ProfitPer

FROM Transactions

INNER JOIN Translines WITH (NOLOCK)

ON Transactions.TransactionID = Translines.TransactionID AND Transactions.SHIFTID &gt;= {SHIFTFROM}

INNER JOIN Store St WITH (NOLOCK)

ON Translines.StockStoreID = St.StoreID

AND Translines.StockStoreID LIKE '{STOREID}'

GROUP BY Transactions.OperatorID, Translines.ProductId

<UpdateAll>false</UpdateAll>

</cViews>

Other Parameter Settings:

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

or

42 | Product Size by OperatorV3.5 (Custom Report)

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

Disclaimer:

<?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-07-14T14:15:32</dtDateUpdated>

<iOperatorID>0</iOperatorID>

<sName>Product Size by OperatorV3.5</sName>

<sViewXML>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;cCustomReport xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;&lt;sReportName&gt;Product size count by operV3.5&lt;/sReportName&gt;&lt;sSQL&gt;Select

(SELECT FirstName FROM Operator WHERE Operator.OperatorID = Transactions.OperatorID) AS FirstName,

(SELECT LastName FROM Operator WHERE Operator.OperatorID = Transactions.OperatorID) AS LastName,

(SELECT Name FROM Product WHERE Product.ProductID = Translines.ProductId) AS ProductName,

sum(Translines.UnitQtySold/100) As TotalCount,

CAST(sum(CASE Translines.Size WHEN 1 then Translines.UnitQtySold end)/100 AS VARCHAR) + ' x ' + (SELECT Product.SizeName_1 FROM Product WHERE Product.ProductID = Translines.ProductID) as Size1,

CAST(sum(CASE Translines.Size WHEN 2 then Translines.UnitQtySold end)/100 AS VARCHAR) + ' x ' + (SELECT Product.SizeName_2 FROM Product WHERE Product.ProductID = Translines.ProductID) as Size2,

CAST(sum(CASE Translines.Size WHEN 3 then Translines.UnitQtySold end)/100 AS VARCHAR) + ' x ' + (SELECT Product.SizeName_3 FROM Product WHERE Product.ProductID = Translines.ProductID) as Size3,

CAST(sum(CASE Translines.Size WHEN 4 then Translines.UnitQtySold end)/100 AS VARCHAR) + ' x ' + (SELECT Product.SizeName_4 FROM Product WHERE Product.ProductID = Translines.ProductID) as Size4,

CAST(sum(CASE Translines.Size WHEN 5 then Translines.UnitQtySold end)/100 AS VARCHAR) + ' x ' + (SELECT Product.SizeName_5 FROM Product WHERE Product.ProductID = Translines.ProductID) as Size5,

CAST(sum(CASE Translines.Size WHEN 6 then Translines.UnitQtySold end)/100 AS VARCHAR) + ' x ' + (SELECT Product.SizeName_6 FROM Product WHERE Product.ProductID = Translines.ProductID) as Size6,

Sum(Translines.Gross) as Gross,

Sum(Translines.Nett) as Nett,

Sum(Translines.CostTotal)*100 as Cost,

(Sum(Translines.Nett/1.1)) - (Sum(Translines.CostTotal)*100) AS Profit,

CASE WHEN Sum(Translines.Nett/1.1) = 0 THEN 0 ELSE (Sum(Translines.Nett/1.1) - Sum(Translines.CostTotal)*100) / Sum(Translines.Nett/1.1)*10000 END as ProfitPer

FROM Transactions

INNER JOIN Translines WITH (NOLOCK)

ON Transactions.TransactionID = Translines.TransactionID AND Transactions.SHIFTID &amp;gt;= {SHIFTFROM}

INNER JOIN Store St WITH (NOLOCK)

ON Translines.StockStoreID = St.StoreID

AND Translines.StockStoreID LIKE '{STOREID}'

GROUP BY Transactions.OperatorID, Translines.ProductId

<UpdateAll>false</UpdateAll>

</cViews>

Other Parameter Settings:

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

or

<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>Product size count by operV3.5</sReportName><sSQL>Select

CASE WHEN Sum(Translines.Nett/1.1) = 0 THEN 0 ELSE (Sum(Translines.Nett/1.1) - Sum(Translines.CostTotal)100) / Sum(Translines.Nett/1.1)10000 END as ProfitPer

ON Transactions.TransactionID = Translines.TransactionID AND Transactions.SHIFTID >= {SHIFTFROM}