Open Forum

Expand all | Collapse all

Report for item consumption?

  • 1.  Report for item consumption?

    Posted Dec 05, 2018 11:29 AM
    ​Cannot find it now, but thought there was a report within NAV 2016 that shows quantity of items in an Assembly consumed, as well as remaining quantity on hand.  We're tried to create such a thing with a NAV view as well as with Jet, but can't seem to get the numbers to reconcile. A little tricky using Item Ledger Entry as it requires an accurate set of specific filters.

    Ideally I am looking for items used for Assembly orders only,  plus quantity remaining (in stock) so that purchasing can run a little leaner when it comes to ordering.  Anyone?

    Thanks and Regards,

    Devora Locke
    Shin-Etsu MicroSi, Inc
    Phoenix AZ

  • 2.  RE: Report for item consumption?

    Posted Dec 06, 2018 09:11 AM
    NAV 2015 here.

    We created a report in Excel using PowerPivot that uses the Item Ledger Entry table from SQL and a NAV Query.  The NAV query shows the QOH and the ILE table shows Items consumed for raw materials and subassemblies.  Two NAV Query filters - inventory posting group and item type (we exclude discontinued items) - QOH always matches item card as QOH flow field is used.   SQL - three filters - inventory posting group, entry type - assembly consumption, and item type.  The SQL query is written right in PowerPivot (it's pretty simple, uses table view), the NAV query is pulled into Excel and then "added to the data model".  Relationship set on Item no.  Add the required measures...

    That is the simple overview, there are some exceptions, but we are able to address them either in the query or sql code.

    Jeane Meade
    Sheldons', Inc.
    Antigo WI

  • 3.  RE: Report for item consumption?

    Posted Dec 06, 2018 09:16 AM
    You may want to try the Item Transaction Detail Report.  There are a TON of options available to run the report so start small with a single item # you know you've used in an assembly an a narrow date range.  The bottom filter section is the magic, it has a selection for Transaction Type, use 'Assembly Consumption'.  I use this field to develop SQL queries.  There's also a section for Production Consumption that is just called 'Consumption'.
    I think the out of the box version of the Item Transaction Detail report will show you quantity on hand as well.

    Hope this helps.

    Mark Williams
    MED-EL Corporation

If you've found this thread useful, dive deeper into User Group community content by role