Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics RMS (Archived) / HQ Report to reference...
Microsoft Dynamics RMS (Archived)

HQ Report to reference Item Dynamics table for Last Sold

(0) ShareShare
ReportReport
Posted on by DEVON LEROUX 1,565

Hi, I'm looking for the script to add the field "last sold" to a report in HQ. From what I understand this would need to reference the Item Dynamics table for each store.

Here is the report we are starting with:

 

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

Begin ReportSummary
   ReportType = reporttypeItems
   ReportTitle = "HQ REORDER/RESTOCK"
   PageOrientation = pageorientationLandscape
   OutLineMode = True
   Groups = 1
   GroupDescription = "Total"
   DisplayLogo = True
   LogoFileName = "MyLogo.bmp"
   ProcedureCall = ""
   TablesQueried = <BEGIN>
  
      FROM       Item WITH(NOLOCK)
      INNER JOIN Department WITH(NOLOCK) ON Item.DepartmentID=Department.ID
      INNER JOIN Category WITH(NOLOCK) ON Item.CategoryID=Category.ID
      LEFT JOIN  Supplier WITH(NOLOCK) ON Item.SupplierID = Supplier.ID 
      LEFT JOIN  ItemDynamic AS S1 WITH(NOLOCK) ON S1.ItemID = Item.ID AND S1.StoreID=1
      LEFT JOIN  ItemDynamic AS S2 WITH(NOLOCK) ON S2.ItemID = Item.ID AND S2.StoreID=2
      LEFT JOIN  ItemDynamic AS S3 WITH(NOLOCK) ON S3.ItemID = Item.ID AND S3.StoreID=3
      LEFT JOIN  ItemDynamic AS S4 WITH(NOLOCK) ON S4.ItemID = Item.ID AND S4.StoreID=4
      LEFT JOIN  ItemDynamic AS S5 WITH(NOLOCK) ON S5.ItemID = Item.ID AND S5.StoreID=5
      LEFT JOIN  ItemDynamic AS S6 WITH(NOLOCK) ON S6.ItemID = Item.ID AND S6.StoreID=6
      LEFT JOIN  ItemDynamic AS S7 WITH(NOLOCK) ON S7.ItemID = Item.ID AND S7.StoreID=7
      LEFT JOIN  ItemDynamic AS S8 WITH(NOLOCK) ON S8.ItemID = Item.ID AND S8.StoreID=8
      LEFT JOIN  ItemDynamic AS S10 WITH(NOLOCK) ON S10.ItemID = Item.ID AND S10.StoreID=10

<END>
   SelCriteria = ""
   GroupBy = ""
   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 = 12
   Color = "Black"
End TitleRow

Begin TitleRow
   Text = "As Of: <Report Date>"
   Font = "Arial"
   FontBold = True
   FontSize = 10
   Color = "Black"
End TitleRow


//--- Filters ---//


//--- Columns ---//

Begin Column
   FieldName = "Department.Name"
   DrillDownFieldName = "Department.Name"
   DrillDownReportName = ""
   Title = "Dept. Name"
   VBDataType = vbString
   Formula = ""
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 2895
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "Category.Name"
   DrillDownFieldName = "Category.Name"
   DrillDownReportName = ""
   Title = "Cat. Name"
   VBDataType = vbString
   Formula = ""
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 2820
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "Supplier.SupplierName"
   DrillDownFieldName = "Supplier.SupplierName"
   DrillDownReportName = ""
   Title = "Supplier"
   VBDataType = vbString
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 825
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "Item.ItemLookUpCode"
   DrillDownFieldName = "Item.ItemLookUpCode"
   DrillDownReportName = ""
   Title = "LookUp Code"
   VBDataType = vbString
   Formula = ""
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1245
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "Item.Description"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Description"
   VBDataType = vbString
   Formula = ""
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1600
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column


Begin Column
   FieldName = "Item.Price"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Price"
   VBDataType = vbCurrency
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 765
   GroupMethod = groupmethodAverage
   ColFormat = ""
End Column


Begin Column
   FieldName = "Item.Quantity"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "On-Hand"
   VBDataType = vbDouble
   Formula = ""
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1080
   GroupMethod = groupmethodSum
   ColFormat = "#.##"
End Column

Begin Column
   FieldName = "S1.SnapShotTime"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "South SnapShot Date"
   VBDataType = vbDate
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1395
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column


Begin Column
   FieldName = "S1.SnapShotQuantity"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "South Quantity"
   VBDataType = vbDouble
   Formula = ""
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1000
   GroupMethod = groupmethodSum
   ColFormat = "#.##"
End Column


Begin Column
   FieldName = "S1.SnapShotQuantityCommitted"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "South Committed"
   VBDataType = vbDouble
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1200
   GroupMethod = groupmethodSum
   ColFormat = "#.##"
End Column


Begin Column
   FieldName = "S1.SnapShotReorderPoint"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "South Reorder"
   VBDataType = vbDouble
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1100
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column


Begin Column
   FieldName = "S1.SnapShotRestockLevel"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "South Restock"
   VBDataType = vbDouble
   Formula = ""
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1100
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column


Begin Column
   FieldName = "S2.SnapShotTime"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "West SnapShot Date"
   VBDataType = vbDate
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1395
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column


Begin Column
   FieldName = "S2.SnapShotQuantity"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "West Quantity"
   VBDataType = vbDouble
   Formula = ""
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1000
   GroupMethod = groupmethodSum
   ColFormat = "#.##"
End Column


Begin Column
   FieldName = "S2.SnapShotQuantityCommitted"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "West Committed"
   VBDataType = vbDouble
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1200
   GroupMethod = groupmethodSum
   ColFormat = "#.##"
End Column


Begin Column
   FieldName = "S2.SnapShotReorderPoint"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "West Reorder"
   VBDataType = vbDouble
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1100
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column


Begin Column
   FieldName = "S2.SnapShotRestockLevel"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "West Restock"
   VBDataType = vbDouble
   Formula = ""
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1100
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column


Begin Column
   FieldName = "S3.SnapShotQuantity"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Mikes Quantity"
   VBDataType = vbDouble
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1000
   GroupMethod = groupmethodSum
   ColFormat = "#.##"
End Column

This continues on for all 9 of our "stores" (two are stores, seven are small "warehouses" for backup stock)

 

Thanks!
Devon

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: HQ Report to reference Item Dynamics table for Last Sold

    Hello Matt,

    How to handle the NULL values in the table?

    Thanks & Regards,

    Upen

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: HQ Report to reference Item Dynamics table for Last Sold

    You could compare to a Detailed Sales Report and copy the pieces of each report that you want, but that is a different type of report and would start listing results vertically instead of side-by-side, and might miss items that don't get sold.  I'm sure it could be done, but you probably should keep those reports separate.

  • DEVON LEROUX Profile Picture
    DEVON LEROUX 1,565 on at
    Re: HQ Report to reference Item Dynamics table for Last Sold

    Worked great, thanks Matt!

    Is there a way to add QTY Sold year to date for this same report?

    Ideally it would be QTY Sold from all stores in one column.

    Thanks,

    Devon

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: HQ Report to reference Item Dynamics table for Last Sold

    Your column definition would look something like:

    Begin Column
       FieldName = "S1.LastSold"
       DrillDownFieldName = ""
       DrillDownReportName = ""
       StoreIDFieldName = ""
       Title = "South LastSold"
       VBDataType = vbDate
       Formula = ""
       ColHidden = False
       ColNotDisplayable = False
       FilterDisabled = False
       ColWidth = 1020
       GroupMethod = groupmethodNone
       ColFormat = ""
    End Column

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans