web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

Sign Up for Ask a Community Pro Today!
Introducing our New Video Series: Community Spotlight
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics RMS (Archived) / QTY to order HQ Report...
Microsoft Dynamics RMS (Archived)

QTY to order HQ Report with SupplierCost

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

Hello, we are looking to make a report that will quickly show all items that need to be ordered for both of our stores to check and see if we can make order minimums from any of our suppliers.

 

So far I've got this, but I'm getting errors


      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
 

FROM PurchaseOrder WITH(NOLOCK)
      LEFT JOIN  PurchaseOrderEntry ON PurchaseOrder.ID = PurchaseOrderEntry.PurchaseOrderID
      LEFT JOIN  Supplier WITH (NOLOCK) ON Supplier.ID = PurchaseOrder.SupplierID
      LEFT JOIN  ItemPurchaseOrder WITH (NOLOCK) on PurchaseOrderEntry.ItemID = Item.ID

Lots of other stuff

Lots of other stuff


Begin Column
   FieldName = "QuantityToOrder"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Q to Order"
   VBDataType = vbDouble
   Formula = "CASE WHEN S1.SnapShotQuantity - S1.SnapShotQuantityCommitted + S2.SnapShotQuantity - S2.SnapShotQuantityCommitted <= S1.SnapShotReorderPoint + s2.SnapShotReorderPoint - PurchaseOrderEntry.QuantityOrdered -PurchaseOrderEntry.QuantityReceivedToDate THEN S1.SnapShotRestockLevel -    S1.SnapShotQuantity + s1.SnapShotQuantityCommitted + S2.SnapShotRestockLevel - S2.SnapShotQuantity + s2.SnapShotQuantityCommitted  ELSE 0 END"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1215
   GroupMethod = groupmethodSum
   ColFormat = "#.##"
End Column

  

Right now when I run this report I get the error "The report could not be loaded due to the following error: Incorrect syntax near the keyword FROM"

This is pointing to "FROM PurchaseOrder"

I can run the report without that and "PurchaseOrderEntry.QuantityOrdered -PurchaseOrderEntry.QuantityReceivedToDate" But then the report lists everything we have on backorder (which from our suppliers can often be A LOT of stuff ... yes, its frustrating.)

Any thoughts or ideas?

 

I'd also like to add SupplierCost to the report so we can easily see the total to order from each supplier.

 

Thanks,
Devon

 

 

 

here's the whole report if you'd like to see it:

 

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

Begin ReportSummary
   ReportType = reporttypeItems
   ReportTitle = "STOCK"
   PageOrientation = pageorientationPortrait
   WordWrap = False
   ShowDateTimePicker = False
   OutLineMode = True
   Groups = 1
   GroupDescription = "Total"
   DisplayLogo = True
   LogoFileName = "MyLogo.bmp"
   ProcedureCall = ""
   PreQuery1 = ""
   PreQuery2 = ""
   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
 FROM PurchaseOrder WITH(NOLOCK)
      LEFT JOIN  PurchaseOrderEntry ON PurchaseOrder.ID = PurchaseOrderEntry.PurchaseOrderID
      LEFT JOIN  Supplier WITH (NOLOCK) ON Supplier.ID = PurchaseOrder.SupplierID
      LEFT JOIN  ItemPurchaseOrder WITH (NOLOCK) on PurchaseOrderEntry.ItemID = Item.ID


<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 ---//

Begin Filter
   FieldName = "Item.Quantity"
   FilterOp = reportfilteropNotEqual
   FilterLoLim = "0"
   FilterHilim = "0"
   FilterNegated = False
   FilterConnector = reportfilterbooleanconAND
End Filter

Begin Filter
   FieldName = "S3.SnapShotQuantity"
   FilterOp = reportfilteropGreater
   FilterLoLim = "0"
   FilterHilim = "0"
   FilterNegated = False
   FilterConnector = reportfilterbooleanconAND
End Filter

Begin Filter
   FieldName = "S5.SnapShotQuantity"
   FilterOp = reportfilteropGreater
   FilterLoLim = "0"
   FilterHilim = "0"
   FilterNegated = False
   FilterConnector = reportfilterbooleanconOR
