In RMS 2, How do I generate an hourly sales report across multiple days that I can export to analyze? I need to evaluate hourly sales by day of the week, and total sales by day of the week for staffing purposes.
I am very familiar with the built in reports in manager, but don't know of any way to do it through them.
Thanks,
Tim B.
*This post is locked for comments
Mabuhay!
Hi! I tried this code and it will show the details filtered. Is there any chance that i will filter via hourly sales?
Like 9:00AM - 12:00PM with same date.
Thanks,
Chris
I am not sure how to use this query. Is this done by creating a new query in 'Administrator'? If so, in V2.0, would the query work with SQL Express 2005 and SQL 2008? it doesn't work for me. Thanks,
Put the hour column back in, and it will show both hour sold and the half hour (0 for first 30 min and 1 for last half of hour). Your actual finished product will vary depending on your specific requirements. The formula in the code was a suggestion of how to calculate which half of the hour sales were made in. If you are not able to customize for your specific requirements, you may need to pay someone to help you.
Hi Matt, report shows as follows, so not sure what has gone wrong. Pasted report code below. Regards Alex
//--- Report Summary --- //
Begin ReportSummary
ReportType = reporttypeSales
ReportTitle = "Sales Summary Store by Date"
PageOrientation = pageorientationLandscape
WordWrap = False
ShowDateTimePicker = False
OutLineMode = True
Groups = 0
GroupDescription = ""
DisplayLogo = True
LogoFileName = "MyLogo.bmp"
ProcedureCall = ""
PreQuery1 = ""
PreQuery2 = ""
TablesQueried = <BEGIN>
FROM TransactionEntry
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 Department WITH(NOLOCK)
ON Item.DepartmentID = Department.ID
<END>
SelCriteria = ""
GroupBy = "Transactionentry.StoreID,convert(nvarchar,month(time))+'/'+convert(nvarchar,day(time))+'/'+convert(nvarchar,year(time)),datepart(hh,time),datename(dw,time),(datepart(mi,time)/30)%2"
SortOrder = "Transactionentry.StoreID,convert(nvarchar,month(time))+'/'+convert(nvarchar,day(time))+'/'+convert(nvarchar,year(time)),datepart(hh,time)"
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 = <MonthStart>
FilterHilim = <Today>
FilterNegated = False
FilterConnector = reportfilterbooleanconAND
End Filter
//--- Columns ---//
Begin Column
FieldName = "Transactionentry.StoreID"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Store ID"
VBDataType = vbLong
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 870
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Date"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Date"
VBDataType = vbDate
Formula = "convert(nvarchar,month(time))+'/'+convert(nvarchar,day(time))+'/'+convert(nvarchar,year(time))"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = True
ColWidth = 900
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "HalfHour"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "HalfHour"
VBDataType = vbInteger
Formula = "(datepart(mi,time)/30)%2"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = True
ColWidth = 900
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Weekday"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Weekday"
VBDataType = vbString
Formula = "datename(dw,time)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = True
ColWidth = 900
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Total"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Total Sales"
VBDataType = vbCurrency
Formula = "Sum(TransactionEntry.Price * TransactionEntry.Quantity)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1605
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "Department.Name"
DrillDownFieldName = "Department.Name"
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Department"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = True
FilterDisabled = False
ColWidth = 2205
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Begin Column
FieldName = "[Transaction].Time"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Date Sold"
VBDataType = vbDate
Formula = ""
ColHidden = True
ColNotDisplayable = True
FilterDisabled = False
ColWidth = 900
GroupMethod = groupmethodNone
ColFormat = ""
End Column
add (datepart(mi,time)/30)%2 to your group by list
Thanks for the prompt reply. They following error is display when running the report:
Regards
Alex
Begin Column
FieldName = "HalfHour"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "HalfHour"
VBDataType = vbInteger
Formula = "(datepart(mi,time)/30)%2"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = True
ColWidth = 900
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "HalfHour"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Hour"
VBDataType = vbInteger
Formula = "(datepart(mi,time)/30)%2"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = True
ColWidth = 900
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Matt, report is just what we are looking for, except can you modifiy to show time per half hour?
Regards
You could probably use this report to export to excel for graphs or pivot table etc.
After copy and paste into notepad, you will need to repair the lines that wrapped.
//--- Report Summary --- //
Begin ReportSummary
ReportType = reporttypeSales
ReportTitle = "Sales Summary Store by Date"
PageOrientation = pageorientationLandscape
WordWrap = False
ShowDateTimePicker = False
OutLineMode = True
Groups = 0
GroupDescription = ""
DisplayLogo = True
LogoFileName = "MyLogo.bmp"
ProcedureCall = ""
PreQuery1 = ""
PreQuery2 = ""
TablesQueried = <BEGIN>
FROM TransactionEntry
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 Department WITH(NOLOCK)
ON Item.DepartmentID = Department.ID
<END>
SelCriteria = ""
GroupBy = "Transactionentry.StoreID,convert(nvarchar,month(time))+'/'+convert(nvarchar,day(time))+'/'+convert(nvarchar,year(time)),datepart(hh,time),datename(dw,time)"
SortOrder = "Transactionentry.StoreID,convert(nvarchar,month(time))+'/'+convert(nvarchar,day(time))+'/'+convert(nvarchar,year(time)),datepart(hh,time)"
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 = <MonthStart>
FilterHilim = <Today>
FilterNegated = False
FilterConnector = reportfilterbooleanconAND
End Filter
//--- Columns ---//
Begin Column
FieldName = "Transactionentry.StoreID"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Store ID"
VBDataType = vbLong
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 870
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Date"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Date"
VBDataType = vbDate
Formula = "convert(nvarchar,month(time))+'/'+convert(nvarchar,day(time))+'/'+convert(nvarchar,year(time))"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = True
ColWidth = 900
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Hour"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Hour"
VBDataType = vbInteger
Formula = "datepart(hh,time)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = True
ColWidth = 900
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Weekday"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Weekday"
VBDataType = vbString
Formula = "datename(dw,time)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = True
ColWidth = 900
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Begin Column
FieldName = "Total"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Total Sales"
VBDataType = vbCurrency
Formula = "Sum(TransactionEntry.Price * TransactionEntry.Quantity)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1605
GroupMethod = groupmethodSum
ColFormat = ""
End Column
Begin Column
FieldName = "Department.Name"
DrillDownFieldName = "Department.Name"
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Department"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = True
FilterDisabled = False
ColWidth = 2205
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Begin Column
FieldName = "[Transaction].Time"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Date Sold"
VBDataType = vbDate
Formula = ""
ColHidden = True
ColNotDisplayable = True
FilterDisabled = False
ColWidth = 900
GroupMethod = groupmethodNone
ColFormat = ""
End Column
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 290,902 Super User 2024 Season 2
Martin Dráb 229,336 Most Valuable Professional
nmaenpaa 101,156