Microsoft Dynamics NAV (Archived)

Inventory consumption by period

(0) Share

Report

Posted on by Community Member

How do I go to look-up all raw material consumption by period in NAV 4.03

nkp

*This post is locked for comments

I have the same question (0)

All responses (2)

Answers (0)

Suggested answer

Alexander Ermakov 28,096 on at

Like (1)

Report

I would suggest you filter out your Item Ledger Entries by certain values of item posting groups and period.

Was this reply helpful? Yes No
Suggested answer

Alex A 2,902 on at

Like (1)

Report
Hello Nkp,

I like the answer Alexander has given because it is user friendly. If you have more advanced skills or a DBA in-house, you can get the answer to this or any question by querying the database. To do this, first you have to define what period you are interested in. The following example shows the average consumption by Item per month over the previous 12 months. You can connect to your database and run such a query from Excel, from Visual Studio, or from SQL Server Management Studio (be sure to replace the XXX with the prefix of your table names):

SELECT [I].[No_] AS [Item], [I].[Description] AS [Description], (CAST(ROUND((( SELECT COALESCE(SUM([POC3].[Qty_ Picked]), 0) /*Select production order component demand*/ FROM [XXX$Prod_ Order Component] AS [POC3] LEFT JOIN [XXX$Production Order] AS [PO4] ON [PO4].[No_] = [POC3].[Prod_ Order No_] WHERE [POC3].[Item No_] = [I].[No_] AND [POC3].[Status] = 4 /*For Finished Production Orders*/ AND [PO4].[Starting Date] >= DATEADD(MONTH,-12,GETDATE()) /*Include prod orders 12 months back to today*/ AND [PO4].[Starting Date] < GETDATE() /*Use the Starting Date, End Date, or Due Date*/ ) / 12), 3) AS DECIMAL(19,3))) AS [Avg Usage/Mo] /*Divide by 12, and Cast as Decimal*/ FROM [XXX$Item] AS [I] WHERE [I].[Inventory Posting Group] = 'RM' ORDER BY [Item] ASC

Was this reply helpful? Yes No