110
I am trying to figure out how to generate reports that show me my purchases for the month by cost and at retail both by department and category. Everything that I can generate right now is only show me quantities. I need $$ amount.
*This post is locked for comments
Hello Michelle,
Thank you for your question.
We have several reports on the Modified Reports page on CustomerSource that will display the item cost information along with other data.
To find a report that may work for your needs, please visit the Modified Reports Page at the following page:
mbs.microsoft.com/.../modifiedreports_rmsstoreoperationsreports
Please note that you will need an active support plan to access the support page.
Thank you,
Scott Wardzinski
Microsoft Dynamics RMS & POS Support Engineer
Sorry to hear your so-called partner sold you software they can't support; I don't believe MS creates reports for end users either; see if the below works for you. copy and paste it into a NOTEPAD document, save it with file name Custom - Purchase Order Receiving Details.qrp in the default report directory. If you have problems following these steps drop an email at c25consult at gmail dot com and I'll send it to you as an attachment.
//--- Report Summary --- //
Begin ReportSummary
ReportType = reporttypeOrders
ReportTitle = "Purchase Order Receiving Details"
PageOrientation = pageorientationLandscape
WordWrap = False
ShowDateTimePicker = False
OutLineMode = True
Groups = 1
GroupDescription = ""
DisplayLogo = True
LogoFileName = "MyLogo.bmp"
ProcedureCall = ""
PreQuery1 = ""
PreQuery2 = ""
TablesQueried = <BEGIN>
FROM InventoryTransferLog WITH(NOLOCK)
LEFT JOIN PurchaseOrder WITH(NOLOCK)
ON InventoryTransferLog.ReferenceID = PurchaseOrder.ID
LEFT JOIN PurchaseOrderEntry WITH(NOLOCK)
ON InventoryTransferLog.ReferenceEntryID = PurchaseOrderEntry.ID
AND InventoryTransferLog.ReferenceID = PurchaseOrderEntry.PurchaseOrderID
LEFT JOIN Item WITH(NOLOCK)
ON InventoryTransferLog.ItemID = Item.ID
LEFT JOIN Supplier WITH(NOLOCK)
ON PurchaseOrder.SupplierID = Supplier.ID
LEFT JOIN Department WITH(NOLOCK) ON Item.DepartmentID = Department.ID
LEFT JOIN Cashier WITH(NOLOCK) ON InventoryTransferLog.CashierID = Cashier.ID
<END>
SelCriteria = "InventoryTransferLog.Type = 1 AND PurchaseOrder.POType IN (0,1)"
GroupBy = ""
SortOrder = "[MarginPercent]"
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 = "Generated On <Report Date>"
Font = "Arial"
FontBold = True
FontSize = 10
Color = "Black"
End TitleRow
//--- Filters ---//
Begin Filter
FieldName = "Status"
FilterOp = reportfilteropNotEqual
FilterLoLim = "Open"
FilterHilim = "Open"
FilterNegated = False
FilterConnector = reportfilterbooleanconAND
End Filter
Begin Filter
FieldName = "InventoryTransferLog.DateTransferred"
FilterOp = reportfilteropBetween
FilterLoLim = "<MonthStart>"
FilterHilim = "<Today>"
FilterNegated = False
FilterConnector = reportfilterbooleanconAND
End Filter
//--- Columns ---//
Begin Column
FieldName = "PurchaseOrder.PONumber"
DrillDownFieldName = "PurchaseOrder.PONumber"
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "PO #"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1300
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Begin Column
FieldName = "Supplier.SupplierName"
DrillDownFieldName = "Supplier.SupplierName"
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Supplier"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1800
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignCenterCenter
End Column
Begin Column
FieldName = "InventoryTransferLog.DateTransferred"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Date Rcvd"
VBDataType = vbDate
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1000
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignCenterCenter
End Column
Begin Column
FieldName = "Department.Name"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Department"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1400
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Begin Column
FieldName = "Item.ItemLookupCode"
DrillDownFieldName = "Item.ItemLookupCode"
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Lookup Code"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1400
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Begin Column
FieldName = "Item.Description"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Item Description"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2315
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Begin Column
FieldName = "InventoryTransferLog.Quantity"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Qty Rcvd."
VBDataType = vbDouble
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 900
GroupMethod = groupmethodNone
ColFormat = "#"
ColAlignment = flexAlignCenterCenter
End Column
Begin Column
FieldName = "InventoryTransferLog.Cost"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Rcvd Cost"
VBDataType = vbCurrency
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1000
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignCenterCenter
End Column
Begin Column
FieldName = "Item.Cost"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Inven Cost"
VBDataType = vbCurrency
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1200
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignCenterCenter
End Column
Begin Column
FieldName = "Item.Price"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Inven Price"
VBDataType = vbCurrency
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1200
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignCenterCenter
End Column
Begin Column
FieldName = "ItemMargin"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Margin $"
VBDataType = vbCurrency
Formula = "Item.Price - Item.Cost"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1100
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignCenterCenter
End Column
Begin Column
FieldName = "MarginPercent"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Margin %"
VBDataType = vbDouble
Formula = "CASE WHEN Item.Price > 0 THEN ((Item.Price - Item.Cost)/Item.Price) ELSE 0 END"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1300
GroupMethod = groupmethodNone
ColFormat = "#.##%"
ColAlignment = flexAlignCenterCenter
End Column
Begin Column
FieldName = "Status"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Status"
VBDataType = vbString
Formula = "(CASE PurchaseOrder.Status WHEN 0 THEN 'Open' WHEN 1 THEN 'Partial' WHEN 2 THEN 'Closed' ELSE 'Unknown' END)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 690
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Begin Column
FieldName = "Cashier.Name"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Rcvd By"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1690
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Michael Haugen, you originally responded to my question saying someone in Microsoft could do this for me with a current maintenance plan. I called them originally and asked if they could do it and they told me I needed to talk to who I purchased my software through. I called that company and they told me they could not help me. I have no computer programming expertise and I just need someone to set up these reports for me so I can access the information I need. Can you advise me please on who I would need to talk to that could help me?
Have you looked at the Custom Reports library available to download from the CustomerSource/PartnerSource sites?
If it is not there, we have a few reports with some of this info, with different columns - one has Department and more but not Category.
But MS will also make them up for you/your customers with a current maintenance plan.
#1
André Arnaud de Cal...
291,431
Super User 2024 Season 2
#2
Martin Dráb
230,503
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
