This article is relevant if you are seeking to produce a cumulative (or running) total via NetSuite Saved Search technology.
Background
Nearly 3 year ago, I wrote an article, NetSuite Saved Search Running Totals Sublist Challenge, where I was challenged to solve a Saved Search problem to aggregate and tally data. Today, that challenge is solved due to the kindness of Robert Pottorff, a NetSuite developer serving Lone Star Percussion.
Robert reached out to share with me that he was able to trick the NetSuite Saved Search parser by allowing it use a “SUM()” in a non-aggregate search. NetSuite assumes that if you use “SUM(” in a formula, you are trying to aggregate. We really want to use Oracle’s Analytic Function set. By placing a SQL comment in the formula field, NetSuite does not assume you are doing an aggregate and passes the string to the underlying Oracle database to do the work we need.
I went ahead and produced a list of transactions to see if I could get it to perform — and indeed it did!
Sample Running Total Saved Search
Here is the actual formula text I used to build the cumulative total based on the amount field:
sum/* comment */({amount})
OVER(PARTITION BY {name}
ORDER BY {internalid}
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
See images for the saved search definition and the results.
Solving NetSuite Technical Challenges
When Robert helped me solved this, I jumped for joy. Robert would be a welcome addition to our team of consultants — it’s this kind of innovation that we, and our clients, appreciate. If you would like help with your Saved Searches or other NetSuite enhancements, let’s have a conversation.
See Related Articles
- Demystifying NetSuite Inventory Numbers, Details and Bin Complexities
- Learn How To Craft a NetSuite ABC Analysis Report Using Advanced SuiteQL Techniques
- Explain NetSuite Inventory Quantity, Value and Average Cost Over Time
- Learn How To Build NetSuite Percent Complete Summary Searches with Progress Indicators
- Learn How To Accumulate NetSuite Inventory Balances via Saved Search
- How To: NetSuite Stock Inventory Movement and In-Transit
- How To: Generate NetSuite Item Weighted Price from Kits
- NetSuite Saved Search Running Totals Sublist Challenge
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
Hey marty,
Is there a way to get the formula to filter based on criteria provided? is there a way to get it to sum the quantities based on a Case When conditional statement?
I am wondering the same thing vernita is asking! Thank you very much marty!
Hello Heath,
Your questions are a good one. I don’t have a grounded answer at this point. I will need to work this a bit to confirm.
Marty
Venita,
I haven’t tried to get this working yet as criteria, only as result output. As soon as I learn if we can, I will post it.
Marty
Hello John,
I haven’t tried to get this working yet as criteria, only as result output. As soon as I learn if we can, I will post it.
Marty
Criteria can be used as a part of the Oracle analytics function. Here is an example where I didn’t want to include bill credits in a running total for a payment file:
TRIM(TO_CHAR((SUM/* comment */
(case when {applyingtransaction.type} = ‘Bill Credit’ OR {applyingtransaction.line} =2 then 0
else {applyingtransaction.amountpaid} end)
OVER(PARTITION BY {internalid} ORDER BY TO_CHAR({internalid},’00000000000000009′) DESC )),’9999999999.00′))
Thank you Jason,
It’s good to see how we can push the tools in this manner.
Marty
This is incredible! It seriously has made our lives a lot easier. Thank you for sharing this knowledge!
Will this only work for transaction totals? I have been trying to get this formula to work when Main Line = False (to show individual line items) however the results don’t seem to be accurate
Hello Kim,
That question is too broad. But once you understand how the Oracle database works with these analytic functions, it’s up to you to be creative to get the data to output the way you want. Naturally, we can tackle your specific concerns by reaching one directly:
https://www.prolecto.com/contact-us/
Marty
Marty,
I’m trying to develop a saved search for one of my company’s KPIs. Thanks to this article I was able to get the formula to work for to generate the overall data, but when I try to aggregate it with the AVERAGE function I get an “unexpected error”. It is a transaction search. The search criteria is Type is Work Order; Status is Work Order:Closed
The formula is as follows.
CASE
WHEN
{line} = 0
THEN
CASE
WHEN
(SUM/* */({built} * ABS({quantity}) * {item.cost}) OVER(PARTITION BY {internalid}))
/
(
(SUM/* */(CASE WHEN {line} = 0 THEN {quantity} ELSE 0 END) OVER(PARTITION BY {internalid}))
*
(SUM/* */({item.cost} * {quantityshiprecv}) OVER(PARTITION BY {internalid}))
)
<= 1
THEN
1
ELSE
0
END
END
Any ideas?
Thanks,
Eric
Hi there,
Im using the formula and it’s working but I need to use a DATE filter in the saved search, and when I apply the filter for a specific day, the formula breaks and doesnt show the correct amounts.
Any idea on how to fix this?
Thanks!
Thanks for this Marty!
I’ve tried using it but it only works when my saved search results is not grouped. Is there any way to make this work when I am grouping my results already?
Hi Jennie,
You may be able to get to it. You will have to play and please do report back. These days, we are solving so many challenges with this license-free tool:
Render NetSuite SQL Queries Like Saved Searches
https://blog.prolecto.com/2021/11/27/render-netsuite-sql-queries-like-saved-searches/
Marty
how can i do the substraction
Can you explain more what you mean by subtraction?
Marty