Dynamics Community Forum Thread Details

Top Items Report by Mother Code (Matrix Item)

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

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