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

  • Suggested answer
    Antonijo Todorovik Profile Picture
    Antonijo Todorovik 4,025 on at
    RE: HQ Report to reference Item Dynamics table for Last Sold

    Hi Upendra Nath M.

    You can use the "CASE" tag in formula field. For example, the field can be something like this next:

    Formula = "CASE [FieldName] WHEN Null THEN 0 ELSE [FieldName] END"

    Also, You can use tSQL function ISNULL, and assign a 0 or by default value when your field is 0...

    Hope this helps, A.

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

    Hello Devon,

    Thanks, but there are lot of NULL values, how to handle them?

    Thanks & Regards,

    Upen

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,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans