web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Developping Stock valu...
Microsoft Dynamics AX (Archived)

Developping Stock value report via SQL

(0) ShareShare
ReportReport
Posted on by JauB 352

Hello

Can someone help me to develop some SQL request which give the same result as Stock value report ? My need is to get the stock valued by warehouse. I think that AX is making many calculations to get this value in Stock value report ...

Thanks.

*This post is locked for comments

I have the same question (0)
  • Guy Terry Profile Picture
    Guy Terry 28,924 Moderator on at

    It should be possible to get this for the current point in time, by adding the cost amount of records in the InventTrans table. If both 'Post physical inventory' and 'Post financial inventory' are ticked on the Inventory model group, then you will be interested in only Inventory Transactions where the Receipt status is 0 or 1 or 2 and the Issue status is 0 or 1 or 2. You would need to bring in the InventDim table (join on InventDimId) to find the warehouse of each transaction.

  • JauB Profile Picture
    JauB 352 on at

    Thanks Terry

    How to add cost amount ? is it a field that I can get from InventTrans  table ? Or I have to calculate it ? in this case to procedd for calculation ?

  • Suggested answer
    Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at

    Hi JauB,

    Why don't you use the inventory value report, which gives you the stock value by warehouse (and other inventory dimensions)?

    I can't see the necessity to do some development here.

    Best regards,

    Ludwig

  • JauB Profile Picture
    JauB 352 on at

    Hi Ludwig

    My need is to complete this report with several new fields and send it by email throw a specific application to some users who don't have an access to AX (like our partners...)

    Thanks

  • Guy Terry Profile Picture
    Guy Terry 28,924 Moderator on at

    Hi JauB,

    If the Issue or Receipt status of the transaction is '2', the cost amount is:

    InventTrans.CostAmountPhysical + InventTrans.CostAmountAdjustment

    If the Issue or Receipt status of the transaction is '1', the cost amount is:

    InventTrans.CostAmountPosted + InventTrans.CostAmountAdjustment

  • JauB Profile Picture
    JauB 352 on at

    Hi Terry

    I did what you said in this way :

    select itemid, sum(qty) as Qty, SUM(CASE WHEN STATUSRECEIPT = 2 THEN InventTrans.CostAmountPhysical + InventTrans.CostAmountAdjustment WHEN STATUSRECEIPT = 1 THEN InventTrans.CostAmountPosted + InventTrans.CostAmountAdjustment ELSE 0 END) as Value
from InventTrans
where itemid = '70002026000201'
GROUP BY ITEMID


    But this SQL request return 702 as Quantity and 86 183 as value.

    When I execute Stcok value report i get for this item :

    200 as Quantity and 4 900 as value and 24,50 as Average unit cost.

    What can be the issue ?

    Thanks. 

  • Guy Terry Profile Picture
    Guy Terry 28,924 Moderator on at

    [quote user="GuyUK"]

    you will be interested in only Inventory Transactions where the Receipt status is 0 or 1 or 2 and the Issue status is 0 or 1 or 2

    [/quote]

    Something like this on the 'WHERE'?:

    AND InventTrans.StatusIssue IN (0,1,2) AND InventTrans.StatusReceipt IN (0,1,2)

    It's also good practise to get into the habit of including Company Id on any SQL you write against AX data. Again, on the WHERE:

    AND InventTrans.DataAreaId = 'YOURAXCOMPANYID'

    You should add DataAreaId even if you only have one company, because all the SQL indexes will have it.

  • JauB Profile Picture
    JauB 352 on at

    With the new modification, i can see that the quantity is good now, but not the value.

    This new SQL request :

    select itemid, sum(qty) as Qty, SUM(CASE WHEN STATUSRECEIPT = 2 THEN InventTrans.CostAmountPhysical + InventTrans.CostAmountAdjustment WHEN STATUSRECEIPT = 1 THEN InventTrans.CostAmountPosted + InventTrans.CostAmountAdjustment ELSE 0 END) as Value
from InventTrans
where itemid = '70002026000201'
AND InventTrans.StatusIssue IN (0,1,2) AND InventTrans.StatusReceipt IN (0,1,2)
AND  InventTrans.DataAreaId ='difa'
GROUP BY ITEMID


    The Quantity is 200 like in the standard report, but the value returned is 86 183 instead of 4 900 !

  • Guy Terry Profile Picture
    Guy Terry 28,924 Moderator on at

    Yeah, you need to work on your CASE WHEN. You're taking the cost of all your issue transactions as zero. What if the StatusReceipt = 0 and StatusIssue = 1 or 2?

  • JauB Profile Picture
    JauB 352 on at

    I didn't get your point. Can you tell me more ? because i don't know what means these fields Receipt and issue status. in my CASE i'm testing only on STATUSRECEIPT, do I have to test on StatusIssue  too ? How ? :)

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans