[David York]

Hello,

Does anyone know of a way to automatically track inventory on hand by storing a snapshot of each day’s quantities? Rather than running a historical stock status and adding everything up every day, I’m picturing an automatic data pull each day at noon, for example, and storing it in Excel without having to actively do it myself the hard way.

The applications would be numerous, but I’m most interested in calculating a room’s capacity and tracking how much space our inventory is taking up over time.

Is this possible? We also have Power BI, if that might make things easier than setting up an Excel report.

Thank you,
David York

——————————
David York
Allagash Brewing Company
Portland ME
——————————

[Barry Crowell]

David,

I would use one of the SQL Inventory views from Victoria Yudin and create a SQL Job that runs everyday at noon to insert the inventory data into a custom table. I would then use Power BI to analyze your inventory data.  You may want to look at sqlbi.com’s Synoptic Designer tool.  With the tool you can add a layout of your warehouse floor to use as a custom visual inside of Power BI.

——————————
Barry Crowell
Senior Business Consultant
KTL Solutions, Inc
Frederick MD
——————————
——————————————-

[Beat Bucher]

Hi David,
We do that on a weekly basis thru our SSRS scheduled reports that export the result to an Excel workbook on a shared network drive for the users.. This way they do have a weekly status of the inventory historical value based on the recevings.
Use one of the many views that are available on the net or from Victoria as suggested by Barry.

——————————
Beat Bucher
Business Analyst, Dynamics GP MVP
Ultra-Electronics Forensic Technology Inc.
Montreal QC/Canada
+1-514-489-4267
@GP_Beat http://dyngpbeat.wordpress.com/
Montreal QC GPUG Chapter Leader
GP2013R2 / MR2012 CU14
——————————
——————————————-

[Joseph C. Markovich]

Hi David-

We do something like this but on a monthly basis — using Extender and SmartConnect.

There’s an Extender window linked to Item Maintenance. SmartConnect runs a bulk data load job based on a view I created (only because I forgot about Victoria’s excellent views on her blog). It’s scheduled to run at midnight the 1st of every month and populates the Extender detail window.

The nice thing is all the data is stored in GP. You could easily dump this to Excel, create an Excel refreshable report or link it to PowerBI.

-Joe

——————————
Joseph Markovich
IT Manager
Okaya Electric America
Valparaiso IN
——————————
——————————————-

[Chris Adaline]

If you’re using multibin, it might be useful to capture all the detail in IV00112.  You could create a scheduled job to run something like this:

USE CompanyDB

DECLARE @sqlcmd nvarchar(100)

set @sqlcmd = ‘SELECT * INTO IV00112_’+ CONVERT(VARCHAR(8), GETDATE(), 112) + ‘ FROM IV00112’

EXEC (@sqlcmd)

Then use Excel to access the tables.  The problem is that these can get quite big after a while.  I used to keep a couple years of monthly snapshots of the FIFO layers in IV10200 like this for analyzing average costing. 

——————————
Chris Adaline
——————————
——————————————-