Reporting Help: Hourly Sales Data by day of the week

(0) Share

Report

Posted on by Community Member Microsoft Employee

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

All responses (10)

Answers (0)

Community Member Microsoft Employee on at

Like (0)

Report

RE: Reporting Help: Hourly Sales Data by day of the week

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
Community Member Microsoft Employee on at

Like (0)

Report

Re: Reporting Help: Hourly Sales Data by day of the week

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,
Suggested answer

Community Member Microsoft Employee on at

Like (0)

Report

Re: Reporting Help: Hourly Sales Data by day of the week

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.
Alex R on at

Like (0)

Report

Re: Reporting Help: Hourly Sales Data by day of the week

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
Suggested answer

Community Member Microsoft Employee on at

Like (0)

Report

Re: Reporting Help: Hourly Sales Data by day of the week

add (datepart(mi,time)/30)%2 to your group by list
Alex R on at

Like (0)

Report

Re: Reporting Help: Hourly Sales Data by day of the week

Thanks for the prompt reply. They following error is display when running the report:

Regards

Alex
Community Member Microsoft Employee on at

Like (0)

Report

Re: Reporting Help: Hourly Sales Data by day of the week

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
Suggested answer

Community Member Microsoft Employee on at

Like (0)

Report

Re: Reporting Help: Hourly Sales Data by day of the week

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
Alex R on at

Like (0)

Report

Re: Reporting Help: Hourly Sales Data by day of the week

Matt, report is just what we are looking for, except can you modifiy to show time per half hour?

Regards
Suggested answer

Community Member Microsoft Employee on at

Like (0)

Report

Re: Reporting Help: Hourly Sales Data by day of the week

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