Open Forum

Expand all | Collapse all

T-SQL NAV Inventory Valuation Report

  • 1.  T-SQL NAV Inventory Valuation Report

    Posted Mar 05, 2019 03:04 PM
    Hello, Community!

    I am looking for the NAV Inventory Valuation Report duplicated in T-SQL so I can execute it directly as a SQL query.  Does anyone have something they can share?  I'd rather not take the time to reverse-engineer the NAV report if this exists and someone is willing to share it.

    Much appreciated.

    Alex.

    ------------------------------
    Alex Scheler
    Carma Laboratories, Inc.
    Franklin WI
    ------------------------------


  • 2.  RE: T-SQL NAV Inventory Valuation Report

    GOLD CONTRIBUTOR
    Posted Mar 06, 2019 09:13 AM
    This is old, but maybe it can be a start for you.

    https://forum.mibuso.com/discussion/31810/inventory-valuation-to-sql-report-performance

    ------------------------------
    Kyle Hardin
    NAV Developer
    ArcherPoint Inc.
    Duluth GA
    ------------------------------



  • 3.  RE: T-SQL NAV Inventory Valuation Report

    Posted Mar 06, 2019 11:29 AM
    @Charles Ruark did a presentation at NAVUG 2017 on SSRS reporting and had one at that time.  I will see if I can find what he had shared, otherwise, he might be willing to share it with you.

    ------------------------------
    Stuart Ness
    IT Coordinator
    Mrs. Gerry's Kitchen, Inc
    Albert Lea MN
    ------------------------------



  • 4.  RE: T-SQL NAV Inventory Valuation Report

    Posted Mar 06, 2019 12:03 PM
    Here is the SQL I'm using for an SSRS Inventory Valuation Report...it has a parameter for location code to allow filtering to 1 or more location.
    Just replace "CompanyName" with the company name in the table names:

    SELECT [CompanyName$Item].No_ AS ItemNo, [CompanyName$Item].Description, Detail.[Location Code], Detail.Qty, Detail.InventoryValue, ISNULL(ROUND(Detail.InventoryValue / NULLIF (Detail.Qty, 0), 2), 0)
    AS UnitCost, [CompanyName$Location].Sort
    FROM [CompanyName$Item] INNER JOIN
    (SELECT [Item No_], [Location Code], SUM(Qty) AS Qty, SUM(InventoryValue) AS InventoryValue
    FROM (SELECT [Item No_], [Location Code], CAST(SUM(Q) + SUM(Q2) AS float) AS Qty, CAST(SUM(InventoryValue) AS float) AS InventoryValue
    FROM (SELECT [Entry No_], [Item No_], [Location Code], Quantity AS Q,
    (SELECT ISNULL(SUM(IAE.Quantity), 0) AS Expr1
    FROM [CompanyName$Item Application Entry] AS IAE INNER JOIN
    [CompanyName$Item Ledger Entry] AS ILE2 ON IAE.[Inbound Item Entry No_] = ILE2.[Entry No_]
    WHERE (IAE.[Posting Date] <= @D) AND (ILE2.[Posting Date] <= @D) AND (ILE.[Item No_] = ILE2.[Item No_]) AND (IAE.[Outbound Item Entry No_] = ILE.[Entry No_]) AND
    (IAE.[Item Ledger Entry No_] = ILE.[Entry No_])) AS Q2,
    (SELECT SUM([Cost Amount (Expected)] + [Cost Amount (Actual)]) AS Expr1
    FROM [CompanyName$Value Entry] AS V
    WHERE ([Posting Date] <= @D) AND ([Item Ledger Entry No_] = ILE.[Entry No_])) AS InventoryValue
    FROM [CompanyName$Item Ledger Entry] AS ILE
    WHERE ([Posting Date] <= @D) AND (Positive = 0)) AS OutBoundEntries
    GROUP BY [Item No_], [Location Code]
    UNION
    SELECT [Item No_], [Location Code], CAST(SUM(Q) - SUM(Q2) AS float) AS Qty, CAST(SUM(InventoryValue) AS float) AS InventoryValue
    FROM (SELECT [Item No_], [Location Code], Quantity AS Q,
    (SELECT ISNULL(SUM(IAE.Quantity), 0) AS Expr1
    FROM [CompanyName$Item Application Entry] AS IAE INNER JOIN
    [CompanyName$Item Ledger Entry] AS ILE2 ON IAE.[Item Ledger Entry No_] = ILE2.[Entry No_]
    WHERE (IAE.[Posting Date] <= @D) AND (ILE2.[Posting Date] <= @D) AND (ILE.[Entry No_] = IAE.[Inbound Item Entry No_]) AND (IAE.[Outbound Item Entry No_] <> 0)
    AND (IAE.[Item Ledger Entry No_] <> ILE.[Entry No_])) AS Q2,
    (SELECT ISNULL(SUM([Cost Amount (Expected)] + [Cost Amount (Actual)]), 0) AS Expr1
    FROM [CompanyName$Value Entry] AS V
    WHERE ([Posting Date] <= @D) AND ([Item Ledger Entry No_] = ILE.[Entry No_])) AS InventoryValue
    FROM [CompanyName$Item Ledger Entry] AS ILE
    WHERE ([Posting Date] <= @D) AND (Positive = 1)) AS InBoundEntries
    GROUP BY [Item No_], [Location Code]) AS CombinedEntries
    GROUP BY [Item No_], [Location Code]) AS Detail ON [CompanyName$Item].No_ = Detail.[Item No_] INNER JOIN
    [CompanyName$Location] ON Detail.[Location Code] = [CompanyName$Location].Code
    WHERE (Detail.[Location Code] IN (@loc))
    ORDER BY ItemNo

    ------------------------------
    Charles Ruark
    Dir. Business Systems
    CCA & B, LLC (The Elf on The Shelf)
    Marietta GA
    ------------------------------



  • 5.  RE: T-SQL NAV Inventory Valuation Report

    Posted Mar 25, 2019 06:14 PM
    Hello Alex,

    We have the same need for this report, and I built it to run on Excel and Power BI.  I hope this will help.
    Note that these numbers will be the same as if you pull the "Inventory Valuation" report from Navision.  The "Inventory Valuation - WIP" is an entirely different report.

    If you simply want the total inventory at the moment the query is executed, you can run the following query:
    Select 
    	  Sum([Item Ledger Entry Quantity]) as [Total Qty]
    	, Sum([Cost Amount (Actual)]+[Cost Amount (Expected)]) as [Total Value] 
    From [DatabaseGoesHere].dbo.[CompanyGoesHere$Value Entry]
    Where [Item No_] <> ''


    But if you need to be able to select any day in the past and see the inventory valuation for that date, you could use the query below.  You will need to do running sum on the [Quantity] if you want to find the on-hand balance, or the [Inventory Value] if you want the valuation.  In my case, I run this query from Excel/Power BI and use the DAX formula to get the running sum.

    Select 
    	  'Forum SDC' as [Company]
    	, [Item No_]
    	, iif([Posting Date] <= EOMONTH(Getdate(), -13), Dateadd(Day, 1, EOMONTH(Getdate(), -13)), [Posting Date]) as [Posting Date]
    	, Sum([Item Ledger Entry Quantity]) as [Quantity]
    	, iif(Sum([Item Ledger Entry Quantity]) = 0, 0, Sum([Cost Amount (Actual)]+[Cost Amount (Expected)])/Sum([Item Ledger Entry Quantity])) as [Unit Cost]
    	, Sum([Cost Amount (Actual)]+[Cost Amount (Expected)]) as [Inventory Value]
    From [Database].dbo.[CompanyGoesHere$Value Entry]  Where [Item No_] <> ''
    Group By [Item No_], iif([Posting Date] <= EOMONTH(Getdate(), -13), Dateadd(Day, 1, EOMONTH(Getdate(), -13)), [Posting Date])


    ------------------------------
    Dzuy Trinh
    Forum Energy Technologies
    Houston TX
    ------------------------------



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