Report with QOH and Sales by Month

I don't believe that there is a "canned" report (one provided with RMS) that will give you the information that you want. But, if you want a custom report written feel free to contact me at sbinder1 @ comcast.net

"Andrew" wrote:

Hi,

Is this for HQ or store ops.

Cheers

Andrew:

Add this report to your Memorized Reports as "Memorized-QOH & Item Sales.qrp" to generate the report you are looking for. This will report your current on hand quantities and sales results for the date range you specify.

//--- Report Summary --- //

Begin ReportSummary ReportType = reporttypeSales ReportTitle = "QOH & Item Sales" PageOrientation = pageorientationLandscape WordWrap = False ShowDateTimePicker = False OutLineMode = True Groups = 1 GroupDescription = "" DisplayLogo = True LogoFileName = "MyLogo.bmp" ProcedureCall = "" PreQuery1 = "" PreQuery2 = "" 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 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 ReasonCode AS ReasonCodeDiscount WITH(NOLOCK) ON TransactionEntry.DiscountReasonCodeID = ReasonCodeDiscount.ID LEFT JOIN ReasonCode AS ReasonCodeTaxChange WITH(NOLOCK) ON TransactionEntry.TaxChangeReasonCodeID = ReasonCodeTaxChange.ID LEFT JOIN ReasonCode AS ReasonCodeReturn WITH(NOLOCK) ON TransactionEntry.ReturnReasonCodeID = ReasonCodeReturn.ID LEFT JOIN Customer WITH(NOLOCK) ON [Transaction].CustomerID Customer.ID LEFT JOIN Cashier WITH(NOLOCK) ON [Transaction].CashierID Cashier.ID LEFT JOIN QuantityDiscount WITH(NOLOCK) ON TransactionEntry.QuantityDiscountID = QuantityDiscount.ID

SelCriteria = "" GroupBy = "Item.Quantity,Item.ItemLookupCode,Department.Name,Category.Name" 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 = "1/1/2002" FilterHilim = "4/5/2009" FilterNegated = False FilterConnector = reportfilterbooleanconAND End Filter

//--- Columns ---//

Begin Column FieldName = "Department.Name" DrillDownFieldName = "Department.Name" DrillDownReportName = "" StoreIDFieldName = "" Title = "Department" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1770 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column

Begin Column FieldName = "Category.Name" DrillDownFieldName = "Category.Name" DrillDownReportName = "" StoreIDFieldName = "" Title = "Category" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1410 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 = 1860 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 = "Item.Quantity" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "On Hand" VBDataType = vbDouble Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 990 GroupMethod = groupmethodSum 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 = 1170 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 = 1290 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 = 1290 GroupMethod = groupmethodSum 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

Pacific Computer Systems has been a Point of Sale systems solution provider for over 20 years. We added Microsoft RMS to our line last year and offer full evaluation, implementation and support services to our accounts.

Please let me know if you have additional requirements.

Aloha, Miles snipped-for-privacy@pacificcomp.biz

Pacific Computer Systems Miles Baidack

Supporting Business Since 1985 Phone: (808)395-8156 or (808)277-8156

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

"Andrew" wrote:

Thanks for this. This is a helpful report for planning reorders and seeing our stock-to-sales ratios.