Akber,
I liked the idea of seeing the top items in specific departments so I tried this and I am getting some errors. Here is what I have: Thanks for your help. Kevin
//--- Report Summary --- //
Begin ReportSummary ReportType = reporttypeSales ReportTitle = "Top Items Report" PageOrientation = pageorientationPortrait 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 AND TransactionEntry.StoreID = [Transaction].StoreID LEFT JOIN Item WITH (NOLOCK) ON TransactionEntry.ItemID = Item.ID LEFT JOIN Category WITH(NOLOCK) ON Item.CategoryIDÊtegory.ID LEFT JOIN Department WITH(NOLOCK) ON Item.DepartmentIDÞpartment.ID" SelCriteria = "" GroupBy = "Department.Name,Category.Name,Item.ItemLookupCode" SortOrder = "Sales DESC" 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 = "" End Filter
//--- Columns ---//
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 = 1800 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 = 2835 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "Store.ID" DrillDownFieldName = "" DrillDownReportName = "" 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 = "" 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 = "" 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 = "" 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 = "" 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 = "" 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 = "" 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 = "" 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 = 1710 GroupMethod = groupmethodSum ColFormat = "#.##" 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 = 1830 GroupMethod = groupmethodSum ColFormat = "" End Column
Begin Column FieldName = "Profit" DrillDownFieldName = "" DrillDownReportName = "" 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
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 = "Department.Name" DrillDownFieldName = "Department.Name" DrillDownReportName = "" Title = "Department" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1515 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = FlexAlignLeftCenter End Column
Thanks, Akber!
>
> "Akber Alwani" wrote:
>
>> Hi, you can do the following to achive this:
>> 1. Open the Sales -Top Items Report.grp in notepad. the file is located >> at
>> \Program Files\Microsoft Retail Management System\Store
>> Operations\Reports
>> 2. Update/Replace the Begin Reportsummary with following code:
>> 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 >> Category
>> WITH(NOLOCK) ON Item.CategoryID = Category.ID LEFT JOIN Department >> WITH(NOLOCK) ON
>> Item.DepartmentIDÞpartment.ID"
>> SelCriteria = ""
>> GroupBy = "Department.Name,Category.Name,Item.ItemLookupCode"
>> 3. Add the column underneath of Category.Name column with following code:
>> Begin Column
>> FieldName = "Department.Name"
>> DrillDownFieldName = "Department.Name"
>> DrillDownReportName = ""
>> Title = "Department"
>> VBDataType = vbString
>> Formula = ""
>> ColHidden = False
>> ColNotDisplayable = False
>> FilterDisabled = False
>> ColWidth = 1515
>> GroupMethod = groupmethodNone
>> ColFormat = ""
>> ColAlignment = flexAlignLeftCenter
>> End Column
>>
>> Now Save the file and run the report. You are accomplished what you want. >>
>> Please rate and give your comments about me.
>>
>> "gregfrgusa" wrote:
>>
>> > Hello,
>> >
>> > I am trying to run a report where the report line is a the item level, >> > with
>> > qty sold, total sales, etc.
>> >
>> > The Top items report is good, but you cannot filter by departement. >> > (eg
>> > Report the top selling items in Dept A).
>> >
>> > The Detailed sales report is OK also, and the group by item description >> > is
>> > what I am looking for, but you cannot sort by the grouped level, so I >> > cannot
>> > get the best seller to the top of the report.
>> >
>> > Thanks,
>> > Greg
>> > FRGUSA