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) / HQ Reorder/Restock Rep...
Microsoft Dynamics RMS (Archived)

HQ Reorder/Restock Report Modified

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

Hi, we are looking for a way to have each stores reorder point and restock level side by side in a report in HQ. We are familiar with the Item Snapshot Quantity list but this report seperates everything by store.


Ideally it would be be something like this:

Dept, Category, Item Lookup Code, Description, QTY on hand, Store 1 Reorder Point, Store 1 Restock Level, Store 2 Reorder Point, Store 2 Restock level, etc.


If someone has the code to add this to an existing report that would be great!

Thanks,
Devon

*This post is locked for comments

I have the same question (0)
  • Verified answer
    Community Member Profile Picture
    Community Member on at

    This report should do it.  Add stores to the select list and columns as needed:

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

    Begin ReportSummary
       ReportType = reporttypeItems
       ReportTitle = "Store Reorder-Restock"
       PageOrientation = pageorientationPortrait
       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  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

    <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 = "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.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 = "S1 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 = "S1 Quantity"
       VBDataType = vbDouble
       Formula = ""
       ColHidden = True
       ColNotDisplayable = False
       FilterDisabled = False
       ColWidth = 1000
       GroupMethod = groupmethodSum
       ColFormat = "#.##"
    End Column


    Begin Column
       FieldName = "S1.SnapShotQuantityCommitted"
       DrillDownFieldName = ""
       DrillDownReportName = ""
       Title = "S1 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 = "S1 Reorder"
       VBDataType = vbDouble
       Formula = ""
       ColHidden = False
       ColNotDisplayable = False
       FilterDisabled = False
       ColWidth = 1100
       GroupMethod = groupmethodNone
       ColFormat = ""
    End Column


    Begin Column
       FieldName = "S1.SnapShotRestockLevel"
       DrillDownFieldName = ""
       DrillDownReportName = ""
       Title = "S1 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 = "S2 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 = "S2 Quantity"
       VBDataType = vbDouble
       Formula = ""
       ColHidden = True
       ColNotDisplayable = False
       FilterDisabled = False
       ColWidth = 1000
       GroupMethod = groupmethodSum
       ColFormat = "#.##"
    End Column


    Begin Column
       FieldName = "S2.SnapShotQuantityCommitted"
       DrillDownFieldName = ""
       DrillDownReportName = ""
       Title = "S2 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 = "S2 Reorder"
       VBDataType = vbDouble
       Formula = ""
       ColHidden = False
       ColNotDisplayable = False
       FilterDisabled = False
       ColWidth = 1100
       GroupMethod = groupmethodNone
       ColFormat = ""
    End Column


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

  • DEVON LEROUX Profile Picture
    DEVON LEROUX 1,565 on at

    This worked perfectly!

    Thank-you Matt

  • Scott Hale Profile Picture
    Scott Hale 25 on at

    Hi there,

    Thank you for the report above - this also helped me.

    However, I was wondering if it's possible to run the same type of report but include sales of each  item for each store over a specified timescale.

    Thanks

    Scott

  • Community Member Profile Picture
    Community Member on at

    you could probably get a prequery view to collect that sales data first and then query from that, but I think you would be better off using the detailed sales report instead.

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