Open Forum

Expand all | Collapse all

Historical inventory over time? Jet reports?

  • 1.  Historical inventory over time? Jet reports?

    Posted Oct 03, 2019 07:29 PM
    Hi,
    I've been trying to look into the past to do some capacity planning (and eventually some forecasting for MRP) and I've had trouble getting historical numbers for inventory. I have access to jet reports and I have tried looking at the physical inventory ledger - unfortunately the inventory wasn't run every month in the past leaving me with holes in my data. I've heard that the inventory valuation report is the main path but our valuation methods have changed over time, I'm not familiar enough with this area to be confident it will be accurate.

    Is the best way to go about this pulling the item ledger entries for the items I'm interested in and calculating the running total? I'm worried about bogging down my capacity models in excel by pulling so many lines but I may try using power query/ the data model to handle it if that's the most efficient way.

    Is there a more straightforward path? I haven't dug into cubes for jet reports but I'm wondering if this would be a good job for it.

    Thank you!
    Jon

    ------------------------------
    Jonathan Kao
    Cypress Grove Cheese
    Arcata CA
    ------------------------------
    Conference-BCNAVUG_200x200


  • 2.  RE: Historical inventory over time? Jet reports?

    Posted Oct 04, 2019 02:21 AM
    Edited by Adam Jones Oct 04, 2019 02:25 AM
      |   view attached
    Hi Jon,

    I was asked to do something similar a couple of years ago, my way is very simple, but effective for us.  I created a report from the item table, a few lines of code and voila, a list of all products at a past date. You can then either put it into a page report, or export to excel if this is better for you.


    Hope this helps, many thanks.

    Adam.


    ------------------------------
    Adam Jones
    Celebration Paper & Plastics Ltd
    Burton upon Trent
    ------------------------------

    Conference-BCNAVUG_200x200


  • 3.  RE: Historical inventory over time? Jet reports?

    Posted Oct 04, 2019 12:17 PM
    Hi Adam,
    Thank you for the information, this looks pretty straight forward but unfortunately I don't have access to NAV development environment, but it looks like each time you run this report it recalculates the items based on the date you set as your calcdate. Does it take a long time to run? do you run it for a range of dates or just one?

    Thanks!
    Jon

    ------------------------------
    Jonathan Kao
    Cypress Grove Cheese
    Arcata CA
    ------------------------------

    Conference-BCNAVUG_200x200


  • 4.  RE: Historical inventory over time? Jet reports?

    Posted Oct 07, 2019 02:31 AM
    Hi Jon,

    We have around 5,000 items and it takes less than a minute to run, the way I have it set up is just for one date, but I don't think it would take much to have a range of dates returned.

    I don't have a developer licence, but you should be able to create/edit reports from a standard licence, if you ask your partner for access they may give it to you, they may also be able to give you some training on report writing (ours did).

    Hope this helps.

    Many thanks.

    Adam.

    ------------------------------
    Adam Jones
    Celebration Paper & Plastics Ltd
    Burton upon Trent
    ------------------------------

    Conference-BCNAVUG_200x200


  • 5.  RE: Historical inventory over time? Jet reports?

    Posted Oct 08, 2019 10:51 AM
    Thanks for the information Adam!

    ------------------------------
    Jonathan Kao
    Cypress Grove Cheese
    Arcata CA
    ------------------------------

    Conference-BCNAVUG_200x200


  • 6.  RE: Historical inventory over time? Jet reports?

    TOP CONTRIBUTOR
    Posted Oct 09, 2019 09:43 AM
    While Adam's solution would certainly work, if you're only learning NAV reports for this project, then it might be worth exploring other options.
    I think your original thoughts on using a Jet Cube are quite reasonable--if you have that functionality in place. If not--go simpler. If it was me, I'd start by simply filtering Item Ledger Entries (or value entries) down to the date range and items that you want. Then export to excel and make a Pivot table off of it to summarize the data. Once you find a format that you like in Excel, you could write a standard Jet report to gather the data rather than exporting from NAV. You could also utilize a Jet cube to accomplish the same thing. I'd also recommend that you compare your results against the "Inventory Valuation" and/or the "Inventory to GL Reconcile" report. You may find that you can trust those reports after all--removing the need for a custom solution altogether.
    Best of luck!

    ------------------------------
    Greg Enns
    ERP Coordinator
    Technical Prospects
    Kaukauna WI
    ------------------------------

    Conference-BCNAVUG_200x200


  • 7.  RE: Historical inventory over time? Jet reports?

    Posted Oct 10, 2019 12:05 PM
    That sounds like a good path as well Greg, definitely a lot of ways to skin this cat.

    ------------------------------
    Jonathan Kao
    Cypress Grove Cheese
    Arcata CA
    ------------------------------

    Conference-BCNAVUG_200x200


  • 8.  RE: Historical inventory over time? Jet reports?

    SILVER CONTRIBUTOR
    Posted Oct 10, 2019 12:40 PM

    >​ you could write a standard Jet report to gather the data rather than exporting from NAV.

    We don't use cubes (yet) so I'm interested in this. Does anyone have any existing jet code to share on how to create such a report?

    Thanks,
    -d.



    ------------------------------
    Devora Locke
    Shin-Etsu MicroSi, Inc
    Phoenix AZ
    ------------------------------

    Conference-BCNAVUG_200x200


  • 9.  RE: Historical inventory over time? Jet reports?

    Posted Oct 11, 2019 11:18 PM
    Edited by Janice Taylor Oct 11, 2019 11:21 PM
      |   view attached
    Hi Jonathan,

    If you are a Jet customer and own Jet Analytics (formerly Jet Enterprise), you could download their report NAV063 - Jet Analytics Inventory Over Time. In the Jet ribbon, you will need to click on the Pivot Table icon and click on Update Data Sources and select the name of your data cube so the report updates to your data. You can get the report templates here: https://insightsoftware.com/downloads/

    If you only have Jet Reports, then a report like the attached might get you on the right track. I built it similar to what Adam suggested. It uses the Item table and the flow fields Quantity on Hand for the current inventory and Net Change and filtered for the date for the prior period. These is standard in NAV. You could easily expand this to add more periods.

    I've also built it to only show items that have a current inventory and/or inventory at a prior period to avoid showing items that don't have inventory today or at that reporting period. (Less clutter in the report.)

    For fun, I added some conditional formatting to indicate if the inventory increased since the prior period.

    This took me about 30 minutes to write on a lazy Friday night.

    Hopefully you find this helpful!

    Cheers

    ------------------------------
    Janice Taylor
    Managing Director
    Reports Your Way Ltd.
    Toronto, Canada
    ------------------------------

    Attachment(s)

    xlsx
    Inventory.xlsx   60K 1 version
    Conference-BCNAVUG_200x200


  • 10.  RE: Historical inventory over time? Jet reports?

    Posted Oct 14, 2019 07:15 AM

    Thanks for the link.  Since the acquisition of Jet by Insight, I wasn't sure where the sample reports we any longer.

     

    Teri Chow

    ERP Systems Manager

    (408) 266-8866 ext. 282

    www.shfb.org

     

     

    image004.jpg@01D5786D.F1C86C30

     




    Conference-BCNAVUG_200x200


  • 11.  RE: Historical inventory over time? Jet reports?

    Posted Oct 14, 2019 06:59 PM
    Thank you so much Janice!
    I will definitely take a close look at using these reports or using them as a template.

    ------------------------------
    Jonathan Kao
    Cypress Grove Cheese
    Arcata CA
    ------------------------------

    Conference-BCNAVUG_200x200


  • 12.  RE: Historical inventory over time? Jet reports?

    TOP CONTRIBUTOR
    Posted Oct 16, 2019 03:23 PM
    A couple of thoughts:
    We built a separate database with views pointing back to the production NAV database.
    Item ledger Entries and focusing on entry type of Output and Consumption.  Then we just leverage Microsoft Power BI.  First just PBI Desktop but then Power BI Pro as we are Office 365 E5 users.  With this we use the Power BI Gateway and schedule in the Workspace we publish the reports to refresh the datasets at about 3am as this gets a snapshot of a full days production.  You can also leverage your Release Production Order Line Item and evaluate what was Schedule, what was finished and / or remaining.  This give the production team a dashboard for various uses.

    The other is using the same database and views; create a Item View and recreate your own hyperlink totals such as the shown on the Item Card: Quantity On hand, In Weight, On Sales Orders, On Hold, On production Orders, etc.  You can get an Inventory dashboard.

    Other options are to use OData Queries such a summarized Item or Item Ledger Entries or Warehouse entries..

    Note at time you will catch some ill regularities between Item Ledger Entries and Warehouse Entries depending on your as of queries but these are easy to clear up.


    You might have an OData already useable and you can leverage Power BI or Excel with Power Pivot.


    ------------------------------
    Ronald McVicar, IT Software Mgr
    NSP (Quality Meats)
    nspproteins.com
    ------------------------------

    Conference-BCNAVUG_200x200


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