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) / Top Items Report by Mo...
Microsoft Dynamics RMS (Archived)

Top Items Report by Mother Code (Matrix Item)

(0) ShareShare
ReportReport
Posted on by Alex R

Hi There, and thanks in advance for any help.

We are looking for the standard report in Sales > Top Performers > Top Items, to group the Item Look up by Mother code but also to show standard items. 

Currently each individual matrix item of a mother code shows on the report as a seprarte line.

Many thanks, Alex

The code for the Top Items Report is as follows:

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

Begin ReportSummary    ReportType = reporttypeSales    ReportTitle = "Top Items Report"    PageOrientation = pageorientationPortrait    OutLineMode = True    Groups = 0    GroupDescription = ""    DisplayLogo = True    LogoFileName = "MyLogo.bmp"    ProcedureCall = ""    TablesQueried = "FROM TransactionEntry WITH(NOLOCK)  INNER JOIN [Transaction] WITH(NOLOCK) ON TransactionEntry.TransactionNumber = [Transaction].TransactionNumber LEFT JOIN Item WITH (NOLOCK) On TransactionEntry.ItemID = Item.ID"    SelCriteria = ""    GroupBy = "Item.ItemLookupCode"    SortOrder = "Sales DESC" 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 = "<YearStart>"    FilterHilim = "<Today>" End Filter

//--- Columns ---//

Begin Column    FieldName = "ItemLookupCode"    DrillDownFieldName = "Item.ItemLookupCode"    DrillDownReportName = ""    Title = "Item Lookup Code"    VBDataType = vbString    Formula = "MAX(Item.ItemLookupCode)"    ColHidden = False    ColNotDisplayable = False    FilterDisabled = False    ColWidth = 2055    GroupMethod = groupmethodNone    ColFormat = "" End Column

Begin Column    FieldName = "ItemDescription"    DrillDownFieldName = ""    DrillDownReportName = ""    Title = "Description"    VBDataType = vbString    Formula = "MAX(Item.Description)"    ColHidden = False    ColNotDisplayable = False    FilterDisabled = False    ColWidth = 2835    GroupMethod = groupmethodNone    ColFormat = "" End Column

Begin Column    FieldName = "[Transaction].Time"    DrillDownFieldName = ""    DrillDownReportName = ""    Title = "Date Sold"    VBDataType = vbDate    Formula = ""    ColHidden = True    ColNotDisplayable = True    FilterDisabled = False    ColWidth = 1035    GroupMethod = groupmethodNone    ColFormat = "" End Column

Begin Column    FieldName = "QtySold"    DrillDownFieldName = ""    DrillDownReportName = ""    Title = "Qty Sold"    VBDataType = vbDouble    Formula = "SUM(TransactionEntry.Quantity)"    ColHidden = False    ColNotDisplayable = False    FilterDisabled = False    ColWidth = 1710    GroupMethod = groupmethodSum    ColFormat = "#.##" End Column

Begin Column    FieldName = "Sales"    DrillDownFieldName = ""    DrillDownReportName = ""    Title = "Sales"    VBDataType = vbCurrency    Formula = "SUM(TransactionEntry.Price * TransactionEntry.Quantity)"    ColHidden = False    ColNotDisplayable = False    FilterDisabled = False    ColWidth = 1830    GroupMethod = groupmethodSum    ColFormat = "" End Column

Begin Column    FieldName = "Profit"    DrillDownFieldName = ""    DrillDownReportName = ""    Title = "Profit"    VBDataType = vbCurrency    Formula = "SUM((TransactionEntry.Price - TransactionEntry.Cost) * TransactionEntry.Quantity)"    ColHidden = False    ColNotDisplayable = False    FilterDisabled = False    ColWidth = 1815    GroupMethod = groupmethodSum    ColFormat = "" End Column

*This post is locked for comments

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

    The below query will do the job for u, try to convert it to RMS report, the idea of the query is to union 2 queries, one for the matrix items and the other one for the standard items, then select the top x items from the combination of the 2 queries by value

    select top 10 itemlookupcode,description,qty, amount

    from

    (SELECT itemclass.itemlookupcode, Itemclass.Description

    ,sum(TransactionEntry.Quantity) as Qty, sum(TransactionEntry.Quantity*TransactionEntry.Price)as Amount

    FROM [Transaction] INNER JOIN TransactionEntry

    ON [Transaction].TransactionNumber=TransactionEntry.TransactionNumber

    inner JOIN Item ON TransactionEntry.ItemID=Item.ID

    INNER JOIN ItemClassComponent ON Item.ID=ItemClassComponent.ItemID

    inner join ItemClass ON ItemClass.ID=ItemClassComponent.ItemClassID

    group by itemclass.itemlookupcode, Itemclass.Description

    union

    SELECT item.itemlookupcode, Item.Description

    ,sum(TransactionEntry.Quantity) as Qty, sum(TransactionEntry.Quantity*TransactionEntry.Price)as Amount

    FROM [Transaction] INNER JOIN TransactionEntry

    ON [Transaction].TransactionNumber=TransactionEntry.TransactionNumber

    inner JOIN Item ON TransactionEntry.ItemID=Item.ID

    left JOIN ItemClassComponent ON Item.ID=ItemClassComponent.ItemID

    where ItemClassComponent.ItemID  is null

    group by item.itemlookupcode, Item.Description

    ) t1

    order by amount desc

  • Alex R Profile Picture
    Alex R on at

    Thanks for the reply.  I'm not sure how to convert this into the RMS report.  Are you able to help on this?

    Thanks, Alex

  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    You can get Matrix reporting with the Store Utilities from Retail Realm, which you can get for free.

  • Community Member Profile Picture
    Community Member on at

    is this still available I too would like a report that groups my matrix

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