You’re offline. This is a read only version of the page.
I'm looking for a report that will show the top 10 selling items by department. Anyone have something custom that they are willing to share?
*This post is locked for comments
@ FERNANDO
Thank you so dear much for the codes. It has been very useful and very helpful. Thank you.
However, can you also write a report that will show the followings:
Item lookup codes,
Opening Quantity
Transfer in (as in goods received in the store)
Transfer out (as in goods that have gone out of the store)
Closing stock
Thanks.
perfect Fernando!! I may tweak it out a bit to group by department but other than that it's perfect. Thanks so much.
Hi SammySue - here is a report that will limit the output to ten - you can change the output in the filter at run time in deptrank, so if you want to see the top 20, you change the filter.click the department column header to sort by department. Hope this helps.
//--- Report Summary --- //
Begin ReportSummary
ReportType = reporttypeSales
ReportTitle = "Top Ranked Items By Department"
PageOrientation = pageorientationLandscape
OutLineMode = True
Groups = 0
GroupDescription = ""
DisplayLogo = True
LogoFileName = "MyLogo.bmp"
ProcedureCall = ""
TablesQueried = "FROM TransactionEntry WITH(NOLOCK) INNER JOIN [Transaction] WITH(NOLOCK) ON TransactionEntry.TransactionNumber = [Transaction].TransactionNumber LEFT JOIN Item WITH (NOLOCK) On TransactionEntry.ItemID = Item.ID LEFT JOIN Department on Item.DepartmentID = Department.ID"
SelCriteria = ""
GroupBy = "Department.Name, Item.ItemLookupCode"
SortOrder = "QtySold DESC"
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 = "Profit Margin is as Percent of Price"
Font = "Arial"
FontBold = True
FontSize = 8
Color = "Black"
End TitleRow
Begin TitleRow
Text = "Generated On <Report Date>"
Font = "Arial"
FontBold = True
FontSize = 8
Color = "Black"
End TitleRow
//--- Filters ---//
Begin Filter
FieldName = "[Transaction].Time"
FilterOp = reportfilteropBetween
FilterLoLim = "<YearStart>"
FilterHilim = "<Today>"
End Filter
Begin Filter
FieldName = "DeptRank"
FilterOp = reportfilteropBetween
FilterLoLim = "1"
FilterHilim = "10"
End Filter
//--- Columns ---//
Begin Column
FieldName = "DepartmentName"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Department"
VBDataType = vbString
Formula = "MAX(Department.Name)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2000
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "ItemLookupCode"
DrillDownFieldName = "Item.ItemLookupCode"
DrillDownReportName = ""
Title = "Item Lookup Code"
VBDataType = vbString
Formula = "MAX(Item.ItemLookupCode)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1855
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "ItemDescription"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Description"
VBDataType = vbString
Formula = "MAX(Item.Description)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2535
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "SubDesc1"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Size"
VBDataType = vbString
Formula = "MAX(Item.SubDescription1)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 800
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 = 1035
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "QtySold"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Qty Sold"
VBDataType = vbDouble
Formula = "SUM(TransactionEntry.Quantity)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 990
GroupMethod = groupmethodSum
ColFormat = "#"
ColAlignment = flexAlignCenterCenter
End Column
Begin Column
FieldName = "Sales"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Sales"
VBDataType = vbCurrency
Formula = "SUM(TransactionEntry.Price * TransactionEntry.Quantity)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1130
GroupMethod = groupmethodSum
ColFormat = ""
ColAlignment = flexAlignCenterCenter
End Column
Begin Column
FieldName = "COGS"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "COGS"
VBDataType = vbCurrency
Formula = "SUM(TransactionEntry.Cost * TransactionEntry.Quantity)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1130
GroupMethod = groupmethodSum
ColFormat = ""
ColAlignment = flexAlignCenterCenter
End Column
Begin Column
FieldName = "Profit"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Margin $"
VBDataType = vbCurrency
Formula = "SUM((TransactionEntry.Price - TransactionEntry.Cost) * TransactionEntry.Quantity)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1115
GroupMethod = groupmethodSum
ColFormat = ""
ColAlignment = flexAlignCenterCenter
End Column
Begin Column
FieldName = "ProfitMargin"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "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 = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 950
GroupMethod = groupmethodNone
ColFormat = "0.00%"
End Column
Begin Column
FieldName = "DeptRank"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Dep Rank"
VBDataType = vbDouble
Formula = "dense_rank() over (partition by name order by sum(transactionentry.quantity) desc)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 950
GroupMethod = groupmethodNone
ColFormat = "#"
ColAlignment = flexAlignCenterCenter
End Column
Begin Column
FieldName = "Item.LastSold"
DrillDownFieldName = ""
DrillDownReportName = ""
Title = "Last Sold"
VBDataType = vbDate
Formula = "MAX(Item.LastSold)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1035
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Hi Sammy,
Are you looking for this report for Store Ops or Headquarters? There will be some differences between the two. Below I've posted the correct custom report for Headquarters.
//--- Report Summary --- //
Begin ReportSummary
ReportType = reporttypeSales
ReportTitle = "Top Items Report"
PageOrientation = pageorientationPortrait
WordWrap = False
ShowDateTimePicker = False
OutLineMode = True
Groups = 0
GroupDescription = ""
DisplayLogo = True
LogoFileName = "MyLogo.bmp"
ProcedureCall = ""
PreQuery1 = ""
PreQuery2 = ""
TablesQueried = <BEGIN>
FROM TransactionEntry WITH(NOLOCK) INNER JOIN [Transaction] WITH(NOLOCK) ON TransactionEntry.TransactionNumber = [Transaction].TransactionNumber and [Transaction].StoreID = TransactionEntry.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
<END>
SelCriteria = ""
GroupBy = "Item.ItemLookupCode"
SortOrder = "Sales DESC"
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 = "1/1/2013"
FilterHilim = "11/13/2013"
FilterNegated = False
FilterConnector = reportfilterbooleanconAND
End Filter
Begin Filter
FieldName = "SupplierName"
FilterOp = reportfilteropLike
FilterLoLim = "herb company"
FilterHilim = "herb company"
FilterNegated = False
FilterConnector = reportfilterbooleanconAND
End Filter
//--- Columns ---//
Begin Column
FieldName = "ItemDescription"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Description"
VBDataType = vbString
Formula = "MAX(Item.Description)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2835
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "SupplierName"
DrillDownFieldName = "Supplier.SupplierName"
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Supplier"
VBDataType = vbString
Formula = "MAX(Supplier.SupplierName)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2835
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "ItemLookupCode"
DrillDownFieldName = "Item.ItemLookupCode"
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Item Lookup Code"
VBDataType = vbString
Formula = "MAX(Item.ItemLookupCode)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2055
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Price"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Price"
VBDataType = vbString
Formula = "MAX(Item.Price)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2835
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "QtySold"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Qty Sold"
VBDataType = vbDouble
Formula = "SUM(TransactionEntry.Quantity)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1710
GroupMethod = groupmethodSum
ColFormat = "#.##"
End Column
Begin Column
FieldName = "Sales"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Sales"
VBDataType = vbCurrency
Formula = "SUM(TransactionEntry.Price * TransactionEntry.Quantity)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1830
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "Profit"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Profit"
VBDataType = vbCurrency
Formula = "SUM((TransactionEntry.Price - TransactionEntry.Cost) * TransactionEntry.Quantity)"
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1815
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "Department.Name"
DrillDownFieldName = "Department.Name"
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Department"
VBDataType = vbString
Formula = "MAX(Department.Name)"
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2205
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Category.Name"
DrillDownFieldName = "Category.Name"
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Category"
VBDataType = vbString
Formula = "MAX(Category.Name)"
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2205
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 = 1035
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Thanks. This works as a Top Item Report but doesn't limit just the top 10 selling items by category or department.
Hi. I never tested the above code but to make use of it, copy and save the above code to notepad. Rename and save to reports folder of RMS. Let me know if this helps. tnx.
There has to be a way to run the top 10 selling items by department report. I know that it's just a sql query. I just don't know the query.
Or else use below report
//--- Report Summary --- //
Begin ReportSummary
ReportType = reporttypeSales
ReportTitle = "Top Items Report by Category"
PageOrientation = pageorientationPortrait
WordWrap = False
ShowDateTimePicker = False
OutLineMode = True
Groups = 0
GroupDescription = ""
DisplayLogo = True
LogoFileName = "MyLogo.bmp"
ProcedureCall = ""
PreQuery1 = ""
PreQuery2 = ""
TablesQueried = <BEGIN>
FROM TransactionEntry WITH(NOLOCK)
INNER JOIN [Transaction] WITH(NOLOCK)
ON TransactionEntry.TransactionNumber = [Transaction].TransactionNumber
AND TransactionEntry.StoreID = [Transaction].StoreID
LEFT JOIN Item WITH (NOLOCK)
ON TransactionEntry.ItemID = Item.ID
LEFT JOIN Store ON TransactionEntry.StoreID = Store.ID
LEFT JOIN Department WITH (NOLOCK)
ON Item.DepartmentID = Department.ID
LEFT JOIN Category WITH (NOLOCK)
ON Item.CategoryID = Category.ID
<END>
SelCriteria = ""
GroupBy = "Item.ItemLookupCode,Department.Name,Category.Name"
SortOrder = "Sales DESC"
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 = "1-1-2013"
FilterHilim = "9-9-2013"
FilterNegated = False
FilterConnector = reportfilterbooleanconAND
End Filter
//--- Columns ---//
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 = "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 = "ItemLookupCode"
DrillDownFieldName = "Item.ItemLookupCode"
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Item Lookup Code"
VBDataType = vbString
Formula = "MAX(Item.ItemLookupCode)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1800
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "ItemDescription"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Description"
VBDataType = vbString
Formula = "MAX(Item.Description)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2835
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Store.ID"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Store ID"
VBDataType = vbLong
Formula = ""
ColHidden = True
ColNotDisplayable = True
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 = True
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 = True
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 = True
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 = True
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 = True
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 = True
FilterDisabled = False
ColWidth = 1560
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 = 1035
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "QtySold"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Qty Sold"
VBDataType = vbDouble
Formula = "SUM(TransactionEntry.Quantity)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1710
GroupMethod = groupmethodSum
ColFormat = "#.##"
End Column
Begin Column
FieldName = "Sales"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Sales"
VBDataType = vbCurrency
Formula = "SUM(TransactionEntry.Price * TransactionEntry.Quantity)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1830
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "Profit"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Profit"
VBDataType = vbCurrency
Formula = "SUM((TransactionEntry.Price - TransactionEntry.Cost) * TransactionEntry.Quantity)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1815
GroupMethod = groupmethodSum
ColFormat = ""
End Column
As for top ten i don't think it will work,but you can use sales report:
Reports>sales>top performers>top items
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (