Learn How To Build NetSuite Percent Complete Summary Searches with Progress Indicators

This article is relevant if you are using NetSuite Saved Search and you would like to calculate a percentage of two summaries (aggregates) and/or you would like to produce a visual progress bar.

Background

Recently, One of our new clients came to us asking for help to calculate a percentage via saved search. In our client’s case, he sought to determine the number of items needed to produce on a workorder relative to the number of items committed to the workorder to come up with a percentage ready for having component inventory on hand as a rough measure of “workorder backlog”.

In a more general way to think about this, all we want to do is add up all the quantities needed on an order relative to the all the quantities that are marked committed to that same order. Let’s remember that NetSuite’s commitment flag is the mechanism that effectively allocates inventory to a specific transaction. Thus, if all the inventory you have on hand has been committed to an order, and your inventory balances are trustworthy, then you should be confident you can fulfill the order in whole.

Let’s go a bit further and consider that the ratio between these two numbers (quantity and quantity committed) can be thought of as percent complete. Percent complete, a common measurement, can visually be presented as progress bar.

Step One: The Percent Complete Aggregate

When working with NetSuite summary searches, it is tricky to take the aggregate of one number and the aggregate of another number and then perform additional math on it. It’s easy to be blinded by the way NetSuite presents the summary column dropdowns to consider it is indeed possible to do more advanced calculations. But leveraging NetSuite’s underlying Oracle Database via formula fields, we can create an Analytical Function to unlock power. You may want to see my example Solving the NetSuite Cumulative Saved Search Tally Challenge. In our client’s case, they needed the following formula to get percent committed (or complete):

ROUND(
	100*
		(1-SUM(
				({quantity} - NVL({quantitycommitted},0))
			) / 
			NULLIF
			(
				SUM(
						NVL({quantity},0)
					),0
			)
		)
	)

Notice the SUM statements in the formula? These are Oracle Analytical Functions and they will aggregate based on the other grouping columns in your query. In the above example, we treat Nulls as Zero and we handle Divide by Zero errors by using an Oracle database trick to play with nulls to prevent NetSuite errors.

Once you have this in your formula, you then set your saved search output column to provide the “Maximum” results. Click on associated images.

Step Two: Output the Saved Search Progress Bars

Our client then leveraged this handy trick provided by another community member to show progress bars. Here is yet another article showing a similar technique.

I will output the total value of the formula here prettified so you can more easily interpret what is happening.

'
<style>
.progress {
	overflow: hidden;
	height: 18px;
	background-color: #D51D0A;
	border-radius: 4px;
	-webkit-box-shadow: inset 0 1px 2px rgba(0, 0, 0, 0.1);
	box-shadow: inset 0 1px 2px rgba(0, 0, 0, 0.1)
}
.progress-bar {
	float: left;
	width: 0;
	height: 100%;
	font-size: 11px;
	line-height: 18px;
	color: #fff;
	text-align: center;
	background-color: #428bca;
	-webkit-box-shadow: inset 0 -1px 0 rgba(0, 0, 0, 0.15);
	box-shadow: inset 0 -1px 0 rgba(0, 0, 0, 0.15);
	-webkit-transition: width .6s ease;
	transition: width .6s ease
}
</style>
<div class="progress">' || 
	'<div class="progress-bar" style="width:' ||
		100*(1-SUM(({quantity} - NVL({quantitycommitted},0))) / NULLIF(sum(NVL({quantity},0)),0)) || '%;"> ' || 
		ROUND(100*(1-SUM(({quantity} - NVL({quantitycommitted},0))) / NULLIF(SUM(NVL({quantity},0)),0))) || '%
	</div>
</div>
'

Work with Strong NetSuite Help

In this article, our client worked with Matthew M., one of our senior consultants, to ultimately pull the solution together. If you found this article helpful, sign up for notification of new articles. If you would like more muscle to handle your NetSuite heavy-lifting, let’s have a conversation.

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