Retrieve customer revenue

Question

I want to create a report with the top 20 customers (based on revenue).

I am using the query:

SELECT dbo.CustTable.AccountNum
,dbo.dirpartytable.NAME
,dbo.hcmworker.PERSONNELNUMBER
,dbo.CustInvoiceJour.SALESBALANCE
,dbo.custinvoicejour.QTY
FROM dbo.CustTable
inner JOIN dbo.HCMWORKER ON dbo.HCMWORKER.RECID = dbo.CustTable.KEV_Worker
inner join dbo.custInvoiceJour on CustInvoiceJour.OrderAccount = CustTable.AccountNum
inner join dbo.dirpartytable on dirpartytable.recid = custtable.PARTY
where CustTable.KEV_Worker = '5633561745'
ORDER BY SalesBalanceMst DESC

I can't find the relation for the customer revenue, after all, that is how I want to sort the report. I am sorting on SalesBalanceMST right now while building the report. Also I am getting multiple records when executing this query.

What am i doing wrong?

EDIT: I now realize I am showing each Invoice Journal, how can I display the Total Revenue of the customer?

Answer 1

A similar search from AX 2012:

CustInvoiceJour CustInvoiceJour;
CustTable CustTable;
DirPartyTable DirPartyTable;
select forceLiterals generateonly sum(SalesBalanceMST), sum(Qty) from CustInvoiceJour
    where CustInvoiceJour.OrderAccount == '102372200'
       && CustInvoiceJour.InvoiceDate  >  today()-365
    join TableId from CustTable
    group AccountNum
    where CustTable.AccountNum == CustInvoiceJour.OrderAccount
    join TableId from DirPartyTable
    group Name
    where DirPartyTable.RecId == CustTable.Party;
info(CustInvoiceJour.getSQLStatement());

This shows the following SQL:

SELECT SUM(T1.SALESBALANCEMST),SUM(T1.QTY),T2.ACCOUNTNUM,T3.NAME 
FROM CUSTINVOICEJOUR T1 
CROSS JOIN CUSTTABLE T2 
CROSS JOIN DIRPARTYTABLE T3 
WHERE (((T1.PARTITION=5637144576) AND (T1.DATAAREAID=N'xxx')) 
AND ((T1.ORDERACCOUNT=N'102372200') 
AND (T1.INVOICEDATE>{ts '2015-11-06 00:00:00.000'}))) 
AND (((T2.PARTITION=5637144576) AND (T2.DATAAREAID=N'xxx')) 
AND (T2.ACCOUNTNUM=T1.ORDERACCOUNT)) 
AND ((T3.PARTITION=5637144576) 
AND (T3.RECID=T2.PARTY))    
GROUP BY T2.ACCOUNTNUM,T3.NAME 
ORDER BY T2.ACCOUNTNUM,T3.NAME 

What is different from your query:

• no join on HcmWorker, as I do not have your custom field.
• Using sum() to aggregate
• selecting on InvoiceDate
• selection on OrderAccount
• selection on DataAreaId, really important for performance, implicit in AX
• selection on Partition, really important for performance, implicit in AX

You cannot directly sort on a sum, but may on a nested SQL query.