I would like to find out if there is a custom report that will show detailed sales by sales rep (like the custom report available to download from Microsoft for Store Operations) for HQ. I need to be able to see who sold what in each store from the HQ level so I can give commission to my sales people.
Thanks in advance.
Copy this into Notepad, and save to: c:\Program Files\Microsoft Retail Management System\Headquarters\Reports\Memorized-Commission Report.qrp
//--- Report Summary --- //
Begin ReportSummary ReportType = reporttypeSales ReportTitle = "Commission Report" PageOrientation = pageorientationPortrait WordWrap = False ShowDateTimePicker = False OutLineMode = True Groups = 1 GroupDescription = "" DisplayLogo = True LogoFileName = "MyLogo.bmp" ProcedureCall = "" PreQuery1 = "" PreQuery2 = "" TablesQueried FROM TransactionEntry INNER JOIN [Transaction] WITH(NOLOCK) ON TransactionEntry.TransactionNumber [Transaction].TransactionNumber AND TransactionEntry.StoreID [Transaction].StoreID LEFT JOIN SalesRep WITH(NOLOCK) ON TransactionEntry.SalesRepID = SalesRep.ID AND TransactionEntry.StoreID = SalesRep.StoreID LEFT JOIN Store ON [Transaction].StoreID = Store.ID
SelCriteria = "" GroupBy = "SalesRep.Number, SalesRep.Name, Store.Name, Store.ID" 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 ---//
Begin Filter FieldName = "[Transaction].Time" FilterOp = reportfilteropBetween FilterLoLim FilterHilim FilterNegated = False FilterConnector = reportfilterbooleanconAND End Filter
Begin Filter FieldName = "SalesRep.Number" FilterOp = reportfilteropNotEqual FilterLoLim = "" FilterHilim = "" FilterNegated = False FilterConnector = reportfilterbooleanconAND End Filter
Begin Filter FieldName = "Total" FilterOp = reportfilteropGreater FilterLoLim = "0" FilterHilim = "0" FilterNegated = False FilterConnector = reportfilterbooleanconAND End Filter
//--- Columns ---//
Begin Column FieldName = "SalesRep.Number" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "SalesRep Number" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 2040 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "SalesRep.Name" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "SalesRep Name" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1950 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "Store.Name" DrillDownFieldName = "Store.Name" DrillDownReportName = "" StoreIDFieldName = "" Title = "Store Name" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1800 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "Total" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Total Sales" VBDataType = vbCurrency Formula = "Sum(TransactionEntry.Price * TransactionEntry.Quantity)" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1605 GroupMethod = groupmethodSum ColFormat = "" End Column
Begin Column FieldName = "Commission" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Commission" VBDataType = vbCurrency Formula = "Sum(TransactionEntry.Commission)" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1605 GroupMethod = groupmethodSum ColFormat = "" End Column
Begin Column FieldName = "Store.ID" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Store ID" VBDataType = vbLong Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 870 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "[Transaction].Time" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Date Sold" VBDataType = vbDate Formula = "" ColHidden = True ColNotDisplayable = True FilterDisabled = False ColWidth = 900 GroupMethod = groupmethodNone ColFormat = "" End Column
"Kyle" wrote:
What if I wanted to pull in this same report:
Price Level A
Price Level B
Price Level C
Retail Price
by Specific Customer
Thanks Matt, you seem very helpful!
"Matt Hurst" wrote:
that commission report was grouped by sales rep, which might not be the right way to start a customer report. Try this instead:
//--- Report Summary --- //
Begin ReportSummary ReportType = reporttypeSales ReportTitle = "Sales By Customer" PageOrientation = pageorientationLandscape WordWrap = False ShowDateTimePicker = False OutLineMode = True Groups = 1 GroupDescription = "" DisplayLogo = True LogoFileName = "MyLogo.bmp" ProcedureCall = "" PreQuery1 = "" PreQuery2 = "" TablesQueried FROM TransactionEntry INNER JOIN [Transaction] WITH(NOLOCK) ON TransactionEntry.TransactionNumber [Transaction].TransactionNumber AND TransactionEntry.StoreID [Transaction].StoreID INNER JOIN Batch WITH(NOLOCK) ON [Transaction].BatchNumber = Batch.BatchNumber AND [Transaction].StoreID = Batch.StoreID LEFT JOIN Customer WITH(NOLOCK) ON [Transaction].CustomerID = Customer.ID 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 Store ON [Transaction].StoreID = Store.ID
SelCriteria = "" 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 ---//
Begin Filter FieldName = "[Transaction].Time" FilterOp = reportfilteropBetween FilterLoLim FilterHilim FilterNegated = False FilterConnector = reportfilterbooleanconAND End Filter
//--- Columns ---//
Begin Column FieldName = "Store.Name" DrillDownFieldName = "Store.Name" DrillDownReportName = "" StoreIDFieldName = "" Title = "Store Name" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1800 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "Store.ID" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Store ID" VBDataType = vbLong Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 870 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "Store.StoreCode" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Store Code" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1095 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "Store.Region" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Store Region" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1335 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "Store.City" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Store City" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1020 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "Store.State" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Store State" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1155 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "Store.Zip" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Store Zip" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 945 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "Store.Country" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Store Country" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1560 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "Department.Name" DrillDownFieldName = "Department.Name" DrillDownReportName = "" StoreIDFieldName = "" Title = "Department" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 2205 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column
Begin Column FieldName = "Category.Name" DrillDownFieldName = "Category.Name" DrillDownReportName = "" StoreIDFieldName = "" Title = "Category" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1515 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column
Begin Column FieldName = "Supplier.SupplierName" DrillDownFieldName = "Supplier.SupplierName" DrillDownReportName = "" StoreIDFieldName = "" 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 = "" StoreIDFieldName = "" 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 = "" StoreIDFieldName = "" 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 = "" StoreIDFieldName = "" Title = "Description" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 2115 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column
Begin Column FieldName = "Item.Quantity" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" 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 = "" StoreIDFieldName = "" Title = "Price" VBDataType = vbCurrency Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 555 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "Item.PriceA" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Price A" VBDataType = vbCurrency Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 720 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "Item.PriceB" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "PriceB" VBDataType = vbCurrency Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 660 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "Item.PriceC" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Price C" VBDataType = vbCurrency Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 720 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "Item.SalePrice" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" 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 = "" StoreIDFieldName = "" 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 = "" StoreIDFieldName = "" Title = "Sale Ends" VBDataType = vbDate Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 930 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "Item.MSRP" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "MSRP" VBDataType = vbCurrency Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 945 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "Item.LastSold" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" 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 = "" StoreIDFieldName = "" Title = "Qty Sold" VBDataType = vbDouble Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1305 GroupMethod = groupmethodSum ColFormat = "#.##" End Column
Begin Column FieldName = "TransactionEntry.Price" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Sold Price" VBDataType = vbCurrency Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1245 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "Total" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Total Sales" VBDataType = vbCurrency Formula = "TransactionEntry.Price * TransactionEntry.Quantity" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1605 GroupMethod = groupmethodSum ColFormat = "" End Column
Begin Column FieldName = "[Transaction].TransactionNumber" DrillDownFieldName = "[Transaction].TransactionNumber" DrillDownReportName = "" StoreIDFieldName = "" Title = "Transaction" VBDataType = vbLong Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1245 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "[Transaction].Time" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Date Sold" VBDataType = vbDate Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 900 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "ReasonCodeTaxChange.Description" DrillDownFieldName = "Reasoncode.Description" DrillDownReportName = "" StoreIDFieldName = "" 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 = "" StoreIDFieldName = "" 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 = "Customer.AccountNumber" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Account#" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1815 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftTop End Column
Begin Column FieldName = "CustomerName" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Customer Name" VBDataType = vbString Formula = "Customer.LastName + ', ' + Customer.FirstName" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1830 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftTop End Column
Begin Column FieldName = "Customer.Address" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Address" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1815 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftTop End Column
Begin Column FieldName = "Customer.City" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "City" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1515 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftTop End Column
Begin Column FieldName = "Customer.State" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "State" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 600 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftTop End Column
Begin Column FieldName = "Customer.Zip" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Zip" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 630 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftTop End Column
Begin Column FieldName = "Customer.PhoneNumber" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Phone" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1110 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftTop End Column
"Pete" wrote:
Thanks very much, this worked beautifully. Are you available for side work?
Warm Regards,
Pete
"Matt Hurst" 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.