Anyone know how to do this? I am familiar with editing reports but this one has me baffled.
- posted
18 years ago
Anyone know how to do this? I am familiar with editing reports but this one has me baffled.
Jack,
It's OrderEntry.SalesRepID, but you should also join to the Sales Rep table to get the name.
Good luck, Matt
Jack,
LEFT JOIN SalesRep WITH(NOLOCK) on OrderEntry.SalesRepID = SalesRep.ID
Don't be afraid of looking or stealing at/from the other built-in reports. This came from the Sales Commission Report with a minor change of tables names. TransactionEntry.SalesRepID to OrderEntry.SalesRepID
You can steal the Sales Rep column from that report too!
Thanks Jeff, Works Great. I will post the form here so anyone else can copy save and use!
//--- Report Summary --- //
Begin ReportSummary ReportType = reporttypeOrders ReportTitle = "Layaway Report with Salesperson details" PageOrientation = pageorientationLandscape OutLineMode = True Groups = 1 GroupDescription = "" DisplayLogo = True LogoFileName = "MyLogo.bmp" ProcedureCall = "" TablesQueried FROM OrderEntry WITH(NOLOCK) INNER JOIN [Order] WITH(NOLOCK) ON OrderEntry.OrderID = [Order].ID LEFT JOIN Customer WITH(NOLOCK) ON [Order].CustomerID Customer.ID LEFT JOIN Item WITH(NOLOCK) ON OrderEntry.ItemID = Item.ID LEFT JOIN ReasonCode AS ReasonCodeDiscount WITH(NOLOCK) ON OrderEntry.DiscountReasonCodeID = ReasonCodeDiscount.ID LEFT JOIN ReasonCode AS ReasonCodeTaxChange WITH(NOLOCK) ON OrderEntry.TaxChangeReasonCodeID = ReasonCodeTaxChange.ID LEFT JOIN SalesRep WITH(NOLOCK) on OrderEntry.SalesRepID = SalesRep.ID
SelCriteria = "[Order].Type = 5" GroupBy = "" SortOrder = "" End ReportSummary
//--- Title Rows ---//
Begin TitleRow Text = "" Font = "Arial" FontBold = True FontSize = 16 Color = "Blue" End TitleRow
Begin TitleRow Text = "" Font = "Arial" FontBold = True FontSize = 12 Color = "Black" End TitleRow
Begin TitleRow Text = "Generated On " Font = "Arial" FontBold = True FontSize = 10 Color = "Black" End TitleRow
//--- Filters ---//
//--- Columns ---//
Begin Column FieldName = "[Order].Time" DrillDownFieldName = "" DrillDownReportName = "" Title = "Date" VBDataType = vbDate Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1350 GroupMethod = groupmethodNone ColFormat = "ddddd" ColAlignment = flexAlignRightCenter End Column
Begin Column FieldName = "SalesRep.Name" DrillDownFieldName = "SalesRep.Name" DrillDownReportName = "" StoreIDFieldName = "" Title = "Sales Rep" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1920 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column
Begin Column FieldName = "[Order].ID" DrillDownFieldName = "" DrillDownReportName = "" Title = "Layaway #" VBDataType = vbLong Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 810 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column
Begin Column FieldName = "Status" DrillDownFieldName = "" DrillDownReportName = "" Title = "Status" VBDataType = vbString Formula = "CASE [Order].Closed WHEN 0 THEN 'Open' ELSE 'Closed' END" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 810 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column
Begin Column FieldName = "[Order].Comment" DrillDownFieldName = "" DrillDownReportName = "" Title = "Comment" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1455 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column
Begin Column FieldName = "CustomerName" DrillDownFieldName = "" DrillDownReportName = "" Title = "Customer" VBDataType = vbString Formula = "Customer.LastName + ', ' + Customer.FirstName" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1350 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column
Begin Column FieldName = "Customer.AccountNumber" DrillDownFieldName = "Customer.AccountNumber" DrillDownReportName = "" Title = "Account #" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1035 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column
Begin Column FieldName = "[Order].ExpirationOrDueDate" DrillDownFieldName = "" DrillDownReportName = "" Title = "Exp. Date" VBDataType = vbDate Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 885 GroupMethod = groupmethodNone ColFormat = "ddddd" ColAlignment = flexAlignRightCenter End Column
Begin Column FieldName = "Item.ItemLookupCode" DrillDownFieldName = "Item.ItemLookupCode" DrillDownReportName = "" Title = "Item Lookup Code" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1665 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column
Begin Column FieldName = "Item.Description" DrillDownFieldName = "" DrillDownReportName = "" Title = "Description" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 2010 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column
Begin Column FieldName = "Item.Quantity" DrillDownFieldName = "" DrillDownReportName = "" Title = "Qty On Hand" VBDataType = vbDouble Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1125 GroupMethod = groupmethodSum ColFormat = "#.##" End Column
Begin Column FieldName = "QuantityOrdered" DrillDownFieldName = "" DrillDownReportName = "" Title = "Qty Ord." VBDataType = vbDouble Formula = "(OrderEntry.QuantityOnOrder + OrderEntry.QuantityRTD)" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 825 GroupMethod = groupmethodSum ColFormat = "#.##" End Column
Begin Column FieldName = "OrderEntry.Price" DrillDownFieldName = "" DrillDownReportName = "" Title = "Price Ea." VBDataType = vbCurrency Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 855 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "ExtendedPrice" DrillDownFieldName = "" DrillDownReportName = "" Title = "Extended" VBDataType = vbCurrency Formula = "OrderEntry.Price * (OrderEntry.QuantityOnOrder + OrderEntry.QuantityRTD)" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1125 GroupMethod = groupmethodSum ColFormat = "" End Column
Begin Column FieldName = "OrderEntry.QuantityRTD" DrillDownFieldName = "" DrillDownReportName = "" Title = "Qty Rcv." VBDataType = vbDouble Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1200 GroupMethod = groupmethodSum ColFormat = "#.##" End Column
Begin Column FieldName = "OrderEntry.QuantityOnOrder" DrillDownFieldName = "" DrillDownReportName = "" Title = "Qty On Layaway" VBDataType = vbDouble Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1380 GroupMethod = groupmethodSum ColFormat = "#.##" End Column
Begin Column FieldName = "Item.BinLocation" DrillDownFieldName = "" DrillDownReportName = "" Title = "Bin Location" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1140 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column
Begin Column FieldName = "OrderEntry.Cost" DrillDownFieldName = "" DrillDownReportName = "" Title = "Cost Each" VBDataType = vbCurrency Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 945 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "ReasonCodeDiscount.Description" DrillDownFieldName = "Reasoncode.Description" DrillDownReportName = "" Title = "Discount Reason Code" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 2280 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column
Begin Column FieldName = "ReasonCodeTaxChange.Description" DrillDownFieldName = "Reasoncode.Description" DrillDownReportName = "" Title = "Tax Change Reason Code" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 2280 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column
"Jeff" wrote:
BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.