Does anyone have a report that can pull up detailed sales history by Sales Rep?
- posted
15 years ago
Detailed sales report by Sales Rep
Loading thread data ...
- Vote on answer
- posted
15 years ago
Hi Scott - paste this verbiage on a notepad, save it as "Custom - Sales by Sales Rep" Run it from the custom section - hope this helps
//--- Report Summary --- //
Begin ReportSummary ReportType = reporttypeSales ReportTitle = "Detailed Sales Report by Sales Rep" PageOrientation = pageorientationPortrait OutLineMode = True Groups = 1 GroupDescription = "" DisplayLogo = True LogoFileName = "MyLogo.bmp" ProcedureCall = "" TablesQueried FROM TransactionEntry INNER JOIN [Transaction] WITH(NOLOCK) ON TransactionEntry.TransactionNumber [Transaction].TransactionNumber INNER JOIN Batch WITH(NOLOCK) ON [Transaction].BatchNumber Batch.BatchNumber LEFT JOIN Item WITH(NOLOCK) ON TransactionEntry.ItemID = Item.ID LEFT JOIN Department WITH(NOLOCK) ON Item.DepartmentID Department.ID LEFT JOIN Category WITH(NOLOCK) ON Item.CategoryID = Category.ID LEFT JOIN Supplier WITH(NOLOCK) ON Item.SupplierID = Supplier.ID LEFT JOIN ReasonCode AS ReasonCodeDiscount WITH(NOLOCK) ON TransactionEntry.DiscountReasonCodeID ReasonCodeDiscount.ID LEFT JOIN ReasonCode AS ReasonCodeTaxChange WITH(NOLOCK) ON TransactionEntry.TaxChangeReasonCodeID ReasonCodeTaxChange.ID LEFT JOIN ReasonCode AS ReasonCodeReturn WITH(NOLOCK) ON TransactionEntry.ReturnReasonCodeID = ReasonCodeReturn.ID LEFT JOIN Register WITH(NOLOCK) ON Batch.RegisterID = Register.ID LEFT JOIN Customer WITH(NOLOCK) ON [Transaction].CustomerID Customer.ID LEFT JOIN Cashier WITH(NOLOCK) ON [Transaction].CashierID Cashier.ID LEFT JOIN QuantityDiscount WITH(NOLOCK) ON TransactionEntry.QuantityDiscountID = QuantityDiscount.ID LEFT JOIN SalesRep WITH(NOLOCK) on [TransactionEntry].SalesRepID = SalesRep.ID
SelCriteria = "" GroupBy = "" SortOrder = "SalesRep.Name, [Transaction].Time" 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 ---//
Begin Filter FieldName = "[Transaction].Time" FilterOp = reportfilteropBetween FilterLoLim = "" FilterHilim = "" End Filter
//--- Columns ---//
Begin Column FieldName = "SalesRep.Name" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Sales Rep" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1515 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column
Begin Column FieldName = "SalesRep.Number" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Sales Rep #" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1515 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column
Begin Column FieldName = "[Transaction].Time" DrillDownFieldName = "" DrillDownReportName = "" Title = "Date Sold" VBDataType = vbDate Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1100 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "Department.Name" DrillDownFieldName = "Department.Name" DrillDownReportName = "" Title = "Department" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 2205 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column
Begin Column FieldName = "Category.Name" DrillDownFieldName = "Category.Name" DrillDownReportName = "" Title = "Category" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1515 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column
Begin Column FieldName = "Supplier.SupplierName" DrillDownFieldName = "Supplier.SupplierName" DrillDownReportName = "" Title = "Supplier" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 825 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column
Begin Column FieldName = "Item.ItemLookupCode" DrillDownFieldName = "Item.ItemLookupCode" DrillDownReportName = "" Title = "Item" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1380 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter 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 = "Item.Description" DrillDownFieldName = "" DrillDownReportName = "" Title = "Description" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 2300 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column
Begin Column FieldName = "Item.Quantity" DrillDownFieldName = "" DrillDownReportName = "" Title = "On Hand" VBDataType = vbDouble Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 810 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "Item.Price" DrillDownFieldName = "" DrillDownReportName = "" Title = "Price" VBDataType = vbCurrency Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 555 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "Item.SalePrice" DrillDownFieldName = "" DrillDownReportName = "" Title = "Sale Price" VBDataType = vbCurrency Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 945 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "Item.SaleStartDate" DrillDownFieldName = "" DrillDownReportName = "" Title = "Sale Starts" VBDataType = vbDate Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1020 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "Item.SaleEndDate" DrillDownFieldName = "" DrillDownReportName = "" Title = "Sale Ends" VBDataType = vbDate Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 930 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "Item.LastSold" DrillDownFieldName = "" DrillDownReportName = "" Title = "Last Sold" VBDataType = vbDate Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 900 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "TransactionEntry.Quantity" DrillDownFieldName = "" DrillDownReportName = "" Title = "Qty Sold" VBDataType = vbDouble Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 810 GroupMethod = groupmethodSum ColFormat = "#.##" End Column
Begin Column FieldName = "TransactionEntry.Price" DrillDownFieldName = "" DrillDownReportName = "" Title = "Sold Price" VBDataType = vbCurrency Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1200 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "Total" DrillDownFieldName = "" DrillDownReportName = "" Title = "Total Sales" VBDataType = vbCurrency Formula = "TransactionEntry.Price * TransactionEntry.Quantity" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1275 GroupMethod = groupmethodSum ColFormat = "" End Column
Begin Column FieldName = "[Transaction].TransactionNumber" DrillDownFieldName = "[Transaction].TransactionNumber" DrillDownReportName = "" Title = "Trans#" VBDataType = vbLong Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 700 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "TransactionEntry.Cost" DrillDownFieldName = "" DrillDownReportName = "" Title = "Cost" VBDataType = vbCurrency Formula = "TransactionEntry.Quantity * TransactionEntry.Cost" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 900 GroupMethod = groupmethodSum ColFormat = "" End Column
Begin Column FieldName = "Profit" DrillDownFieldName = "" DrillDownReportName = "" Title = "Profit" VBDataType = vbCurrency Formula = "(TransactionEntry.Price - TransactionEntry.Cost) * TransactionEntry.Quantity" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 900 GroupMethod = groupmethodSum ColFormat = "" End Column
Begin Column FieldName = "ProfitMargin" DrillDownFieldName = "" DrillDownReportName = "" Title = "Profit Margin" VBDataType = vbDouble Formula = "CASE WHEN TransactionEntry.Price 0 THEN CASE WHEN TransactionEntry.Quantity > 0 THEN (TransactionEntry.Price -
TransactionEntry.Cost) / TransactionEntry.Price ELSE CASE WHEN TransactionEntry.Quantity < 0 THEN (TransactionEntry.Price -
TransactionEntry.Cost) / (TransactionEntry.Price*-1) ELSE 0 END END ELSE 0 END" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 900 GroupMethod = groupmethodAverage ColFormat = "0.00%" 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
Begin Column FieldName = "ReasonCodeReturn.Description" DrillDownFieldName = "Reasoncode.Description" DrillDownReportName = "" Title = "Return Reason Code" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 2280 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column
Begin Column FieldName = "PriceSource" DrillDownFieldName = "" DrillDownReportName = "" Title = "Price Source" VBDataType = vbString Formula = "CASE TransactionEntry.PriceSource WHEN 1 THEN 'Regular Price' WHEN 2 THEN (CASE WHEN
TransactionEntry.QuantityDiscountID 0 THEN QuantityDiscount.Description ELSE 'Quantity Discount' END) WHEN 3 THEN 'Buydown
Discount' WHEN 4 THEN 'Price Level Disc.' WHEN 5 THEN 'Sale Price' WHEN 6 THEN 'Disc. from Reg. Price' WHEN 7 THEN 'Disc. from Cur.
Price' WHEN 8 THEN 'Cost Markup Disc.' WHEN 9 THEN 'Profit Margin Disc.' WHEN 10 THEN 'Cashier Set' WHEN 11 THEN 'Component'
WHEN 12 THEN 'Price Level A Disc.' WHEN 13 THEN 'Price Level B Disc.' WHEN
14 THEN 'Price Level C Disc.' WHEN 15 THEN 'Disc. fromReg. Price' WHEN 16 THEN 'Disc. from Cur. Price' WHEN 17 THEN 'Cost Markup Disc.' WHEN 18 THEN 'Profit Margin Disc.' ELSE 'Unknown'
END" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 2280 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column
Begin Column FieldName = "Register.Number" DrillDownFieldName = "" DrillDownReportName = "" Title = "Register" VBDataType = vbLong Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1245 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "CustomerName" DrillDownFieldName = "" DrillDownReportName = "" Title = "Customer" VBDataType = vbString Formula = "Customer.LastName + ', ' + Customer.FirstName" ColHidden = True 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 = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1035 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column
Begin Column FieldName = "Cashier.Name" DrillDownFieldName = "Cashier.Name" DrillDownReportName = "" Title = "Cashier" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1350 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column
"Scott S" wrote: