margin calculation in Customer top 100 report AX 2012

(0) Share

Report

Posted on by Community Member

Hi All,

In the Customer top 100 SSRS report under Sales & Marketing > Reports > Statistics >Customer > Top 100, How Fields like Margin, Sales Quantity and Percentage are calculated. Please help with Table names and their respective fields as well to calculate.( I have got the amount and names from CustInvoiceJour table) But other above mentioned fields i'm not able to find.

Thanks,

JataThakur

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (0)

Sohaib Cheema 49,438 User Group Leader on at

Like (1)

Report

following path may answer all your questions, related to the report.

\Classes\smmSalesRankingDP\processReport

Was this reply helpful? Yes No
Suggested answer

Brandon Wiese 17,788 on at

Like (0)

Report

Margin is typically calculated as Sales - Cost. Margin percent is (Sales - Cost) / Sales.

Cost for a Sales order invoice line is calculated from the InventTrans records associated with the CustInvoiceTrans record, i.e. InventTrans.CostAmountPosted + InventTrans.CostAmountAdjustment.

Sales quantity is just CustInvoiceTrans.Qty, which is in the sales unit of measure.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Brandon,

I'm trying to get the Customer top 100 report through SQL query. I'm taking CUSTINVOICEJOUR Table for getting Customer Name and account. But not able to match with CUSTINVOICETRANS and INVENTTABLE. How to match it or please tel me to join on which column.

Thanks,

JataThakur

Was this reply helpful? Yes No
Brandon Wiese 17,788 on at

Like (0)

Report

CustInvoiceTrans matches to InventTable on the ItemId field, and of course DataAreaId and Partition. You can probably find this relationship in the AOT on the CustInvoiceTrans table.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Brandon,

I have tried to find the relationship, but its not working. Query which i'm using is here

SELECT ORDERACCOUNT AS CUSTOMER, INVOICINGNAME AS NAME, SUM(SALESBALANCE) AS AMOUNT

FROM CUSTINVOICEJOUR

WHERE (DATAAREAID = 'USMF')

GROUP BY ORDERACCOUNT, INVOICINGNAME

ORDER BY CUSTOMER

But after that Margin, Sales Quantity and Percent not able to get it.

Thanks,

JataThakur

Was this reply helpful? Yes No