web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics RMS (Archived) / Monthly Number of Tran...
Microsoft Dynamics RMS (Archived)

Monthly Number of Transactions report on HQ

(0) ShareShare
ReportReport
Posted on by John Robinson 314

On every Z report at the very bottom they break down the number of transactions per hour and then finally the # of transactions for the day.

I'm trying to find a way to PULL that number from HQ in a report to show me how many transactions each store had each month

It feels like I've exhausted all the report options but can not find a way to generate this information that I know RMS has.

Seems like this should be straightforward enough - not sure what I'm overlooking..

Thanks

--John Robinson

Graham Crackers Comics

*This post is locked for comments

I have the same question (0)
  • Community Member Profile Picture
    Community Member on at

    Hi John - the RMS out of the box reporting is WYSIWYG unless you know some basic SQL and have a few hours to play around with the text formats of the QRP files. Anyway - not involved with RMS anymore, but nostalgia brings me around here every now and then. Here is a store operations report with some of the info you want but its by day. See if it works for you. If it does, reply back. I'll see if I can modify it so it groups by store and month in an HQ environment. Cheers.

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

    Begin ReportSummary

      ReportType = reporttypeSales

      ReportTitle = "Store Performance Report by Day"

      PageOrientation = pageorientationPortrait

      OutLineMode = True

      Groups = 0

      GroupDescription = ""

      DisplayLogo = True

      LogoFileName = "MyLogo.bmp"

      ProcedureCall = ""

      PreQuery1 = ""

      PreQuery2 = ""

      TablesQueried = <BEGIN>

         FROM     [Transaction]

    <END>

      SelCriteria = ""

      GroupBy = "CONVERT(datetime, CONVERT(nvarchar, [Transaction].Time, 101), 101)"

      SortOrder = "CONVERT(datetime, CONVERT(nvarchar, [Transaction].Time, 101), 101) ASC"

    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 = "TransactionTime"

      FilterOp = reportfilteropBetween

      FilterLoLim = "<MonthStart>"

      FilterHilim = "<Today>"

    End Filter

    //--- Columns ---//

    Begin Column

      FieldName = "TransactionTime"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Date Sold"

      VBDataType = vbDate

      Formula = "CONVERT(datetime, CONVERT(nvarchar, [Transaction].Time, 101), 101)"

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1500

      GroupMethod = groupmethodNone

      ColFormat = ""

    End Column

    Begin Column

      FieldName = "Total"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Total Sales"

      VBDataType = vbCurrency

      Formula = "SUM(Total)"

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = True

      ColWidth = 1475

      GroupMethod = groupmethodSum

      ColFormat = ""

      ColAlignment = flexAlignCenterCenter

    End Column

    Begin Column

      FieldName = "NumTrans"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "# Transactions"

      VBDataType = vbDouble

      Formula = "COUNT(TransactionNumber)"

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1625

      GroupMethod = groupmethodNone

      ColFormat = "#"

      ColAlignment = flexAlignCenterCenter

    End Column

    Begin Column

      FieldName = "AvgTicket"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Average Ticket"

      VBDataType = vbCurrency

      Formula = "SUM(Total)/COUNT(TransactionNumber)"

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1625

      GroupMethod = groupmethodNone

      ColFormat = "$#.##"

      ColAlignment = flexAlignCenterCenter

    End Column

  • John Robinson Profile Picture
    John Robinson 314 on at

    Hi Fernando -

     Worked on a store level like a charm.   Just need it to do the same information on an HQ level but give me

    a TOTAL for the month (total # of transactions/total average ticket qty) per store.

     Would be happy to pay you for your time and trouble - you can contact me at johnrobinson@grahamcrackers.com - appreciate the trouble you've already gone to.

    thanks

    --John

  • Spencer McCandless Profile Picture
    Spencer McCandless 2,087 on at

    Fernando's right that it's going to take a custom report. If you just want the information and don't care about having it formatted into a report that can be filtered and run repeatedly, you can just open up HQ Administrator, open a new query, and run the following select statement:

    select max(store.name), max(datename(mm, [transaction].time)) as month, max(datename(yy, [transaction].time)) as year, count([transaction].transactionnumber) from [transaction] join store on [transaction].storeid = store.id group by store.name, datename(yy, [transaction].time), datepart(mm, [transaction].time) order by datename(yy, [transaction].time), datepart(mm, [transaction].time)

  • Verified answer
    John Robinson Profile Picture
    John Robinson 314 on at

    Thanks Spencer -

     Your sql query gives me exactly what I need - I'll just run it each month.

    I am still looking to hire someone to write some custom reports - is this something I should take up with Microsoft or are there any 3rd parties out there that anyone would recommend?  

    Appreciate the help!

  • Community Member Profile Picture
    Community Member on at

    John - check your email in-box for an update.

    Cheers.

  • Community Member Profile Picture
    Community Member on at

    Fernando's report worked great for me except I need two items broken out.  Store (we have multiple stores) and by hour instead of by day.  How do I edit the SQL to make this happen?  Any help would be much appreciated.

  • Community Member Profile Picture
    Community Member on at

    Habitat - try this  - hope it helps.

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

    Begin ReportSummary
    ReportType = reporttypeSales
    ReportTitle = "HQ Store Performance Report by Day"
    PageOrientation = pageorientationPortrait
    OutLineMode = True
    Groups = 1
    GroupDescription = ""
    DisplayLogo = True
    LogoFileName = "MyLogo.bmp"
    ProcedureCall = ""
    PreQuery1 = ""
    PreQuery2 = ""
    TablesQueried = <BEGIN>
    FROM [Transaction] JOIN Store ON [Transaction].StoreID = Store.ID

    <END>

    SelCriteria = ""
    GroupBy = "Store.Name, CONVERT(datetime, CONVERT(nvarchar, [Transaction].Time, 101), 101)"
    SortOrder = "CONVERT(datetime, CONVERT(nvarchar, [Transaction].Time, 101), 101) ASC"
    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 = "TransactionTime"
    FilterOp = reportfilteropBetween
    FilterLoLim = "<MonthStart>"
    FilterHilim = "<Today>"
    End Filter


    //--- Columns ---//

    Begin Column
    FieldName = "TransactionTime"
    DrillDownFieldName = ""
    DrillDownReportName = ""
    Title = "Date Sold"
    VBDataType = vbDate
    Formula = "CONVERT(datetime, CONVERT(nvarchar, [Transaction].Time, 101), 101)"
    ColHidden = False
    ColNotDisplayable = False
    FilterDisabled = False
    ColWidth = 1500
    GroupMethod = groupmethodNone
    ColFormat = ""
    End Column

    Begin Column
    FieldName = "Store.Name"
    DrillDownFieldName = ""
    DrillDownReportName = ""
    Title = "Store Name"
    VBDataType = vbString
    Formula = ""
    ColHidden = False
    ColNotDisplayable = False
    FilterDisabled = False
    ColWidth = 1475
    GroupMethod = groupmethodNone
    ColFormat = ""
    ColAlignment = flexAlignCenterCenter
    End Column


    Begin Column
    FieldName = "Sales"
    DrillDownFieldName = ""
    DrillDownReportName = ""
    Title = "Total Sales"
    VBDataType = vbCurrency
    Formula = "SUM(Total)"
    ColHidden = False
    ColNotDisplayable = False
    FilterDisabled = True
    ColWidth = 1475
    GroupMethod = groupmethodSum
    ColFormat = ""
    ColAlignment = flexAlignCenterCenter
    End Column

    Begin Column
    FieldName = "NumTrans"
    DrillDownFieldName = ""
    DrillDownReportName = ""
    Title = "# Transactions"
    VBDataType = vbDouble
    Formula = "COUNT(TransactionNumber)"
    ColHidden = False
    ColNotDisplayable = False
    FilterDisabled = False
    ColWidth = 1625
    GroupMethod = groupmethodSum
    ColFormat = "#"
    ColAlignment = flexAlignCenterCenter
    End Column

    Begin Column
    FieldName = "AvgTicket"
    DrillDownFieldName = ""
    DrillDownReportName = ""
    Title = "Average Ticket"
    VBDataType = vbCurrency
    Formula = "SUM(Total)/COUNT(TransactionNumber)"
    ColHidden = False
    ColNotDisplayable = False
    FilterDisabled = False
    ColWidth = 1625
    GroupMethod = groupmethodNone
    ColFormat = "$#.##"
    ColAlignment = flexAlignCenterCenter
    End Column

  • Community Member Profile Picture
    Community Member on at

    Fernando - thank you so much, this is very helpful!  

    I hope you don't mind me asking one more item!  Could you make this report break out sales/transactions/basket size by hour i.e. 9am-10am, 10am-11am,11am-12pm etc?  Thanks in advance!  

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics RMS (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans