SQL Query for calculating Average Cost Price Unit from InventSum table

(0) Share

Report

Posted on by RizwanAkbarMalik

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

All responses (2)

Answers (0)

Suggested answer

Martin Dráb 230,064 Most Valuable Professional on at

Like (0)

Report

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 16,444 on at

Like (0)

Report

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