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

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics RMS (Archived) / Help Creating Specific...
Microsoft Dynamics RMS (Archived)

Help Creating Specific Custom Report

(0) ShareShare
ReportReport
Posted on by pawlybeans 63

I am running RMS Headquarters Manage 2.0.2, and I need to create a report that pulls information from two specific fields in the item cards, specifically fields in the Store Quantities tab "received" and "sold."

sections.JPG

I've some familiarity with creating custom reports, but I do not know what those fields are called within the custom report file format.

Using "Item.LastSold" or "Item.LastReceived"ends up pulling information from the Inventory tab, which is almost always incorrect and doesn't show which location.

*This post is locked for comments

I have the same question (0)
  • Kevin Antosh Profile Picture
    Kevin Antosh 1,649 on at

    Hi!  You can get a copy of the database schema via ParterSource or CustomerSource. This may also be helpful (but I can't validate whether it is accurate or not): www.google.com/url;source=web&cd=1&ved=2ahUKEwisv9P6n4ffAhWhgVQKHW-UCakQFjAAegQIAxAC&url=https%3A%2F%2Fstatic.austinhartzheim.me%2Ffiles%2Fdocs%2Fmicrosoft-rms%2Fmicrosoft-rms-database-structure.pdf&usg=AOvVaw23F5VUP2dM9FAhRNdGvRtA

  • pawlybeans Profile Picture
    pawlybeans 63 on at

    Seems that in order to access PartnerSource or CustomerSource I need to have service attached to my Microsoft account that I don't presently have access to. Also, Austin Hartzheim's pdf file is largely incomplete (likely since RMS is going to be discontinued and replaced soonish).

  • Kevin Antosh Profile Picture
    Kevin Antosh 1,649 on at

    You may want to check with your Microsoft Dynamics RMS Partner for the DB Schema - not sure what version of RMS you are on but they should have it for you.

  • pawlybeans Profile Picture
    pawlybeans 63 on at

    I might see if I can request it, so long as they don't consider it a service call. We should have had access to this without their help.

    New West Tech is who services our SQL server and RMS, but they've dropped the ball now and then and forced me to have to revisit things they themselves botched while doing a fix.

    We're running RMS Store Ops and Headquarters 2.0.2.

    So far, I found the "Committed" field exists in another report, so I hypothesized that the prefix in the QRP file would work for these, so I added the custom columns in an custom Item Movement Report:

    Begin Column

      FieldName = "Item.QuantityReceived"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Last Received"

      VBDataType = vbDate

      Formula = ""

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1020

      GroupMethod = groupmethodNone

      ColFormat = ""

    End Column

    Begin Column

      FieldName = "Item.QuantitySold"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "LastSold"

      VBDataType = vbDate

      Formula = ""

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1020

      GroupMethod = groupmethodNone

      ColFormat = ""

    End Column

    It gives me this error when I try to run the report:

    (-2147217900) The multi-part identifier "Item.QuantityReceived" could not be bound.

    It gives the same error when attempting to add the Item.Inactive field, which like the Item.QuantityCommitted field I'd based my own columns on, were pulled from the Item - Quantity Report.QRP.

    It seems rather silly that I can't cross reference specific fields that exist in other reports, especially since we had some custom reports that do just that from before I took over the inventory management position last year.

  • Kevin Antosh Profile Picture
    Kevin Antosh 1,649 on at

    Yeah - we've helped out quite a few of New Wests Customers recently with RMS/HQ support and deciding on what to replace RMS/HQ with come 2020/2021 - I've traveled to Seattle and Portland over 4 times in 2018 to meet onsite with many of these retailers. We have 11 support techs and also offer the various RMS/HQ low cost replacement programs in addition to supporting retailers still using RMS/HQ (whether it be onsite for service/consulting/sales work or just remote support/consulting/sales work). At any rate, can you email me at kevina@rite.us? I think that I found the DB schema and can email it to you as an Excel file. You can also call my cell at 208-994-9404 if easier.

  • pawlybeans Profile Picture
    pawlybeans 63 on at

    That helps with some of the field names. I'm still struggling to create some fields and columns that don't show in out-of-the-box reports. My buyer is wanting something simple, for example: a top items report that includes departments and store names as filters and fields (as is, top item reports show everything)- it can be a lot to go through without any sort of filters. In that case, I'm having difficulty getting department, category, and store name to be included, getting the error that it cannot be bound.

    I'm thinking my problems may lie in my lack of understanding of what the system does with information at the beginning of the QRP file before it gets to fields and columns. For example:

    Begin ReportSummary

      ReportType = reporttypeSales

      ReportTitle = "Top Departments Report"

      PageOrientation = pageorientationPortrait

      OutLineMode = True

      Groups = 0

      GroupDescription = ""

      DisplayLogo = True

      LogoFileName = "MyLogo.bmp"

      ProcedureCall = ""

      TablesQueried = <BEGIN>

         FROM        TransactionEntry WITH(NOLOCK)  

         INNER JOIN  [Transaction] WITH(NOLOCK)

                     ON TransactionEntry.TransactionNumber = [Transaction].TransactionNumber AND TransactionENtry.ItemType <> 9

                        AND TransactionEntry.StoreID = [Transaction].StoreID

         LEFT JOIN   Item WITH (NOLOCK)

                     ON TransactionEntry.ItemID = Item.ID

         LEFT JOIN   Department WITH(NOLOCK)

                     ON Item.DepartmentID = Department.ID

         LEFT JOIN   Store WITH(NOLOCK)

                     ON TransactionEntry.StoreID = Store.ID

    <END>

      SelCriteria = ""

      GroupBy = "Department.ID"

      SortOrder = "Sales DESC"

    End ReportSummary

  • Kevin Antosh Profile Picture
    Kevin Antosh 1,649 on at

    If easier for you - I could have one of our techs look at this and help you out or create the report for you. Best estimate is $125 to $250.  Just let me know - I'll email you information on our service rates, etc just in case. No obligation of course.

  • Suggested answer
    eliud mugo Profile Picture
    eliud mugo 1,444 on at

    The data you are looking for is in item dynamic table in HQ environment.item.lastsold and item.lastreceived dates will be different in different stores hence your data will be grouped per store.Send me a mail at eliudmugo11@yahoo.com with the columns you require.

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

Quick Links

Responsible AI policies

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

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November 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