[Latha]

Hi,

I am trying to design a report that gives me all our stock items with their current quantity on hand and include any open POs.

I tried customising the Open PO reports and added the quantity in hand column to it, but then it only shows the items are are currently on order. If I had a stock item with no open orders, they don’t come up on the report. The standard “Current Inventory Status” report is not good enough as it doesn’t allow a drill down into the “On Order” column.

Anyone got any ideas how I could do this? Thanks in advance….
This is a cached copy. Click here to see the original post.

[trimblee]

RE: Inventory report – On Hand + on Order

Because of the issues you’re encountering, I’ve usually preferred searches to reports.

What do you want to see when you drill into On Order?

You’re best bet is probably to build an item search that joins in PO data, summarize the results (e.g. group by item, sum of on hand, sum of on order), and include the transaction detail you want as results, but don’t summarize that, such that when you click on a summarized result it loads the detail.

The drill-down in searches isn’t as good as that in reports, but it works pretty well.

[plantscan]

It’s unclear whether you actually need to see the open POs or if you just want to know the quantity on order If it’s the later, then create a saved search that selects the stock items that are active and display Name, On Hand, On Order. If you have multi-location turned on, then display Location On Hand and Location On Order (and filter what location you are looking for), or you can still just display On Order to see all locations and what has been ordered.

[Latha]

RE: Inventory report – On Hand + on Order

Originally posted by trimblee

View Post

You’re best bet is probably to build an item search that joins in PO data, summarize the results (e.g. group by item, sum of on hand, sum of on order), and include the transaction detail you want as results, but don’t summarize that, such that when you click on a summarized result it loads the detail.

I tried your suggestion of using an Item search linked to Transactions, however, when I restrict it to just POs, the list only displays those items that have POs against it. What I need is a complete list of all our stock items summarised for qty on hand and on order, and the On Order to drill down to display the PO date, PO qty and a couple of custom PO fields.

[kastnerd]

RE: Inventory report – On Hand + on Order

Originally posted by trimblee

View Post

Because of the issues you’re encountering, I’ve usually preferred searches to reports.

I agree, I use a saved search for this exact report. I have multiple locations so I made a report that filters just for that specific location.

[KenFenster]

You could try a REPORT option

I created a custom version of the “current inventory” report. This report is item driven and displays on-hand and on-order by location and provides a total for all locations.

[Latha]

RE: Inventory report – On Hand + on Order

Originally posted by kastnerd

View Post

I agree, I use a saved search for this exact report. I have multiple locations so I made a report that filters just for that specific location.

Hi Kastnerd,

Maybe I am missing something very basic here…

In an Item search I added the Transactions table, and if I restrict it to show only the POs, then the search excludes out all the items which don’t have a PO against them. I want a full list of stock items with their qty on hand and on order (drilldown).

Are you able to share the filters that you are using in your search?

Thanks….

[KenFenster]

I didn’t realize your requirement was to drill down to the specific purchase orders. I use just the summary of the on-order field. I’m using a Report not a Search. Searches do have Filter data fields available. You would need to put some data element from the PO on the results from a search.

What data are you looking for?

[Latha]

RE: Inventory report – On Hand + on Order

Hi Ken,

I am looking for a report/search just lists out all the stock items with columns for qty on hand and qty on order, with a drill down into the the qty on order to show PO Date, PO#, Qty and a few other custom fields.

Thanks…

[KenFenster]

I’ll try to create something quickly to see what I can do. I’ve not look for the join between PO and Item before.

[KenFenster]

I worked on it yesterday but I can’t figure out a way to meet you business requirements. I’m not a programmer. I suggest you contact NS Support.

Sorry

[Latha]

RE: Inventory report – On Hand + on Order

No problem Ken… thanks for trying.

I am now exploring Web Query as an option … export the Item list with stock and the open POs as two Web Query reports and join them in Excel via a Macro….

[Author]

Posts