Export the Inventory Activity Query to Support Targeted Analysis

The Inventory Activity Query is command central for quantities, commitments and costs for items and locations. You may not be aware that there is an Excel Export available on the On Hand Listing that allows you export the specific elements of interest and lay the groundwork for further analysis.

The On Hand Listing is available from the Inventory Activity Query. The standard report offers 3 formats

Committed Quantities: In addition to the On Hand Quantity, the following fields will be displayed: On Order, Committed to Sales, Committed to Production, Committed to Ship, Available to Ship.
Current Costs: In addition to the On Hand Quantity, the following fields will be displayed: Distributed Amount, Last Cost, Average Cost, Standard Cost, Base Price.
Stocking Constraints: In addition to the On Hand Quantity, the following fields will be displayed: Minimum Stock, Maximum Stock, Order Quantity Multiple, Economic Re-Order, Safety Stock.

Each of these formats is very informative but fixed in terms of its layout. If you choose the option to export to Excel, you can control the exact columns and essentially pull elements from any of the formats. Here’s how:

From the Printer icon on the Inventory Activity Query, choose the option for Inventory On Hand Listing.
The Sort Level options will not impact the exported data. The data in the worksheet will appear initiallly sorted by Item.
As noted above, you can ignore the Report Format Options section. The contents of the export will be determined by the Export Configuration.
In the Quantities to Include section, select the categories of quantities that should be inlcuded in the export. Each of the criteria are considered as “and” conditions that further the limit the selection of records.

On Hand: Positive, Negative (Oversold) or Zero (Out of stock)
On Order: Positive, Negative or Zero
In Transit: Positive, Negative or Zero
Committed to Sales: Positive, Negative or Zero
Committed to Production: Positive, Negative or Zero
Committed to Ship: Positive, Negative or Zero

On the next screen, select whether to display the Stocking or Selling Unit of Measure and the number of decimals to use for quantities and costs.

The next 2 screens contain Selected Range options that can be used to identify which items get considered for inclusion in the export.
Finally, on the Routing Tab, select the ‘Send Output To’ option of MS Excel and then click the Configure button to select the columns for the export.

On the final screen, specify the filename to be used for the export as well as settings for whether column headings should be included and whether the spreadsheet should be cleared of old data before being populated with the results of the new export.
Click Finish to complete the configuration.
Save the parameters for future use and click Process to generate the export.