You’re offline. This is a read only version of the page.
How do I go to look-up all raw material consumption by period in NAV 4.03
nkp
*This post is locked for comments
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):
Share
Report
All responses (
Answers (
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
I would suggest you filter out your Item Ledger Entries by certain values of item posting groups and period.
#1
Sohail Ahmed
2
#1
mmv
2
#1
Amol Salvi
2
