1. Home
  2. Point-Of-Sale Software
  3. Error Total sales report hour sales

Error Total sales report hour sales

Hi all,

Got a hourly sales report from the microsoft customer source which gives a wrong total sales because it is selecting more items than sold during a certain time. Can anybody tell me what needs to be change in the qrp. report below:

Jeff2

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

Begin ReportSummary ReportType = reporttypeSales ReportTitle = "Detailed Sales Report by hour" PageOrientation = pageorientationLandscape WordWrap = False ShowDateTimePicker = False OutLineMode = True Groups = 2 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 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 LEFT JOIN Customer ON [Transaction].CustomerID = Customer.ID LEFT JOIN SalesRep ON TransactionEntry.SalesRepID = SalesRep.ID AND TransactionEntry.StoreID = SalesRep.StoreID

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 = "TransactionTime" FilterOp = reportfilteropBetween FilterLoLim = "" FilterHilim = "" End Filter

//--- Columns ---//

Begin Column FieldName = "Store.Name" DrillDownFieldName = "Store.Name" DrillDownReportName = "" Title = "Store Name" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1000 GroupMethod = groupmethodNone ColFormat = "" End Column

Begin Column FieldName = "TimeSold" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Time Sold" VBDataType = vbString Formula = "cast(DatePart(Hh,[Transaction].Time) as nvarchar)+ ':00'" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1000 GroupMethod = groupmethodNone ColFormat = "" End Column

Begin Column FieldName = "Store.ID" DrillDownFieldName = "" DrillDownReportName = "" Title = "Store ID" VBDataType = vbLong Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 870 GroupMethod = groupmethodNone ColFormat = "" End Column

Begin Column FieldName = "Department.Name" DrillDownFieldName = "Department.Name" DrillDownReportName = "" 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 = "" 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 = "" 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.Description" DrillDownFieldName = "" DrillDownReportName = "" Title = "Description" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 2115 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 = "SalesRep.Name" DrillDownFieldName = "SalesRep.Name" DrillDownReportName = "" Title = "Sales Rep" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 2000 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column

Begin Column FieldName = "SalesRep.Number" DrillDownFieldName = "" DrillDownReportName = "" Title = "Sales Rep #" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1365 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter 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.MSRP" DrillDownFieldName = "" DrillDownReportName = "" 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 = "" 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 = 1300 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 = 1250 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 = 1600 GroupMethod = groupmethodSum 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 = groupmethodNone ColFormat = "0.00%" End Column

Begin Column FieldName = "[Transaction].TransactionNumber" DrillDownFieldName = "[Transaction].TransactionNumber" DrillDownReportName = "" Title = "Transaction" VBDataType = vbLong Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1245 GroupMethod = groupmethodNone ColFormat = "" End Column

Begin Column FieldName = "TransactionTime" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Date Sold" VBDataType = vbDate Formula = "CONVERT(datetime, CONVERT(nvarchar, [Transaction].Time, 101),

101)" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 900 GroupMethod = groupmethodNone ColFormat = "" 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

Reply to
Jeff
Loading thread data ...

Hi Jeff,

I have not really read through your report, but I have one for HQ that lists Detailed SPH on a per location basis. It has been tested and used widely by our company. I can send it to you if you would like to compare. You can send me an email, and I can attach it back to you.

Jocelyn snipped-for-privacy@kuhlmancompany.com

"Jeff" wrote:

Reply to
Jocelyn

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.