quantity on hand needs to be included in item movement report

hi Jlg, you can do so by customizing the existing report of item movement. follow this

1. open the Items - Item Movement Report.qrp under C:\Program Files\Microsoft Retail Management System\Store Operations\Reports folder or whereever you installed the RMS
2. Update the preQuery2 view procedure by below: REATE 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, Item.Quantity as OnHand, 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 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, Item.Quantity as OnHand, 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

//-- see above i have add Item.Quantity field and name it as on hand --//

1. Go down and update the Groupby clause as:

GroupBy = "ViewItemMovement.ItemLookupCode, ViewItemMovement.LastSold, ViewItemMovement.LastReceived, ViewItemMovement.Cost,ViewItemMovement.onHand,ViewItemMovement.SupplierName, ViewItemMovement.DepartmentName, ViewItemMovement.CategoryName, ViewItemMovement.ItemDescription"

1. Go down to whole code and add this: Begin Column FieldName = "ViewItemMovementonHand" DrillDownFieldName = "" DrillDownReportName = "" Title = "On Hand" VBDataType = vbDouble Formula = "SUM(ViewItemMovement.OnHand)" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1800 GroupMethod = groupmethodSum ColFormat = "" End Column

1. save the report and run it , now it will show the qty on hand. Once work please rate me.

"jlg" wrote:

Akber

I would like to know more about the following fields in the QRP file:

ProcedureCall = >

PreQuery1 = ?? PreQuery2 = ?? TablesQueried What do they do... I want to create my own QRP reports -

Danny

hi Danny, I have noted ur email address, and sending you the customization guide, this will help you in detail for every aspect of RMS and you will really like it.

Remember rate me once you get.

"Danny" wrote:

soft.public.pos