Monthly Item sales Report - Cross tab

Mar 07, 2006 1 Replies

Rate this thread:

Jay 20 years ago

I was looking for a cross-tab (spreadsheet style) report created by RMS that would put Months across the top (columns) and Items for the rows. It would be great to be able to see Qty on hand. The report would look something like:

Lookup code Description On Hand Month 1 Month 2 Month 3 Month 4 Month 5 Month 6

12345678 Test Item 67 125 80 100 110 100 88
87654321 Another Item 25 80 60 95 147 158 68

This is a great tool for seeing monthly trends as well as forecasting, and determining reorder points and levels

Thanks

Jay

Vote

Jason 20 years ago

I've found the most flexible way is to do a database query in Excel. This is exactly what I do:

Pull the entire contents of the ITEM table into a worksheet. You will need this for Quantity on Hand.

Get the entire Transaction Detail table on another table (you may want to filter by date or trans# if you have a lot of sales).

You will need to do some formulas to put the months on the transaction details sheet.

Run a pivot table and format it to look like your example.

Use an INDEX/MATCH formula to put the quantity on hand next to the pivot table.

I'm sure there is an EASIER way, but I like pivot tables. I'd marry pivot tables if they were a person (or if my state defined marriage as one man and one Excel tool).

Jason

"Jay" wrote:

Vote

Join the Discussion

Have something to add? Share your thoughts — no account required.

Didn't find your answer?

Ask the community — no account required