Hi Everyone,
I am very new to this community. thank you very much in advance if you can enlighten me with your knowledge.
My boss asks me to create a Margin report for each product ( ITEMID). my thinking was to get invoice value from SALESLINE table then get cost from INVENTTRANS table, in which case I could calculate margin. I took one of the sales orders as an example trying to work out how to get Invoice amount (LINEAMOUNT) as well as cost (COSTAMOUNTSETTLED). It turns out multiple sales line and cost line. does anyone know how to combine multiple lines into one?
*This post is locked for comments
Glad it did. Try to look at SQL syntax in general so you have better idea until you work with AX2009. Once you move to D365 there are other tools available that you can use.
Hi Satish,
I don't know how, but it works :) brilliant. thank you ever so much.
Hi mangosteenlu ,
The ItemId is present in CustInvoiceTrans table. The only issue is you can calculate the margin only for invoiced orders and not for open orders.
In general, single sales line can have multiple inventTrans. The tables are joined on item id and inventTransId field. You need to do something like this. Below is
Select itemid, SALESQTY, lineamount, (select sum(costamountposted) from inventtrans IT where IT.Itemid = SL.itemid and IT.dataAreaId = SL.DataAreaId and
IT.InventTransId = SL.InventTransId) from salesline SL
Once you have your numbers, you need to look at this query again to make sure you add any other adjustments etc. and start comparing the numbers with what you see in AX.
Thanks,
Satish Panwar
Please take time to click 'Yes' against the answers that help you guide in right direction to help other community members.
Hi Rustem,
thank you very much for your prompt response. CUSTINVOICETRANS table does not have ITEMID attached to each invoice.
On my screenshot, the first 6 columns were from SALESLINE table, the last column was from INVENTTRANS table.
In order to create a Margin report for each ITEMID, I need ITEMID, SHIPPING DATE, SALES ORDER ID, and INVOICE AMOUNT from SALESLINE table, then the COST AMOUNT from INVENTTRANS table.
however there are multiple lines for each tables, and I am not sure how to get rid of multiple lines as I only need one line for each sales orders.
Thank you
Hi mangosteenlu!
Why you are trying to get invoice value from the SalesLine table instead of CustInvoiceTrans (invoice line table) ?
From what table are records on screenshot ?
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 290,802 Super User 2024 Season 2
Martin Dráb 229,129 Most Valuable Professional
nmaenpaa 101,154