web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics RMS (Archived) / RMS Inventory Report?
Microsoft Dynamics RMS (Archived)

RMS Inventory Report?

(0) ShareShare
ReportReport
Posted on by Community Member

Hello all,

     I am looking to create a report to tell me how many of each of my inventory items I ordered, how many I have sold, and how many I have left for a specific date range, for example from the beginning of the year to the current date. So far I have been unable to figure out how to do this. I thought it might require just some tweaking of the Item Movement or Movement History report but that does not appear to be the case. The report should contain the following information:

Item Description

Extended (English) Description

Size

Price

Supplier

Quantity on Hand

Quantity Sold (from-to specific dates)

Quantity Ordered (from-to specific dates)

Am I going to have to go outside of RMS, say to Server Management Studio, and build a query? Is the requested information even all contained anywhere in the structures? Any assistance I can get is greatly appreciated. Thank you all in advance.

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    eliud mugo Profile Picture
    eliud mugo 1,444 on at
    RE: RMS Inventory Report?

    Check this out,it might be of help.Works at the store level only.

    //--- Report Summary --- //

    Begin ReportSummary

      ReportType = reporttypeItems

      ReportTitle = "Inventory 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.subDescription2 AS ItemsubDescription2,

                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 WHEN POType IN (0,1) THEN ISNULL(InventoryTransferLog.Quantity, 0) ELSE 0 END AS POMove,

                CASE WHEN POType IN (2,4) THEN ISNULL(InventoryTransferLog.Quantity, 0) ELSE 0 END AS InMove,

                CASE WHEN POType IN (3,5) THEN ISNULL(InventoryTransferLog.Quantity, 0) ELSE 0 END AS OutMove,

                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 PurchaseOrder ON InventoryTransferLog.ReferenceID = PurchaseOrder.ID  AND InventoryTransferLog.Type = 1

         UNION ALL          

         SELECT Department.Name as DepartmentName,

              Category.Name as CategoryName,

              Supplier.SupplierName as SupplierName,

              Item.ItemLookupCode AS ItemLookupCode,

                Item.Description AS ItemDescription,

                Item.subDescription2 AS ItemsubDescription2,

                 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,

        0 AS POMove,

        0 AS InMove,

        0 AS OutMove,

                [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

    <END>

      TablesQueried = "FROM ViewItemMovement"

      SelCriteria = ""

      GroupBy = "ViewItemMovement.ItemLookupCode, ViewItemMovement.LastSold, ViewItemMovement.LastReceived, ViewItemMovement.Cost,ViewItemMovement.SupplierName, ViewItemMovement.DepartmentName, ViewItemMovement.CategoryName, ViewItemMovement.ItemDescription, ViewItemMovement.ItemsubDescription2, 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 = 16

      Color = "Black"

    End TitleRow

    Begin TitleRow

      Text = ""

      Font = "Arial"

      FontBold = True

      FontSize = 9

      Color = "Red"

    End TitleRow

    Begin TitleRow

      Text = "Generated On <Report Date>"

      Font = "Arial"

      FontBold = False

      FontSize = 8

      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.ItemsubDescription2"

      DrillDownFieldName = "Item.subDescription2"

      DrillDownReportName = ""

      Title = "SUB Description2"

      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 = ""

      Title = "On Hand"

      VBDataType = vbString

      Formula = ""

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1000

      GroupMethod = groupmethodNone

      ColFormat = ""

      ColAlignment = flexAlignRightCenter

    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 = True

      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 = True

      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 = True

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1400

      GroupMethod = groupmethodMax

      ColFormat = ""

    End Column

    Begin Column

      FieldName = "PurchaseOrder"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "PO Movement"

      VBDataType = vbDouble

      Formula = "SUM(ISNULL(POMove, 0))"

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1400

      GroupMethod = groupmethodSum

      ColFormat = ""

    End Column

    Begin Column

      FieldName = "PurchaseOrder2"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Xfr In Movement"

      VBDataType = vbDouble

      Formula = "SUM(ISNULL(InMove, 0))"

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1500

      GroupMethod = groupmethodSum

      ColFormat = ""

    End Column

    Begin Column

      FieldName = "PurchaseOrder3"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Xfr Out Movement"

      VBDataType = vbDouble

      Formula = "SUM(ISNULL(OutMove, 0))"

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1600

      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

  • Community Member Profile Picture
    Community Member on at
    RE: RMS Inventory Report?

    Actually I guess the committed and backordered would be calculated in the sold total. I'm not sure.

  • Community Member Profile Picture
    Community Member on at
    RE: RMS Inventory Report?

    Hi Ed and thanks for your reply. I think what we're looking to do here is to scan through the database and find a) a total of how many of each item have been ordered in a given date range, b) a total of how many of each item have been sold in a given date range, and c) how many of each item remain. I don't think we need to be concerned with quantities on a given day, other than how many of each item are actually available on the day the report is run. For total available I can see taking committed and backordered values and deducting those from quantity on hand, but as to how to physically get the system to do that, or how to calculate the other desired totals, is beyond my level of expertise. So you have any thoughts given this information? Am I missing something in my thought process? Thanks again for your help.

  • Community Member Profile Picture
    Community Member on at
    RE: RMS Inventory Report?

    You might need to at least start with creating the query in SQL Management Studio.  This is where I would start.   The real tricky part is the you need to know the onhand quantity given a date.  You will have to back out all sales, transfers, and other adjustments back to that date.  Then use that starting inventory on that date to calculate the QOH.  So the tables involved is [transactionentry], [item], [orderentry] (for work orders), [physicalinventory] and [purchaseorderentry].

    The quantity ordered would come from the [purchaseorderentry] table.

    The Quantity sold comes from the [transactionentry] table.

    Also,  I think this has been done before.  Try searching the forum for inventory report.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Dynamics 365 Community Calls Return
Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft
Responsible AI policies for the Community

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Andrés Arias – Community Spotlight

We are honored to recognize Andrés Arias as our Community Spotlight honoree for…

Congratulations to the August Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics RMS (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans