Open Forum

Expand all | Collapse all

T-SQL NAV Inventory Valuation Report

  • 1.  T-SQL NAV Inventory Valuation Report

    Posted 17 days ago
    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 16 days ago
    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 16 days ago
    @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 16 days ago
    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
    ------------------------------