Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics RMS forum

RMS Custom Report - Multiple Grouping

Posted on by Microsoft Employee

I want to customize standard report under Reports>Sales - "Detailed Sales" I want to have multiple grouping; group by "Department.Name"  (first column) and "Category.Name" (second column).

Can somebody help me with this?

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

Begin ReportSummary

  ReportType = reporttypeSales

  ReportTitle = "Detailed Sales Report"

  PageOrientation = pageorientationPortrait

  OutLineMode = True

  Groups = 1

  GroupDescription = ""

  DisplayLogo = True

  LogoFileName = "MyLogo.bmp"

  ProcedureCall = ""

  TablesQueried = <BEGIN>

     FROM     TransactionEntry INNER JOIN [Transaction] WITH(NOLOCK) ON TransactionEntry.TransactionNumber = [Transaction].TransactionNumber

        INNER JOIN  Batch WITH(NOLOCK) ON [Transaction].BatchNumber = Batch.BatchNumber

        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 Register WITH(NOLOCK) ON Batch.RegisterID = Register.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

<END>

  SelCriteria = ""

  GroupBy = ""

  SortOrder = ""

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

  FilterHilim = "<Today>"

End Filter

//--- Columns ---//

Begin Column

  FieldName = "Department.Name"

  DrillDownFieldName = "Department.Name"

  DrillDownReportName = ""

  Title = "Department"

  VBDataType = vbString

  Formula = ""

  ColHidden = False

  ColNotDisplayable = False

  FilterDisabled = False

  ColWidth = 2205

  GroupMethod = groupmethodNone

  ColFormat = ""

  ColAlignment = flexAlignLeftCenter

End Column

Begin Column

  FieldName = "Category.Name"

  DrillDownFieldName = "Category.Name"

  DrillDownReportName = ""

  Title = "Category"

  VBDataType = vbString

  Formula = ""

  ColHidden = False

  ColNotDisplayable = False

  FilterDisabled = False

  ColWidth = 1515

  GroupMethod = groupmethodNone

  ColFormat = ""

  ColAlignment = flexAlignLeftCenter

End Column

.....so on

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: RMS Custom Report - Multiple Grouping

    Hi Antonijo,

    Thank you very much!

    Regards,

  • Verified answer
    Antonijo Todorovik Profile Picture
    Antonijo Todorovik 4,025 on at
    RE: RMS Custom Report - Multiple Grouping

    Hi Babken,

    The only thing You can do is this next: Create a copy of the report file in the same folder where the original report is and rename it like this "Custom - XXXXXXX" and edit it (Notepad), by changing the "Groups = 1" line by putting "Groups = 3". Restart the Manager and look after the personalized report under Reports / Custom reports... Your report should sumarize now per Department and Category....

    Regards, A.

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 Coming on 11/8!

In our never-ending quest to help the Dynamics 365 Community members get answers faster …

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,900 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,275 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans