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 / Finance | Project Operations, Human Resources, ... / Get average unit cost ...
Finance | Project Operations, Human Resources, ...
Answered

Get average unit cost of an Item using X++ in D365

(0) ShareShare
ReportReport
Posted on by Rhushikesh R 752

Hi Everyone,

Need urgent help on below issue

I am trying to fetch average unit cost of Item through X code which is available on below path,

Released products > Manage costs > Cost objects

Below code I found on one of the blogs, but somehow it doesn't give perfect result for all transactions.

 public static void main(Args _args)
    {     
        ItemId                          _itemId = "2108050";
        InventSum                       inventSum;
        Amount                          avgUnitCost;
        InventDim                       inventDim;
        InventDim                       inventDimFind;
        InventCostPriceCache            inventCostPriceCache;
        UnitOfMeasureConverter_Product  productUnitConverter;
        UnitOfMeasureRecId              fromUnitMeasureRecId; 
        UnitOfMeasureRecId              toUnitMeasureRecId;
        InventTable                     inventTable;

        Amount                          costPrice, convertValue;

        select firstonly * from inventSum where inventSum.ItemId == _itemId
            join inventDim;

        if(!inventSum.InventDimId)
        {
            inventDimFind = InventDim::findDim(inventDim);
            if(inventDimFind.inventDimId)
            {
                inventSum.InventDimId = inventDimFind.inventDimId;
            }
        }

        inventCostPriceCache = InventCostPriceCache::construct();

        inventTable = inventSum.inventTable();

        fromUnitMeasureRecId    =   UnitOfMeasure::findBySymbol(inventTable.salesUnitId()).RecId;
        toUnitMeasureRecId      =   UnitOfMeasure::findBySymbol(inventTable.inventUnitId()).RecId;

        productUnitConverter  =   UnitOfMeasureConverter_Product::construct();
        
        productUnitConverter.parmProduct(inventTable.Product);
        productUnitConverter.parmFromUnitOfMeasure(fromUnitMeasureRecId);
        productUnitConverter.parmToUnitOfMeasure(toUnitMeasureRecId);
        productUnitConverter.parmRoundAbsoluteValue(NoYes::Yes);
        productUnitConverter.parmApplyRounding(NoYes::Yes);

        costPrice       = inventCostPriceCache.costPricePcs(inventSum, inventDim);
        convertValue    = productUnitConverter.convertValue(1);

        info(strFmt("costPrice %1, convertValue%2", costPrice, convertValue));


        avgUnitCost = costPrice * convertValue;

        info(strFmt("avgUnitCost %1", avgUnitCost));

        
    }

Please suggest solution on this.

I want to fetch the Average unit cost Site wise. Please refer below highlighted values.

MC_5F00_02.png

  • Rhushikesh R Profile Picture
    Rhushikesh R 752 on at
    RE: Get average unit cost of an Item using X++ in D365

    Hi Uday,

    That works perfectly. Thank you for the solution. 

  • Verified answer
    udaY-ch Profile Picture
    udaY-ch 4,643 on at
    RE: Get average unit cost of an Item using X++ in D365

    HI

    Try this code, this should work as you expect

    public class InventSumQuery
{    
    static void main(Args _args)
    {
        Qty                     totalQty;
        Query                   query;
        QueryRun                queryRun;
        QueryBuildDataSource    qbdsInventDim, qbdsInventSum;
        InventSum               inventSum;
   
        query = new query();

        qbdsInventSum = query.addDataSource(tableNum(InventSum)); 
        qbdsInventDim = qbdsInventSum.addDataSource(tableNum(InventDim));
        qbdsInventDim.relations(true);
        qbdsInventDim.joinMode(JoinMode::InnerJoin);
        qbdsInventDim.addRange(fieldNum(InventDim, InventSiteId)).value("");
        qbdsInventDim.addRange(fieldNum(InventDim, InventLocationId)).value("");
        qbdsInventDim.addRange(fieldNum(InventDim, wmsLocationId)).value("");
        qbdsInventSum.addGroupByField(fieldNum(InventSum, ItemId));

        qbdsInventSum.addSelectionField(fieldNum(InventSum, PostedQty), SelectionField::Sum);
        qbdsInventSum.addSelectionField(fieldNum(InventSum, Deducted), SelectionField::Sum);
        qbdsInventSum.addSelectionField(fieldNum(InventSum, Received), SelectionField::Sum);
        qbdsInventSum.addSelectionField(fieldNum(InventSum, PostedValue), SelectionField::Sum);
        qbdsInventSum.addSelectionField(fieldNum(InventSum, PhysicalValue), SelectionField::Sum);
    
        qbdsInventSum.addRange(fieldNum(InventSum, ItemId)).value("");

        queryrun = new QueryRun(query);

        while (queryRun.next())
        {
            queryRun.changed(tableNum(InventSum));
            {
                inventSum = queryRun.get(tableNum(InventSum));
                totalQty = inventSum.PostedQty - abs(inventSum.Deducted)   inventSum.Received;
                totalQty = abs(inventSum.PostedValue   inventSum.PhysicalValue) / totalQty;
                info(strFmt("Average unit cost - %1", totalQty));
            }
        }
    }

}

    Uday

  • udaY-ch Profile Picture
    udaY-ch 4,643 on at
    RE: Get average unit cost of an Item using X++ in D365

    Then you need to create a x++ query and group by item and sum of these fields to get the group of inventsum for the combination of inventory dimension with aggregation in it.

  • Rhushikesh R Profile Picture
    Rhushikesh R 752 on at
    RE: Get average unit cost of an Item using X++ in D365

    Hi Uday,

    the same code which you have written has been used on InventSum data source method of form InventCostOnHandItem.

    But in that method, we get the group of records in the buffer of _inventSum and not a single record.

    Your code will give the output for only single record of inventsum however, average Unit Cost price displayed on the form InventCostOnHandItem is sum of the multiple records.

  • udaY-ch Profile Picture
    udaY-ch 4,643 on at
    RE: Get average unit cost of an Item using X++ in D365

    Try this code

    var         totalQty;
InventSum   inventsum;
 
totalQty = inventSum.PostedQty - abs(inventSum.Deducted)   inventSum.Received; 
totalQty = abs(inventSum.PostedValue   inventSum.PhysicalValue) / totalQty);

  • Rhushikesh R Profile Picture
    Rhushikesh R 752 on at
    RE: Get average unit cost of an Item using X++ in D365

    Hi Uday,

    Even if I modify the select query with relation, it still doesn't give me the desired result. I modified the query like below,

    select firstonly * from inventSum where inventSum.ItemId == _itemId
            join inventDim
            where inventDim.inventDimId == inventSum.inventDimId
            && inventDim.inventDimId == "#12215326";

    But with above code as well I did not have any luck.

  • udaY-ch Profile Picture
    udaY-ch 4,643 on at
    RE: Get average unit cost of an Item using X++ in D365

    hi

    There is no relation between inventdim and inventsum? Did you notice?

    Uday

  • Rhushikesh R Profile Picture
    Rhushikesh R 752 on at
    RE: Get average unit cost of an Item using X++ in D365

    Hi Ludwig,

    The setup you mentioned is already there. I can see the data Site wise but I wanted to fetch that data through my X++ code. Hence I required help on coding part.

  • Suggested answer
    Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at
    RE: Get average unit cost of an Item using X++ in D365

    Hi,

    If you enable the financial inventory parameter in your storage dimension groups then you can get a cost price by site.

    Have you checked this setup?

    Don't you try doing something similar?

    Best regards,

    Ludwig

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

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Jonas ”Jones” Melgaard – Community Spotlight

We are honored to recognize Jonas "Jones" Melgaard as our April 2025…

Kudos to the March Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 294,430 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 233,043 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,158 Moderator

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans