|
Posted by Kevin on March 5, 2007, 2:55 pm
Please log in for more thread options
> 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 = <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 Category WITH(NOLOCK) ON Item.CategoryID=Category.ID
> LEFT JOIN Department WITH(NOLOCK) ON
> Item.DepartmentID=Department.ID"
> <END>
> SelCriteria = ""
> GroupBy = "Department.Name,Category.Name,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 = "<YearStart>"
> FilterHilim = "<Today>"
> 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 FilesMicrosoft Retail Management SystemStore
>>> OperationsReports
>>> 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=Department.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
>
>
| 
Yahoo! 
Google 
Windows Live 
del.icio.us 
digg 
Netscape 
XML Sitemap