Excel PowerPivot as a way to replace the Inventory Valuation

john_bellinger

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.

daborg

I am wondering, did you have success using this PowerPivot model for the Inventory Valuation replacement?

john_bellinger

I found this which satisfied my end users request. I did not pursue PowerPivot any further regarding the Inventory Valuation report, but have used it successfully on other item type activity (sales / purchases stats);

http://mibuso.com/blogs/ara3n/2009/07/0 ... amics-nav/

Rob_Hansen

We have a similar approach fully packaged up as a NAV report, run like any other NAV report with the same filtering options as the base Inventory Valuation. http://www.epimatic.com/products/fastinventoryvaluation

Alex_Chow

rdhansen wrote:

We have a similar approach fully packaged up as a NAV report, run like any other NAV report with the same filtering options as the base Inventory Valuation. http://www.epimatic.com/products/fastinventoryvaluation

But this one is for sale and the other one is free?

Rob_Hansen

That's right. Companies that are happy to work with the direct SQL output or building on top of it to create a SQL report to run outside of NAV (or build a NAV report on top of it) can use the query and do that at no cost. For those that lack the expertise and want something that runs like any other NAV report with all the filtering options, etc. can consider our product. No one's obligated to buy anything from us...just wanted to mention it.