Open Forum

Like what you see? Discover the benefits of the D365UG/AXUG Community. Learn More

1.  Sales Orders/Gross Margin - AX2012 R2

Posted 9 days ago
Hello All,

Trying to determine the gross margin on sales.  I looked the gross margin by item report and there is a field called 'cost value' but it returns all zeros if you run a query over the same field in the custinvoicetrans table.

Is there another way to capture the cost that you are aware of?

Thanks,

Sam

------------------------------
Sam Clark
Business Systems Analyst
Team Technologies
Morristown TN
------------------------------


2.  RE: Sales Orders/Gross Margin - AX2012 R2

Posted 9 days ago

Hi Sam,


I can't see a Cost value field on the Invoice line table. If you look on the Invoice journal inquiry, there is a margin field on the 'Lines' tab, but this is a calculated field.

Are you wanting to see this on a report in AX, or for something external, like BI?



------------------------------
Guy Terry
Application Consultant
ibrl
United Kingdom
------------------------------



3.  RE: Sales Orders/Gross Margin - AX2012 R2

Posted 7 days ago
Hello @Guy Terry,

I'm trying to get the value to use in Power BI, but the 'cost value' field doesn't seem to do the job.  I was looking for another way to get the info.

Ideas?

Thanks,

Sam

------------------------------
Sam Clark
Business Systems Analyst
Team Technologies
Morristown TN
------------------------------



4.  RE: Sales Orders/Gross Margin - AX2012 R2

Posted 6 days ago
R2 ships with some default SSAS cubes. I'd be tempted to use them as a datasource for PowerBI. The 'Sales cube' probably has what you want. 'Cost of goods sold – accounting currency' seems to be it.

Sales cube (SalesCube) for Microsoft Dynamics AX 2012 R2 and R3 [AX 2012]

Otherwise, to get the cost you need to reach out to the Inventory transaction. InventTransOrigin is related to the CustInvoiceTrans table by InventTransId, but I'd suggest you'd test to see how that holds up in your scenarios (because of how it's technically possible to put multiple sales orders on to one invoice, and invoice a single order line across multiple invoice lines).

From the Inventory transaction, the fields you want are CostAmountPosted and CostAmountAdjustment... add them together to get the cost.

If this all sounds a bit much, you could hijack something that was designed for the SSAS cube. There is an AX View called CustInvoiceTransExpanded. It's used by that Sales Cube, but there's no reason you couldn't use it. It already has a COGS field.....it's done the hard work for you :-)

------------------------------
Guy Terry
Application Consultant
ibrl
United Kingdom
------------------------------



5.  RE: Sales Orders/Gross Margin - AX2012 R2

Posted 6 days ago
Thanks @Guy Terry!

Great info!

Sam

------------------------------
Sam Clark
Business Systems Analyst
Team Technologies
Morristown TN
------------------------------



6.  RE: Sales Orders/Gross Margin - AX2012 R2

Posted 9 days ago
​Hi,
I double checked the 'gross margin by item' report and can identify the cost value and the margin that AX calculates.
When you have this report open there should be a link to the invoice.
Please follow this link, which will open the invoice journal form.
This form has an overview and lines tab and from the latter one you can dig deeper into your transactions and why no cost price is shown.
Best regards,
Ludwig

------------------------------
Ludwig Reinhard
SYCOR GROUP
Goettingen
------------------------------



'