Share
ReportDoes anyone know how to modify a custom report (Item Movement History) to include the Supplier Reorder Number and the Quantity on Hand?
*This post is locked for comments
You’re offline. This is a read only version of the page.
Does anyone know how to modify a custom report (Item Movement History) to include the Supplier Reorder Number and the Quantity on Hand?
*This post is locked for comments
I just modified it and it says, "Error reading (report name).qrp at line #27 "View Item Movement."
Hmmm?
I'm sorry...I mislabeled one of the field. try the following.
//--- Report Summary --- //
Begin ReportSummary
ReportType = reporttypeItems
ReportTitle = "Item Movement Report"
PageOrientation = pageorientationLandscape
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.Quantity as QOH,
SupplierList.ReorderNumber,
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.id = SupplierList.itemid and Item.SupplierId = SupplierList.Supplierid
UNION ALL
SELECT Department.Name as DepartmentName,
Category.Name as CategoryName,
Supplier.SupplierName as SupplierName,
Item.ItemLookupCode AS ItemLookupCode,
Item.Description AS ItemDescription,
Item.Quantity as QOH,
SupplierList.ReorderNumber,
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.id = SupplierList.itemid and Item.SupplierId = SupplierList.Supplierid
<END>
TablesQueried = "FROM ViewItemMovement"
SelCriteria = ""
GroupBy = "ViewItemMovement.ItemLookupCode, ViewItemMovement.LastSold, ViewItemMovement.LastReceived,
ViewItemMovement.Cost,ViewItemMovement.SupplierName, ViewItemMovement.DepartmentName, ViewItemMovement.CategoryName,
ViewItemMovement.ItemDescription, ViewItemMovement.Quantity, ViewItemMovement.ReorderNumber, ViewItemMovement.QOH"
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 = "<MonthStart>"
FilterHilim = "<MonthStart>"
End Filter
//--- Columns ---//
Begin Column
FieldName = "ViewItemMovement.ItemLookupCode"
DrillDownFieldName = "Item.ItemLookupCode"
DrillDownReportName = ""
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.DepartmentName"
DrillDownFieldName = "Department.Name"
DrillDownReportName = ""
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 = ""
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 = ""
Title = "Supplier"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2160
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Begin Column
FieldName = "ViewItemMovement.ItemDescription"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Description"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2160
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Begin Column
FieldName = "ViewItemMovement.QOH"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Quantity On Hand"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2160
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Begin Column
FieldName = "ViewItemMovement.ReorderNumber"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Supplier Reorder Number"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2160
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Begin Column
FieldName = "QtySold"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Quantity Sold"
VBDataType = vbDouble
Formula = "SUM(ViewItemMovement.QuantitySold)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1200
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "Sales"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Sales"
VBDataType = vbCurrency
Formula = "SUM(ViewItemMovement.QuantitySold * ViewItemMovement.PriceSold)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1100
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "ViewItemMovement.LastSold"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Last Sold"
VBDataType = vbDate
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1100
GroupMethod = groupmethodMax
ColFormat = ""
End Column
Begin Column
FieldName = "ViewItemMovement.Cost"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Cost"
VBDataType = vbCurrency
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1100
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "CostSold"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Cost Sold"
VBDataType = vbCurrency
Formula = "SUM(ViewItemMovement.QuantitySold * ViewItemMovement.CostSold)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1100
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "ViewItemMovementQuantity"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Non Sale Movement"
VBDataType = vbDouble
Formula = "SUM(ViewItemMovement.Quantity)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1800
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "ViewItemMovement.LastReceived"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Last Received"
VBDataType = vbDate
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1400
GroupMethod = groupmethodMax
ColFormat = ""
End Column
Begin Column
FieldName = "PurchaseOrder"
DrillDownFieldName = ""
DrillDownReportName = ""
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 = ""
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 = ""
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 = ""
Title = "Date"
VBDataType = vbDate
Formula = ""
ColHidden = True
ColNotDisplayable = True
FilterDisabled = False
ColWidth = 1100
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "ViewItemMovement.Moved"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Moved"
VBDataType = vbBoolean
Formula = ""
ColHidden = True
ColNotDisplayable = True
FilterDisabled = False
ColWidth = 1100
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Brian,
Ahhhhh! Soooo close! The report loaded and ALL the information is there - perfectly- with one glitch:
The quantity on hand is "0" for all of the items, yet most of them have a quantity on hand that is greater than zero. You've been so helpful. Not sure what to do now. Any ideas?
Gratefully,
Lisa
Below is the entire content of the file. Hopefully this will work.
//--- Report Summary --- //
Begin ReportSummary
ReportType = reporttypeItems
ReportTitle = "Item Movement Report"
PageOrientation = pageorientationLandscape
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.Quantity as QOH,
SupplierList.ReorderNumber,
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.id = SupplierList.itemid and Item.SupplierId = SupplierList.Supplierid
UNION ALL
SELECT Department.Name as DepartmentName,
Category.Name as CategoryName,
Supplier.SupplierName as SupplierName,
Item.ItemLookupCode AS ItemLookupCode,
Item.Description AS ItemDescription,
Item.Quantity as QOH,
SupplierList.ReorderNumber,
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.id = SupplierList.itemid and Item.SupplierId = SupplierList.Supplierid
<END>
TablesQueried = "FROM ViewItemMovement"
SelCriteria = ""
GroupBy = "ViewItemMovement.ItemLookupCode, ViewItemMovement.LastSold, ViewItemMovement.LastReceived, ViewItemMovement.Cost,ViewItemMovement.SupplierName, ViewItemMovement.DepartmentName, ViewItemMovement.CategoryName, ViewItemMovement.ItemDescription, ViewItemMovement.Quantity, ViewItemMovement.ReorderNumber"
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 = "<MonthStart>"
FilterHilim = "<MonthStart>"
End Filter
//--- Columns ---//
Begin Column
FieldName = "ViewItemMovement.ItemLookupCode"
DrillDownFieldName = "Item.ItemLookupCode"
DrillDownReportName = ""
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.DepartmentName"
DrillDownFieldName = "Department.Name"
DrillDownReportName = ""
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 = ""
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 = ""
Title = "Supplier"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2160
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Begin Column
FieldName = "ViewItemMovement.ItemDescription"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Description"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2160
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Begin Column
FieldName = "ViewItemMovement.Quantity"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Quantity On Hand"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2160
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Begin Column
FieldName = "ViewItemMovement.ReorderNumber"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Supplier Reorder Number"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2160
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Begin Column
FieldName = "QtySold"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Quantity Sold"
VBDataType = vbDouble
Formula = "SUM(ViewItemMovement.QuantitySold)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1200
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "Sales"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Sales"
VBDataType = vbCurrency
Formula = "SUM(ViewItemMovement.QuantitySold * ViewItemMovement.PriceSold)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1100
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "ViewItemMovement.LastSold"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Last Sold"
VBDataType = vbDate
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1100
GroupMethod = groupmethodMax
ColFormat = ""
End Column
Begin Column
FieldName = "ViewItemMovement.Cost"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Cost"
VBDataType = vbCurrency
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1100
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "CostSold"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Cost Sold"
VBDataType = vbCurrency
Formula = "SUM(ViewItemMovement.QuantitySold * ViewItemMovement.CostSold)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1100
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "ViewItemMovementQuantity"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Non Sale Movement"
VBDataType = vbDouble
Formula = "SUM(ViewItemMovement.Quantity)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1800
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "ViewItemMovement.LastReceived"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Last Received"
VBDataType = vbDate
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1400
GroupMethod = groupmethodMax
ColFormat = ""
End Column
Begin Column
FieldName = "PurchaseOrder"
DrillDownFieldName = ""
DrillDownReportName = ""
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 = ""
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 = ""
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 = ""
Title = "Date"
VBDataType = vbDate
Formula = ""
ColHidden = True
ColNotDisplayable = True
FilterDisabled = False
ColWidth = 1100
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "ViewItemMovement.Moved"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Moved"
VBDataType = vbBoolean
Formula = ""
ColHidden = True
ColNotDisplayable = True
FilterDisabled = False
ColWidth = 1100
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Hi Brian,
I finally had a chance to try your suggestion. So close! I get an error message:
"Invalid column name, 'ReorderNumber.' Just to clarify: I am modifying the Item Movement report and NOT the Item History Movement report as I previously wrote. Any suggestions? I'd really appreciate it!!!
Lisa
Lisa,
At the bottom of the query section, there is a tag labled GroupBy ="......" If you add the two new columns to this section you should be good to go. This will then read:
GroupBy = "ViewItemMovement.ItemLookupCode, ViewItemMovement.LastSold, ViewItemMovement.LastReceived, ViewItemMovement.Cost,ViewItemMovement.SupplierName,
ViewItemMovement.DepartmentName, ViewItemMovement.CategoryName, ViewItemMovement.ItemDescription, ViewItemMovement.Quantity, ViewItemMovement.ReorderNumber"
Hope this helps.
Brian
Brian,
Thank you so much for your reply. I appreciate it very much. I made a mistake. I need to add the reorder number and the QOH to the Item Movement report (not History). So, I input the same information into a copy of the Item Movement report, but now I no longer can filter the report by Supplier. I need to be able to do that, too. I'd sure be grateful for any input.
Best,
Lisa@Jones
Jones,
Assuming you have not modified a report before, here are my suggestions on how to accomplish this:
1 - Locate the original report file and place a copy of it some place as a back up. The report files are located here:
Program Files\Microsoft Retail Management System\Store Operations\Reports
and the report file is "Items - Item Movement History Report.qrp"
2 - Open the file using Notepad.
3 - For this report there are actually 2 queries in it so you will need to perform the following steps (4, 5 and 6) twice.
4- To get the Quantity On Hand is rather straight forward since this is on the Item table which is already included in the query. You will want to add the following line to both of the queries:
Item.Quantity as Quantity,
For me it looked to make sense after the SerialNumber3 and Before CashierName
Serial.SerialNumber3 AS SerialNumber3,
Item.Quantity AS QuantityOH,
Cashier.Name AS CashierName,
5 - Reorder Number requires that you add another table to the queries: The following line should be added to both at the end
LEFT JOIN SupplierList on Item.id = SupplierList.itemid and Item.SupplierId = SupplierList.Supplierid
6 - Now that you have The Supplier List you can add the ReorderNumber. Copy the following into the query just like you did for Quantity.
SupplierList.ReorderNumber,
So when you are done the begining of each of your queries will look like this:
SELECT Department.Name as DepartmentName,
Category.Name as CategoryName,
Item.ItemLookupCode AS ItemLookupCode,
Item.Description AS ItemDescription,
Serial.SerialNumber AS SerialNumber,
Serial.SerialNumber2 AS SerialNumber2,
Serial.SerialNumber3 AS SerialNumber3,
Item.Quantity as QuantityOH,
SupplierList.ReorderNumber,
Cashier.Name AS CashierName,
ETC, ETC
Now that you have the columns in your data, you next need to add the columns to the report. A little further down you will see the section labeled //--- Columns ---//.
Paste the following into the report between what ever columns you would like the data to be:
Begin Column
FieldName = "ViewItemMovementHistory.QuantityOH"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Quantity On Hand"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2160
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Begin Column
FieldName = "ViewItemMovementHistory.ReorderNumber"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Supplier Reorder Number"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2160
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Now Save the Report. same location as before, but I would suggest that you Save it as:
Custom - Item Movement History Report.qrp
This will place it under the Reports \ Custom when you try to run it in Store Operations Manager
Hope this helps
#1
André Arnaud de Cal...
291,431
Super User 2024 Season 2
#2
Martin Dráb
230,503
Most Valuable Professional
#3
nmaenpaa
101,156
All responses (
Answers (
