mibuso.com
Home› NAV Three Tier
Options

Excel PowerPivot as a way to replace the Inventory Valuation

john_bellingerjohn_bellinger Member Posts: 34
edited 2014-04-26 in NAV Three Tier
NAV2009 R2 (Version US Dynamics NAV 6.0 SP1)

I built an Excel Power Pivot worksheet as a way to replace the out of the box Inventory Valuation report (I think). I’m getting results that agree to that report (in my limited testing). I’m wondering if anyone else has used this method and can share their experience. ??

I used the Value Entry table; this is what my Query looks like;

SELECT [dbo].[MYCOMPANY$Value Entry].[Item No_],
[dbo].[MYCOMPANY$Value Entry].[Location Code],
SUM (
[dbo].[MYCOMPANY$Value Entry].[Cost Amount (Actual)] +
[dbo].[MYCOMPANY$Value Entry].[Cost Amount (Expected)] ) AS [Cost Amount] ,
SUM (
[dbo].[MYCOMPANY$Value Entry].[Item Ledger Entry Quantity] ) AS [Item Ledger Entry Quantity]

FROM
[dbo].[MYCOMPANY$Value Entry]

WHERE
[dbo].[MYCOMPANY$Value Entry].[Item No_] <> '' AND
[dbo].[MYCOMPANY$Value Entry].[Posting Date] <= '01/31/2013'

GROUP BY
[dbo].[MYCOMPANY$Value Entry].[Item No_],
[dbo].[MYCOMPANY$Value Entry].[Location Code]

I related the Item and Item Category tables for descriptions and categorization (by Item Category code).

Processing is much more acceptable in PowerPivot vs. NAV, not to mention analysis reporting potential (slice/dice information and graphical presentations). Of course, this assumes I’m getting the results I expect (Inventory Valuation as of a defined date).

Any experiences would be appreciated.

Thanks.
John Bellinger
ERP Manager
Activar, Inc.

Comments

Sign In or Register to comment.