Skip to main content

Notifications

Microsoft Dynamics AX (Archived)

SQL Query for calculating Average Cost Price Unit from InventSum table

Posted on by

Dear All,

I am a BI architect and i don't have much knowledge of X++ methods. I need to calculate the Average Cost Price that is being shown at the Onhand inquiry. The value is being shown by a display method in InventSum table i.e. InventSum.AverageCostPriceUnit(). I need to write a SQL query to calculate the cost as I dont know much about X++. Could anyone help me write a query or advise a simple solution so that i can get the avg cost price using SQL. If anyone can help me with which tables are involved and what will be the logic then i can write the query myself. Thanks in advance. 

*This post is locked for comments

  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 230,064 Most Valuable Professional on at
    RE: SQL Query for calculating Average Cost Price Unit from InventSum table

    If you mean InventSum.averageCostPrice(), it calls quite a lot of business logic, several other methods etc. Writing the whole thing again and maintaining all the duplicated code outside AX doesn't look efficient.

    A better option would be letting AX calculate the values and exporting them on periodic bases. AX uses data entities for data export (you can create a new one if there is none suitable), which can be exported to the entity store. Then you can take data for your BI solution from there.

  • Suggested answer
    Denis Macchinetti Profile Picture
    Denis Macchinetti 16,444 on at
    RE: SQL Query for calculating Average Cost Price Unit from InventSum table

    Hi

    Replicate the Business logic outside AX isn't the best practice.

    So, you have two solutions:

    1-Within SSIS use the Business Connector in order to call directly the AX methods

    2-Using an AIF Custom Service, technet.microsoft.com/.../hh509052.aspx

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,232 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,064 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans