Skip to main content

Notifications

Announcements

Join us in the Community for an AMA: December 12th Register Today
News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics RMS (Archived) / Purchase Order Report ...
Microsoft Dynamics RMS (Archived)

Purchase Order Report with ILC and Item Description

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Having issues tracking down the correct report configuration

Others have posted, and I've had no luck using their iterations.

Need to add: Item Lookup Code, and Item Description


Here is what I'm working with:

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

Begin ReportSummary
   ReportType = reporttypeOrders
   ReportTitle = "Purchase Order List"
   PageOrientation = pageorientationLandscape
   OutLineMode = True
   Groups = 0
   GroupDescription = ""
   DisplayLogo = True
   LogoFileName = "MyLogo.bmp"
   ProcedureCall = ""
   TablesQueried = <BEGIN>
     FROM PurchaseOrder LEFT JOIN PurchaseOrderEntry ON PurchaseOrder.ID = PurchaseOrderEntry.PurchaseOrderID
                    left join PurchaseOrder RefPO on PurchaseOrder.OriginPoid = RefPO.id  left join PurchaseOrder RefParentPO
                on PurchaseOrder.Parentpoid = RefParentPO.id
<END>
   SelCriteria = "PurchaseOrder.POType = 0 OR PurchaseOrder.POType = 1"
   GroupBy = "PurchaseOrder.ID,PurchaseOrder.Status,RefPO.PoNumber, RefParentPO.PoNumber,PurchaseOrder.MasterPO"
   SortOrder = "PONumber"
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 = "Closed"
   FilterHilim = "Closed"
End Filter


//--- Columns ---//

Begin Column
   FieldName = "PONumber"
   DrillDownFieldName = "PurchaseOrder.PONumber"
   Title = "PO #"
   VBDataType = vbString
   Formula = "MAX(PurchaseOrder.PONumber)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1500
   GroupMethod = groupmethodNone
   ColFormat = ""
   ColAlignment = flexAlignLeftCenter
End Column

Begin Column
   FieldName = "MasterPO"
   DrillDownFieldName = ""
   Title = "Master Ref #"
   VBDataType = vbString
   Formula = "MAX(PurchaseOrder.MasterPO)"
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1200
   GroupMethod = groupmethodNone
   ColFormat = ""
   ColAlignment = flexAlignLeftCenter
End Column


Begin Column
   FieldName = "RefParentPoNumber"
   DrillDownFieldName = "RefParentPo.PONumber"
   Title = "Parent PO #"
   VBDataType = vbString
   Formula = "MAX(IsNull(RefParentPo.PONumber,''))"
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1500
   GroupMethod = groupmethodNone
   ColFormat = ""
   ColAlignment = flexAlignLeftCenter
End Column


Begin Column
   FieldName = "RefPoNumber"
   DrillDownFieldName = "RefPo.PONumber"
   Title = "Origin PO #"
   VBDataType = vbString
   Formula = "MAX(RefPo.PONumber)"
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1500
   GroupMethod = groupmethodNone
   ColFormat = ""
   ColAlignment = flexAlignLeftCenter
End Column

Begin Column
   FieldName = "WorksheetID"
   DrillDownFieldName = ""
   Title = "Worksheet ID"
   VBDataType = vbLong
   Formula = "MAX(PurchaseOrder.WorksheetID)"
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 900
   GroupMethod = groupmethodNone
   ColFormat = ""
   ColAlignment = flexAlignLeftCenter
End Column

Begin Column
   FieldName = "POTitle"
   DrillDownFieldName = ""
   Title = "Title"
   VBDataType = vbString
   Formula = "MAX(PurchaseOrder.POTitle)"
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1320
   GroupMethod = groupmethodNone
   ColFormat = ""
   ColAlignment = flexAlignLeftCenter
End Column

Begin Column
   FieldName = "DateCreated"
   DrillDownFieldName = ""
   Title = "PO Date"
   VBDataType = vbDate
   Formula = "MAX(PurchaseOrder.DateCreated)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1035
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "RequiredDate"
   DrillDownFieldName = ""
   Title = "Req'd Date"
   VBDataType = vbDate
   Formula = "MAX(PurchaseOrder.RequiredDate)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1050
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "Supplier"
   DrillDownFieldName = "Supplier.SupplierName"
   Title = "Supplier"
   VBDataType = vbString
   Formula = "MAX(LEFT(PurchaseOrder.[To], CHARINDEX(char(13), PurchaseOrder.[To]+ char(13))-1))"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 2505
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "ShipVia"
   DrillDownFieldName = ""
   Title = "Ship Via"
   VBDataType = vbString
   Formula = "MAX(PurchaseOrder.ShipVia)"
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1665
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "Remarks"
   DrillDownFieldName = ""
   Title = "Remarks"
   VBDataType = vbString
   Formula = "MAX(PurchaseOrder.Remarks)"
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1665
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column



Begin Column
   FieldName = "Status"
   DrillDownFieldName = ""
   Title = "Status"
   VBDataType = vbString
   Formula = "MAX(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 = "PlacementStatus"
   DrillDownFieldName = ""
   Title = "Placement Status"
   VBDataType = vbString
   Formula = "MAX(CASE PurchaseOrder.IsPlaced WHEN 0 THEN 'Not placed' WHEN 1 THEN 'Placed' ELSE 'Unknown' END)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 690
   GroupMethod = groupmethodNone
   ColFormat = ""
   ColAlignment = flexAlignLeftCenter
End Column

Begin Column
   FieldName = "DatePlaced"
   DrillDownFieldName = ""
   Title = "Date Placed"
   VBDataType = vbDate
   Formula = "MAX(IsNull(PurchaseOrder.DatePlaced,'1/1/1990'))"
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1050
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "QuantityOrdered"
   DrillDownFieldName = ""
   Title = "Qty. Ord."
   VBDataType = vbDouble
   Formula = "SUM(PurchaseOrderEntry.QuantityOrdered)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 900
   GroupMethod = groupmethodSum
   ColFormat = "#.##"
End Column

Begin Column
   FieldName = "QuantityReceivedToDate"
   DrillDownFieldName = ""
   Title = "Qty Rcv."
   VBDataType = vbDouble
   Formula = "SUM(PurchaseOrderEntry.QuantityReceivedToDate)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 960
   GroupMethod = groupmethodSum
   ColFormat = "#.##"
End Column

Begin Column
   FieldName = "QuantityBackOrdered"
   DrillDownFieldName = ""
   Title = "Qty Back."
   VBDataType = vbDouble
   Formula = "SUM(PurchaseOrderEntry.QuantityOrdered) - SUM(PurchaseOrderEntry.QuantityReceivedToDate)"
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 960
   GroupMethod = groupmethodSum
   ColFormat = "#.##"
End Column

Begin Column
   FieldName = "SubTotal"
   DrillDownFieldName = ""
   Title = "Sub-Total"
   VBDataType = vbCurrency
   Formula = "SUM(PurchaseOrderEntry.Price * PurchaseOrderEntry.QuantityOrdered)/MAX(PurchaseOrder.ExchangeRate)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1335
   GroupMethod = groupmethodSum
   ColFormat = ""
End Column
//---New Cloumns---//
Begin Column
   FieldName = "EstShipping"
   DrillDownFieldName = ""
   Title = "Est. Shipping"
   VBDataType = vbCurrency
   Formula = "MAX(PurchaseOrder.EstShipping)/MAX(PurchaseOrder.ExchangeRate)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1290
   GroupMethod = groupmethodSum
   ColFormat = ""
End Column

Begin Column
   FieldName = "Shipping"
   DrillDownFieldName = ""
   Title = "Shipping To Date"
   VBDataType = vbCurrency
   Formula = "MAX(PurchaseOrder.Shipping)/MAX(PurchaseOrder.ExchangeRate)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1290
   GroupMethod = groupmethodSum
   ColFormat = ""
End Column

Begin Column
   FieldName = "EstOtherFees"
   DrillDownFieldName = ""
   Title = "Est. Other Fees"
   VBDataType = vbCurrency
   Formula = "MAX(PurchaseOrder.EstOtherFees)/MAX(PurchaseOrder.ExchangeRate)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1290
   GroupMethod = groupmethodSum
   ColFormat = ""
End Column

Begin Column
   FieldName = "OtherFees"
   DrillDownFieldName = ""
   Title = "Other Fees To Date"
   VBDataType = vbCurrency
   Formula = "MAX(PurchaseOrder.OtherFees)/MAX(PurchaseOrder.ExchangeRate)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1290
   GroupMethod = groupmethodSum
   ColFormat = ""
End Column
//---New Columns End---//
Begin Column
   FieldName = "Tax"
   DrillDownFieldName = ""
   Title = "Tax"
   VBDataType = vbCurrency
   Formula = "(SUM(PurchaseOrderEntry.Price * PurchaseOrderEntry.QuantityOrdered) * MAX(PurchaseOrder.TaxRate) / 100) /MAX(PurchaseOrder.ExchangeRate) "
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1065
   GroupMethod = groupmethodSum
   ColFormat = ""
End Column

Begin Column
   FieldName = "Total"
   DrillDownFieldName = ""
   Title = "Total"
   VBDataType = vbCurrency
   Formula = "CASE WHEN PurchaseOrder.Status = 0 OR PurchaseOrder.Status = 1 THEN (SUM(PurchaseOrderEntry.Price * PurchaseOrderEntry.QuantityOrdered) * (1+ MAX(PurchaseOrder.TaxRate) / 100) + MAX(PurchaseOrder.EstShipping)+MAX(PurchaseOrder.EstOtherfees))/MAX(PurchaseOrder.ExchangeRate) WHEN PurchaseOrder.Status = 2 THEN (SUM(PurchaseOrderEntry.Price * PurchaseOrderEntry.QuantityOrdered) * (1+ MAX(PurchaseOrder.TaxRate) / 100) + MAX(PurchaseOrder.Shipping)+MAX(PurchaseOrder.Otherfees))/MAX(PurchaseOrder.ExchangeRate) END"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1170
   GroupMethod = groupmethodSum
   ColFormat = ""
End Column

*This post is locked for comments

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

Join us in the Community for an AMA: December 12th Register Today

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,149 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans