Sometime last year I was tasked with creating a monthly sales report of all the items we have sold over the last year on our company website. There are several ways to do this with NetSuite, but I wanted one that had both good usability in NetSuite and could be exported to be used in Excel or Google Sheets.

Interestingly enough, I was even more recently tasked with creating an inventory forecast, essentially the same sort of saved search but instead of taking past data, using open purchase orders. In this article, I will cover both.

Monthly Sales Report Using NetSuite Saved Search

Step 1: create a Transaction-based saved search that uses the following criteria:
Type = Sales Order
Date = within previous rolling year
Item or Source = these will depend on your own situation; I needed a few extra filters to remove shipping, make sure the source was “web”.

Step 2: Results
Item = Group
Quantity = Sum (total for last 12 months; optional)
Formula (Numeric) = Sum with formulas that look like this:

DECODE(TO_CHAR({today},'MM') - TO_CHAR({trandate},'MM') + 12 * (TO_CHAR({today},'YYYY') - TO_CHAR({trandate},'YYYY')),0,{quantity})

This formula is repeated 12 times, each time changing the array number to get the previous month.

TO_CHAR({trandate},'YYYY')),0,{quantity}
TO_CHAR({trandate},'YYYY')),1,{quantity}
TO_CHAR({trandate},'YYYY')),2,{quantity}
TO_CHAR({trandate},'YYYY')),3,{quantity}
TO_CHAR({trandate},'YYYY')),4,{quantity}
TO_CHAR({trandate},'YYYY')),5,{quantity}
TO_CHAR({trandate},'YYYY')),6,{quantity}
TO_CHAR({trandate},'YYYY')),7,{quantity}
TO_CHAR({trandate},'YYYY')),8,{quantity}
TO_CHAR({trandate},'YYYY')),9,{quantity}
TO_CHAR({trandate},'YYYY')),10,{quantity}
TO_CHAR({trandate},'YYYY')),11,{quantity}

In this array, “0” is the current month, “1” is last month, “2” is 2 months ago, etc. where “11” is 11 months prior. The formula strips the current date (today) of its month and year so it can subtract from past dates, also stripped. Then, once the dates are grouped, it sums up the quantity for those dates and you have a nice view of the items sold for the past year, including this month.

If you wanted to, you could start your array at 1 or go past 11. Make sure your date range in criteria matches accordingly.

Monthly Forecast With a NetSuite Saved Search

Forecasting uses open purchase orders with expected receipt dates within the next year. Many of the items we use in our assemblies are imported and take several months to receive. This gives us a quick view of what is on order. Paired with other saved searches, this helps our purchasing department immensely.

This was essentially the same way as the items sold search but the order of dates being calculated changed.

DECODE(TO_CHAR({expectedreceiptdate},'MM') - TO_CHAR({today},'MM') + 12 * (TO_CHAR({expectedreceiptdate},'YYYY') - TO_CHAR({today},'YYYY')),0,{quantity})

Here we use “expectedreceiptdate” as our starting value to determine how many months away from “today” the purchase order is. The date is subject to change, which is fine. It will also update on the saved search.

Highlighting, Headers, Parting Notes

Like all saved searches, you can add highlights based on specific criteria. I added a gray highlight for any item that was only sold 11 months ago as a way to find poor moving products. A green highlight was used to show items that have sold this month and last month.

Both of these highlights are done by using Highlight if (summary) and the same code as above as your condition formula.

The headers used in both searches are simply typed in. In the case of the forecasting, each month these need to be updated manually.

Both of these saved searches provide a nice visual for the previous or next 12 months, including the current month. They are also easily exported to be used in your preferred spreadsheet. Make sure to include your internal ID on items so they can be keyed and blended with other exported data.

Check out all the archived NetSuite articles here.