SBX - Search With Button

SBX - Forum Post Title

Help Creating Specific Custom Report

Microsoft Dynamics RMS Forum

pawlybeans asked a question on 4 Dec 2018 1:07 PM

Question Status

Suggested Answer

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.

Reply
Kevin Antosh responded on 4 Dec 2018 4:47 PM
My Badges

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

Reply
pawlybeans responded on 5 Dec 2018 9:48 AM

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).

Reply
Kevin Antosh responded on 5 Dec 2018 10:10 AM
My Badges

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.

Reply
pawlybeans responded on 5 Dec 2018 11:25 AM

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.

Reply
Kevin Antosh responded on 5 Dec 2018 6:23 PM
My Badges

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.

Reply
pawlybeans responded on 6 Dec 2018 10:22 AM

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

Reply
Kevin Antosh responded on 7 Dec 2018 8:40 AM
My Badges

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.

Reply
Eliud Mugo responded on 18 Dec 2018 7:40 AM
My Badges
Suggested Answer

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.

Reply
Eliud Mugo responded on 18 Dec 2018 7:40 AM
My Badges
Suggested Answer

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.

Reply

SBX - Two Col Forum

SBX - Migrated JS