Learn the NetSuite Item Sales Trend Pattern via Saved Search

The article is relevant if you are seeking to build a NetSuite trend report, especially where you have multiple columns of similar information, using Saved Search technology.

Background

NetSuite offers a Sales by Item Summary report which gets the basics done. However, there are times that you want more information beyond what the built-in reports can offer. The typical way to address getting more data to a report is to move to a Saved Search. In our client’s case, they wanted additional information such as the date of the first sale, the total costs and sales as well as a weekly view into the last 26 weeks of unit sales.

Solving for NetSuite Item Sales Trend Requirements

As I solved this for our client, there were a few elements that I thought would help other fellow NetSuite users learn how to get more information out of the NetSuite system.  Namely:

  1. How to gather inventory quantity, sales and costs information
  2. How to use formulas to determine the week in relative terms
  3. How to generate many columns of similar data quickly

The approach for getting item sales information is to use a Transaction based search and to leverage the quantity and amount counters. Yet, the other dimension is to key on the general ledger account type.  The key to understanding NetSuite behavior for all inventory and related transactional item operations is to learn that the inventory general ledger accounts drive the inventory ledger (see my article How To: NetSuite Stock Inventory Movement and In-Transit), that you must use criteria of “posting = true” and when to use NetSuite’s Account Type of Special Account Types (see my article NetSuite Account Special Types Reference) to key on specific transaction details.  For example, Sales is an “Income” account type and related costs are a “Cost of Goods Sold” account type.     See related image to review saved search summary references.

Solving for Multiple Week (period) Sales Trend

The other interesting aspect of this client report is to generate weekly sales for the last six month by week.  To produce this, we need to leverage the system date and then compare it to transaction detail date.  Here, we can use Oracle PLSQL functions in formula fields to test every transaction the report scope and then bring in the ones we want.

However, if we have twenty six columns of the nearly the same information, it can be tedious and error-prone to copy-paste formulas.  As such, I wrote a simple NetSuite SuiteLet program to modify my Saved Search to automatically generate the 26 weeks of information.  I present this program below to use as inspiration in your advanced Saved Search efforts. While many times, a program, such as the following, can be run in the browser console (see my article How To: Quickly Update a NetSuite Read Only Field), this one had to be run on the server in order for NetSuite to save the search definition.

function getFormula(week_no){
 var s = "CASE WHEN {accounttype} = 'Income' AND (Trunc(sysdate, 'IW') - Trunc({trandate}, 'IW')) /7 = "
 var s = s + week_no + ' THEN {quantity} ELSE 0 END'
 return s;
}

function getLabel(week_no){
 return "Week " + week_no
}

function main(){

 var a = 26
 var col = new Array();
 var s = nlapiLoadSearch('transaction', 'customsearch_sales_trend_report');
 var i = 0

 for(count = a -1; count >= 0; count--){
   var x = getFormula(count)
   var y = getLabel(count)

   var c = new nlobjSearchColumn('formulanumeric', null, 'sum');
   c.setFormula(x)
   c.setLabel(y)

   col[i] = c
   i = i + 1;
 }
 s.addColumns(col);
 return s.saveSearch("Sales Trend Report", "customsearch_sales_trend_report");
}

Get Expert NetSuite Saved Search Help

For the most part, NetSuite Report challenges are overcome using NetSuite Saved Search.  As such, it is important to have a strong mental model of the NetSuite database structure to allow you to leverage advanced techniques to produce meaningful information.  This article shows how to summarize information into a single view.  Many times, we need to take two or more Saved Searches and put them together to ultimately render information.  In those cases, you can use our Content Rendering Engine to get even more out of your Saved Search efforts.

If you had enough of feeling that you can’t get access to key information with NetSuite, let’s have a conversation.

Be Sociable, Share!

Marty Zigman

Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - YouTube

| Tags: , | Category: NetSuite, Reporting | Leave a comment

Leave a Reply

Your email address will not be published. Required fields are marked *