Dynamics Community Forum Thread Details

I have the following report which is a modified version of the Top Performers, Top Items report from HQ manager...

----------------------------------------------------------------------------------------------------------------------------------------------------------

//--- 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

----------------------------------------------------------------------------------------------------------------------------------------------------------

This report is all good and fine as it lets me sort by the quantity sold for a given period of time however what I need is say only the top 10 selling items in each category. Any idea on how to do this? I would imagine something has to be entered into the select criteria but I cannot seem to figure out the correct syntax. Any help anyone can offer is greatly appreciated...thanks in advance.

Chris