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) / Item inventory purchas...
Microsoft Dynamics AX (Archived)

Item inventory purchase history with average cost for previous and current PO?

(0) ShareShare
ReportReport
Posted on by Rana Anees 1,811

Hii, Let me explain what I want..

I want to show in SSRS report or a Form, following information item wise and for purchase orders only.

Meaning I want to get Items purchase history after the invoicing.

What I want on this report is,
1. Total Qty in stock from previous purchase order if any
2. Average cost price from previous purchase order if any
3. Financial cost for the latest purchase order
4. Total Qty in latest PO
5. Average cost price including this purchase order + previous purchase order stock

How it will work

After invoicing a purchase order, a user will open this report and filter by item,
This report will show item's Purchase history along with what was the total Qty, Price, Avg Price of the Previous Purchase Order in the sale line..
Means how much Qty came in and what is the cost price, its average cost along with what was the Qty, cost price, average cost price from the previous purchase order.

Please guide is there any existing for or report available or

Tables and classes or other objects to explore..

Thanks,




*This post is locked for comments

  • Rana Anees Profile Picture
    Rana Anees 1,811 on at
    RE: Item inventory purchase history with average cost for previous and current PO?

    Our  'Last price history' Inventory parameter is not active. But 'Latest cost price' in Managed Cost tab on Released products is active.

  • Rana Anees Profile Picture
    Rana Anees 1,811 on at
    RE: Item inventory purchase history with average cost for previous and current PO?

    Thanks All,

    Here is my first attempt. RDP processReport and temp table. Need your review. Thanks.

     while select *
            from _inventTransOrigin
                where _inventTransOrigin.ReferenceCategory == 3
                    join _inventTrans
                        where _inventTransOrigin.RecId == _inventTrans.InventTransOrigin
                                join _inventDim
                                    where _inventTrans.inventDimId == _inventDim.inventDimId
                                        join _vendInvoiceTrans
                                            where _inventTransOrigin.InventTransId == _vendInvoiceTrans.InventTransId
        {

            CurrentAverageCostPrice = (PreviousCostPrice + _inventTrans.costValue())/(PreviousQty + _inventTrans.Qty);

            _landedCostTmp.ItemId = _inventTrans.ItemId;
            _landedCostTmp.ItemName = _vendInvoiceTrans.itemName();
            _landedCostTmp.ItemCategoryName = _vendInvoiceTrans.getProcurementCategoryName();
            _landedCostTmp.ReferenceId = _inventTransOrigin.ReferenceId;
            _landedCostTmp.ReferenceCategory = _inventTransOrigin.ReferenceCategory;
            _landedCostTmp.InvoiceAccount = _vendInvoiceTrans.getInvoiceAccount();
            _landedCostTmp.VendName = _vendInvoiceTrans.getName();
            _landedCostTmp.InventLocationId = _inventTrans.inventLocationId();
            _landedCostTmp.InvoiceId = _vendInvoiceTrans.InvoiceId;
            _landedCostTmp.InvoiceDate = _vendInvoiceTrans.InvoiceDate;

            _landedCostTmp.Qty = _inventTrans.Qty;
            _landedCostTmp.CostAmountPosted = _inventTrans.costValue();
            _landedCostTmp.CurrentAvgCostAmount = CurrentAverageCostPrice;

            _landedCostTmp.PreviousQty = PreviousQty;
            _landedCostTmp.PreviousCostAmount = PreviousCostPrice;
            _landedCostTmp.PreviousAvgCostAmount = PreviousAverageCostPrice;

            PreviousQty += _inventTrans.Qty;
            PreviousCostPrice += _inventTrans.costValue();
            PreviousAverageCostPrice = (PreviousCostPrice/PreviousQty);

            _landedCostTmp.insert();

        }

    Output
    avg.png

  • Suggested answer
    guk1964 Profile Picture
    guk1964 10,877 on at
    RE: Item inventory purchase history with average cost for previous and current PO?

    Without wanting to be patronising,  if you need to ask the question then you probably aren't ready to do the development. This is more of a consulting requirement.

     Item Trans and Vend Trans hold most of the data.

    Check the code behind the forms and reports mentioned in my post above and those should give you a good guide to the tables and queries. 

  • Rana Anees Profile Picture
    Rana Anees 1,811 on at
    RE: Item inventory purchase history with average cost for previous and current PO?

    Hii Ludwig and magic1949,

    I will try to do it in Power BI as well.

    But my question is what tables, methods, classes etc should I explore to get the required results.

    Once I know where is all required information is can start developing.

    Thanks

  • Suggested answer
    Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at
    RE: Item inventory purchase history with average cost for previous and current PO?

    Hi Rana,

    As magic1949 indicated, there is no standard report available for your reporting requirement. You can either use power bi or develop a custom ssrs report depending on the expertise you have available in house.

    Best regards

    Ludwig

  • guk1964 Profile Picture
    guk1964 10,877 on at
    RE: Item inventory purchase history with average cost for previous and current PO?

    You will need to pull it altogether. Power Bi may be a better option.

    Try this https://organicax.com/2016/04/22/inventory-parameters-last-price-history/  When the 'Last price history' Inventory parameter,  is enabled it will store the historical prices, then you can look at the Item prices report in Inventory management (by selecting the costing type to Last Price).

    See Invent trans and filter by date and Reference = Purchase order. There are additional fields you can add to the overview grid.

    Also see Item/Vendor and Vendor /item stats and Periodic stats Procurement and sourcing > Reports > Statistics > Vendor,

    The Period statistics button on Released products gives summary information..

    Period statistics button on Released products in AX

    A very basic summary of how many of this particular item had been purchased in the current month, the past month, the current year to date totals, the past year totals, and the lifetime purchase totals for the item. 

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

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February 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... 292,516 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,409 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans