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."
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.
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
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).
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.
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:
FieldName = "Item.QuantityReceived"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Last Received"
VBDataType = vbDate
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1020
GroupMethod = groupmethodNone
ColFormat = ""
FieldName = "Item.QuantitySold"
Title = "LastSold"
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.
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 email@example.com? 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.
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:
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
SelCriteria = ""
GroupBy = "Department.ID"
SortOrder = "Sales DESC"
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.
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 firstname.lastname@example.org with the columns you require.
Business Applications communities