Share
ReportI am in need of a item movement report with items committed column. Unless there is a better report to find committed items received that day. Thanks!
*This post is locked for comments
You’re offline. This is a read only version of the page.
I am in need of a item movement report with items committed column. Unless there is a better report to find committed items received that day. Thanks!
*This post is locked for comments
This is what I'm working with. Where exactly should Item.QuantiyCommitted go?
//--- Report Summary --- //
Begin ReportSummary
ReportType = reporttypeItems
ReportTitle = "ITEM MOVEMENT"
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.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
WHERE Item.ItemType <> 9
UNION ALL
SELECT Department.Name as DepartmentName,
Category.Name as CategoryName,
Supplier.SupplierName as SupplierName,
Item.ItemLookupCode AS ItemLookupCode,
Item.Description AS ItemDescription,
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
WHERE Item.ItemType <> 9
<END>
TablesQueried = "FROM ViewItemMovement"
SelCriteria = ""
GroupBy = "ViewItemMovement.ItemLookupCode, ViewItemMovement.LastSold, ViewItemMovement.LastReceived, ViewItemMovement.Cost,ViewItemMovement.SupplierName, ViewItemMovement.DepartmentName, ViewItemMovement.CategoryName, ViewItemMovement.ItemDescription"
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.DateTransferred"
FilterOp = reportfilteropGreaterEqual
FilterLoLim = "12/1/2015"
FilterHilim = "12/1/2015"
FilterNegated = False
FilterConnector = reportfilterbooleanconAND
End Filter
Begin Filter
FieldName = "PurchaseOrder"
FilterOp = reportfilteropGreater
FilterLoLim = "0"
FilterHilim = "0"
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 = 1980
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 = 2985
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 = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1335
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "QtySold"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Quantity Sold"
VBDataType = vbDouble
Formula = "SUM(ViewItemMovement.QuantitySold)"
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1200
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "Sales"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Sales"
VBDataType = vbCurrency
Formula = "SUM(ViewItemMovement.QuantitySold * ViewItemMovement.PriceSold)"
ColHidden = True
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 = 975
GroupMethod = groupmethodMax
ColFormat = ""
End Column
Begin Column
FieldName = "ViewItemMovement.Cost"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Cost"
VBDataType = vbCurrency
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1095
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "CostSold"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Cost Sold"
VBDataType = vbCurrency
Formula = "SUM(ViewItemMovement.QuantitySold * ViewItemMovement.CostSold)"
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1095
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "ViewItemMovementQuantity"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Non Sale Movement"
VBDataType = vbDouble
Formula = "SUM(ViewItemMovement.Quantity)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1815
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 = "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 = "ViewItemMovement.SupplierName"
DrillDownFieldName = "Supplier.SupplierName"
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Supplier"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2160
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
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 = "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 = 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
Hi,
You can add Item.QuantityCommitted to the active report if this is what you need.
#1
André Arnaud de Cal...
292,494
Super User 2025 Season 1
#2
Martin Dráb
231,309
Most Valuable Professional
#3
nmaenpaa
101,156
All responses (
Answers (
