Hi,
How do I pull a single report with the following criteria: 1) Supplier 2) Total quantity of items sold 3) Total quantity of items on hand 4) Specific Date range 5) "Ext'd Description" column.
The detailed sales report provides the information but it displays a line item for each item sold, so if you sold 6 of item ABC there would be 6 individual line items for each time it sold, I need just a total for the subject item(s) sold.
A custom report was suggested/provided by a member of the forum but it does not include the "Ext'd Description" column.
Any help appreciated.
*This post is locked for comments
Hi Upendra.
You are refering to the report posted by extel? As I saed, I need to dive into my backups and see...
BR, A.
Danny, this was long ago, I have to go through my backups and see if I can find it...
Will try, OK?
Hi Antonijo,
The On Hand qty is showing the HQ qty not the respective store on hand qty.
Thanks & Regards,
Upendra Nath M.
Hi Antonijo, did you ever make this report work? if yes, could you share how you made this work???
Send me you e-mail on antonijo_todorovik@hotmail.com I think i got it. There may be some minor errors, but i think it should be OK...
Kind regards, A.
Thank you for the reply. In regards to the supplier, we always use one supplier. The following was provided by a member of the forum. It works; however, it does not provide the extended description. If this could be adjusted to include the extended description, I believe this would solve our problem:
Copy this info into Notepad and save as .QRP in the Reports folder.
//--- Report Summary --- //
Begin ReportSummary
ReportType = reporttypeSales
ReportTitle = "Summary Sales Report"
PageOrientation = pageorientationPortrait
OutLineMode = True
Groups = 1
GroupDescription = ""
DisplayLogo = True
LogoFileName = "MyLogo.bmp"
ProcedureCall = ""
PreQuery1 = "IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'ViewOnOrder') DROP VIEW ViewOnOrder"
PreQuery2 = <BEGIN>
CREATE VIEW ViewOnOrder AS
SELECT itemid, sum(quantityordered - quantityreceivedtodate) AS OnOrder
FROM PurchaseOrderEntry
LEFT JOIN PurchaseOrder on PurchaseOrderEntry.purchaseorderid = PurchaseOrder.id
WHERE potype < 2
GROUP BY itemid
<END>
TablesQueried = <BEGIN>
FROM TransactionEntry INNER JOIN [Transaction] WITH(NOLOCK) ON TransactionEntry.TransactionNumber = [Transaction].TransactionNumber
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 ViewOnOrder WITH(NOLOCK) ON Item.ID = ViewOnOrder.ItemID
<END>
SelCriteria = ""
GroupBy = "Department.Name, Category.Name, Item.ItemLookupCode, Item.Description, Item.Price, Item.Quantity, Supplier.SupplierName, Item.Cost, ViewOnOrder.OnOrder, Item.SubDescription1, Item.SubDescription2, Item.SubDescription3, Item.BinLocation, Item.PriceA, Item.PriceB, Item.PriceC, Item.MSRP, Item.LastSold, Item.SalePrice, Item.SaleStartDate, Item.SaleEndDate"
SortOrder = ""
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 = "[Transaction].Time"
FilterOp = reportfilteropBetween
FilterLoLim = "<Today>"
FilterHilim = "<Today>"
End Filter
//--- Columns ---//
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 = 1725
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 = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2115
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Begin Column
FieldName = "Item.SubDescription1"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "!CaptionItemSubDescription1"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1600
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Begin Column
FieldName = "Item.SubDescription2"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "!CaptionItemSubDescription2"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1600
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Begin Column
FieldName = "Item.SubDescription3"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "!CaptionItemSubDescription3"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1600
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 = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "ViewOnOrder.OnOrder"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "On Order"
VBDataType = vbDouble
Formula = "ISNULL(ViewOnOrder.OnOrder, 0)"
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 950
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "Item.Price"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Price"
VBDataType = vbCurrency
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 720
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Item.PriceA"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Price A"
VBDataType = vbCurrency
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 750
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Item.PriceB"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Price B"
VBDataType = vbCurrency
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 750
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Item.PriceC"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Price C"
VBDataType = vbCurrency
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 750
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Item.Cost"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Cost"
VBDataType = vbCurrency
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 750
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "TransactionEntry.Quantity"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Qty Sold"
VBDataType = vbDouble
Formula = "SUM(TransactionEntry.Quantity)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 810
GroupMethod = groupmethodSum
ColFormat = "##########"
End Column
Begin Column
FieldName = "Total"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Total Sales"
VBDataType = vbCurrency
Formula = "SUM(TransactionEntry.Price * TransactionEntry.Quantity)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1275
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "TransactionEntry.Cost"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Total Cost"
VBDataType = vbCurrency
Formula = "SUM(TransactionEntry.Cost * TransactionEntry.Quantity)"
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1000
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "Profit"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Profit"
VBDataType = vbCurrency
Formula = "SUM((TransactionEntry.Price - TransactionEntry.Cost) * TransactionEntry.Quantity)"
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 950
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "ProfitMargin"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Profit Margin"
VBDataType = vbDouble
Formula = "CASE WHEN SUM(TransactionEntry.Price * TransactionEntry.Quantity) <> 0 THEN SUM((TransactionEntry.Price - TransactionEntry.Cost) * TransactionEntry.Quantity)/SUM(TransactionEntry.Price * TransactionEntry.Quantity) ELSE 0 END"
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1300
GroupMethod = groupmethodNone
ColFormat = "0.00%"
End Column
Begin Column
FieldName = "TotalDiscount"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Discount"
VBDataType = vbCurrency
Formula = "SUM((TransactionEntry.FullPrice - TransactionEntry.Price) * TransactionEntry.Quantity)"
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1220
GroupMethod = groupmethodsum
ColFormat = ""
End Column
Begin Column
FieldName = "Item.BinLocation"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Bin Location"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1440
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Begin Column
FieldName = "Item.MSRP"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "MSRP"
VBDataType = vbCurrency
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 750
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 = 1000
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 = 1000
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "[Transaction].Time"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Date Sold"
VBDataType = vbDate
Formula = ""
ColHidden = True
ColNotDisplayable = True
FilterDisabled = False
ColWidth = 900
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Hi there. The problem I see in this report is that you want to get the total quantity sold, so you need to group the transactionEntry lines, but if you add ExtendedDescripiton, which is a nText field you can't do that... On other side, items can be delivered by more then one Supplier and in that case you can't know if the item sold was delivered from one or other supplier..., so you have to assure that one item is always delivered from one and always the same supplier....
And if you do this report in external Excel file, by using VBA or similar???
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 290,902 Super User 2024 Season 2
Martin Dráb 229,316 Most Valuable Professional
nmaenpaa 101,156