End Filter

Begin Filter
   FieldName = "S4.SnapShotQuantity"
   FilterOp = reportfilteropGreater
   FilterLoLim = "0"
   FilterHilim = "0"
   FilterNegated = False
   FilterConnector = reportfilterbooleanconOR
End Filter

Begin Filter
   FieldName = "S10.SnapShotQuantity"
   FilterOp = reportfilteropGreater
   FilterLoLim = "0"
   FilterHilim = "0"
   FilterNegated = False
   FilterConnector = reportfilterbooleanconOR
End Filter


//--- Columns ---//

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

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

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

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

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

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

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

Begin Column
   FieldName = "S4.SnapShotQuantity"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "Dads Quantity"
   VBDataType = vbDouble
   Formula = ""
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 792
   GroupMethod = groupmethodSum
   ColFormat = "#.##"
End Column

Begin Column
   FieldName = "S5.SnapShotQuantity"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "Grammas Quantity"
   VBDataType = vbDouble
   Formula = ""
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 756
   GroupMethod = groupmethodSum
   ColFormat = "#.##"
End Column

Begin Column
   FieldName = "S10.SnapShotQuantity"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "Trailer Quantity"
   VBDataType = vbDouble
   Formula = ""
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 708
   GroupMethod = groupmethodSum
   ColFormat = "#.##"
End Column

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

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

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

Begin Column
   FieldName = "QuantityToOrder"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Q to Order"
   VBDataType = vbDouble
   Formula = "CASE WHEN S1.SnapShotQuantity - S1.SnapShotQuantityCommitted + S2.SnapShotQuantity -   

S2.SnapShotQuantityCommitted <= S1.SnapShotReorderPoint + s2.SnapShotReorderPoint - PurchaseOrderEntry.QuantityOrdered -     

PurchaseOrderEntry.QuantityReceivedToDate THEN S1.SnapShotRestockLevel -    S1.SnapShotQuantity +

s1.SnapShotQuantityCommitted + S2.SnapShotRestockLevel - S2.SnapShotQuantity + s2.SnapShotQuantityCommitted  ELSE 0 END"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1215
   GroupMethod = groupmethodSum
   ColFormat = "#.##"
End Column

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

Begin Column
   FieldName = "S10.SnapShotRestockLevel"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "Trailer Restock"
   VBDataType = vbDouble
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1128
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

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

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

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

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

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

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

Begin Column
   FieldName = "S6.SnapShotQuantity"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "Bobs Quantity"
   VBDataType = vbDouble
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 996
   GroupMethod = groupmethodSum
   ColFormat = "#.##"
End Column

Begin Column
   FieldName = "S7.SnapShotQuantity"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "1005 Quantity"
   VBDataType = vbDouble
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 996
   GroupMethod = groupmethodSum
   ColFormat = "#.##"
End Column

Begin Column
   FieldName = "S8.SnapShotQuantity"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "1009 Quantity"
   VBDataType = vbDouble
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 996
   GroupMethod = groupmethodSum
   ColFormat = "#.##"
End Column

Begin Column
   FieldName = "S10.SnapShotTime"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "Trailer SnapShot Date"
   VBDataType = vbDate
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1392
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

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

Begin Column
   FieldName = "S10.SnapShotReorderPoint"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "Trailer Reorder"
   VBDataType = vbDouble
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1104
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

 

 

*This post is locked for comments

I have the same question (0)
  • Artur Hanusek Profile Picture
    Artur Hanusek 200 on at

    Hi Devon,

    Table Item and PurchaseOrderEntry cannot be joined together this way as they contain 2 totally different information.

    PurchaseOrderEntry holds info about product Orders, Item contains info about product itself.

    You can should create view which will display all items on back order and then and then join it to your report.

    Drop me a quick email (arthur@youritsolutions.ie) if you need any help with it, we've done similar reports to this in the past.

    Regards,

    Arthur

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

News and Announcements

Sign Up for Ask a Community Pro Today!
Introducing our New Video Series: Community Spotlight

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Congratulations to our 2025 Community Spotlights

Thanks to all of our 2025 Community Spotlight stars!

Congratulations to the December 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