Skip to main content

Notifications

Microsoft Dynamics RMS (Archived)

Reporting Help: Hourly Sales Data by day of the week

Posted on by 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

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    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 Profile Picture
    Community Member Microsoft Employee on at
    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 Profile Picture
    Community Member Microsoft Employee on at
    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 Profile Picture
    Alex R on at
    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 Profile Picture
    Community Member Microsoft Employee on at
    Re: Reporting Help: Hourly Sales Data by day of the week

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

  • Alex R Profile Picture
    Alex R on at
    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 Profile Picture
    Community Member Microsoft Employee on at
    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 Profile Picture
    Community Member Microsoft Employee on at
    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 Profile Picture
    Alex R on at
    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 Profile Picture
    Community Member Microsoft Employee on at
    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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Complete!

🔔 Be sure to subscribe to the new forums you are interested in to stay up to date! 🔔

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,902 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,336 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans