Try this... I set the Qty Sold to <1
//--- Report Summary --- //
Begin ReportSummary
ReportType = reporttypeItems
ReportTitle = "Item Movement - Dead Stock"
PageOrientation = pageorientationLandscape
WordWrap = False
ShowDateTimePicker = False
OutLineMode = True
Groups = 0
GroupDescription = ""
DisplayLogo = True
LogoFileName = "MyLogo.bmp"
ProcedureCall = ""
PreQuery1 = "IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'ViewItemMovement') DROP VIEW ViewItemMovement"
PreQuery2 = <BEGIN>
CREATE VIEW ViewItemMovement AS
SELECT Department.Name as DepartmentName,
Category.Name as CategoryName,
Supplier.SupplierName as SupplierName,
Item.ItemLookupCode AS ItemLookupCode,
Item.Description AS ItemDescription,
Item.Price AS ItemPrice,
Item.Quantity AS OnHand,
Item.LastSold as LastSold,
Item.LastReceived as LastReceived,
Item.Cost as Cost,
InventoryTransferLog.Type AS Type,
0 as QuantitySold,
ISNULL (InventoryTransferLog.Quantity, 0) AS Quantity,
0 as PriceSold,
0 as CostSold,
InventoryTransferLog.DateTransferred AS DateTransferred,
1 AS Moved,
CASE InventoryTransferlog.Type WHEN 2 THEN InventoryTransferLog.ReferenceID ELSE NULL END AS TransactionNumber
FROM InventoryTransferLog
LEFT JOIN Item ON InventoryTransferLog.ItemID = Item.ID
LEFT JOIN Department ON Item.DepartmentID = Department.ID
LEFT JOIN Category ON Item.CategoryID = Category.ID
LEFT JOIN Supplier ON Item.SupplierID = Supplier.ID
LEFT JOIN SupplierList ON Item.SupplierID = SupplierList.SupplierID AND Item.ID = SupplierList.ItemID
UNION ALL
SELECT Department.Name as DepartmentName,
Category.Name as CategoryName,
Supplier.SupplierName as SupplierName,
Item.ItemLookupCode AS ItemLookupCode,
Item.Description AS ItemDescription,
Item.Price AS ItemPrice,
Item.Quantity AS OnHand,
Item.LastSold AS LastSold,
Item.LastReceived as LastReceived,
Item.Cost AS Cost,
99 AS Type,
ISNULL (TransactionEntry.Quantity, 0) AS QuantitySold,
0 as QuantityTransferred,
ISNULL (TransactionEntry.Price, 0) as PriceSold,
ISNULL (TransactionEntry.Cost, 0) as CostSold,
[Transaction].Time AS DateTransferred,
CASE WHEN [Transaction].Time IS NULL THEN 0 ELSE 1 END AS Moved,
[Transaction].TransactionNumber AS TransactionNumber
FROM Item
LEFT JOIN TransactionEntry ON TransactionEntry.ItemID = Item.ID
LEFT JOIN Department ON Item.DepartmentID = Department.ID LEFT JOIN Category ON Item.CategoryID = Category.ID
LEFT JOIN [Transaction] ON TransactionEntry.TransactionNumber = [Transaction].TransactionNumber
LEFT JOIN Supplier ON Item.SupplierID = Supplier.ID
LEFT JOIN SupplierList ON Item.SupplierID = SupplierList.SupplierID AND Item.ID = SupplierList.ItemID
<END>
TablesQueried = "FROM ViewItemMovement LEFT JOIN Supplier ON ViewItemMovement.SupplierName = Supplier.SupplierName"
SelCriteria = ""
GroupBy = "ViewItemMovement.ItemLookupCode, ViewItemMovement.LastSold, ViewItemMovement.LastReceived, ViewItemMovement.Cost,ViewitemMovement.Itemprice, Supplier.SupplierName, ViewItemMovement.DepartmentName, ViewItemMovement.CategoryName, ViewItemMovement.ItemDescription, ViewItemMovement.OnHand"
SortOrder = ""
End ReportSummary
//--- Title Rows ---//
Begin TitleRow
Text = "<Store Name>"
Font = "Arial"
FontBold = True
FontSize = 16
Color = "Blue"
End TitleRow
Begin TitleRow
Text = "<Report Title>"
Font = "Arial"
FontBold = True
FontSize = 12
Color = "Black"
End TitleRow
Begin TitleRow
Text = "Generated On <Report Date>"
Font = "Arial"
FontBold = True
FontSize = 10
Color = "Black"
End TitleRow
//--- Filters ---//
Begin Filter
FieldName = "ViewItemMovement.CategoryName"
FilterOp = reportfilteropEqual
FilterLoLim = "MULTIQUIP PARTS"
FilterHilim = "MULTIQUIP PARTS"
FilterNegated = False
FilterConnector = reportfilterbooleanconAND
End Filter
Begin Filter
FieldName = "ViewItemMovement.OnHand"
FilterOp = reportfilteropGreater
FilterLoLim = "0"
FilterHilim = "0"
FilterNegated = False
FilterConnector = reportfilterbooleanconAND
End Filter
Begin Filter
FieldName = "QtySold"
FilterOp = reportfilteropLess
FilterLoLim = "2"
FilterHilim = "2"
FilterNegated = False
FilterConnector = reportfilterbooleanconAND
End Filter
//--- Columns ---//
Begin Column
FieldName = "ViewItemMovement.ItemLookupCode"
DrillDownFieldName = "Item.ItemLookupCode"
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Item Lookup Code"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2160
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Begin Column
FieldName = "ViewItemMovement.ItemDescription"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Description"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2160
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Begin Column
FieldName = "ViewItemMovement.OnHand"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "On Hand"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 855
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Begin Column
FieldName = "QtySold"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Quantity Sold"
VBDataType = vbDouble
Formula = "SUM(ViewItemMovement.QuantitySold)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1260
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "Sales"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Sales"
VBDataType = vbCurrency
Formula = "SUM(ViewItemMovement.QuantitySold * ViewItemMovement.PriceSold)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1095
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "ViewItemMovement.LastSold"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Last Sold"
VBDataType = vbDate
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1095
GroupMethod = groupmethodMax
ColFormat = ""
End Column
Begin Column
FieldName = "ViewItemMovement.Cost"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Cost"
VBDataType = vbCurrency
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 765
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "ViewItemMovement.Itemprice"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Price"
VBDataType = vbCurrency
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 900
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "CostSold"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Cost Sold"
VBDataType = vbCurrency
Formula = "SUM(ViewItemMovement.QuantitySold * ViewItemMovement.CostSold)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 975
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "ViewItemMovement.LastReceived"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Last Received"
VBDataType = vbDate
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1335
GroupMethod = groupmethodMax
ColFormat = ""
End Column
Begin Column
FieldName = "Adjusted"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Adjusted"
VBDataType = vbDouble
Formula = "SUM(CASE WHEN Type = 5 OR Type = 6 THEN ISNULL(Quantity, 0) ELSE 0 END) "
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 930
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "Supplier.SupplierName"
DrillDownFieldName = "Supplier.SupplierName"
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Supplier Name"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1935
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Begin Column
FieldName = "ViewItemMovementQuantity"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Non Sale Movement"
VBDataType = vbDouble
Formula = "SUM(ViewItemMovement.Quantity)"
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1800
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "ViewItemMovement.DepartmentName"
DrillDownFieldName = "Department.Name"
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Department"
VBDataType = vbString
Formula = "ViewItemMovement.DepartmentName"
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2160
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Begin Column
FieldName = "ViewItemMovement.CategoryName"
DrillDownFieldName = "Category.Name"
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Category"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2160
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Begin Column
FieldName = "PurchaseOrder"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "PO Movement"
VBDataType = vbDouble
Formula = "SUM(CASE WHEN Type = 1 THEN ISNULL(Quantity, 0) ELSE 0 END) "
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1710
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "OfflineMovement"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Offline Movement"
VBDataType = vbDouble
Formula = "SUM(CASE WHEN Type = 2 OR Type = 3 OR Type = 4 THEN ISNULL(Quantity, 0) ELSE 0 END) "
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1710
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "ViewItemMovement.DateTransferred"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Date"
VBDataType = vbDate
Formula = ""
ColHidden = True
ColNotDisplayable = True
FilterDisabled = False
ColWidth = 1095
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "ViewItemMovement.Moved"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Moved"
VBDataType = vbBoolean
Formula = ""
ColHidden = True
ColNotDisplayable = True
FilterDisabled = False
ColWidth = 1095
GroupMethod = groupmethodNone
ColFormat = ""
End Column
