Solving the NetSuite Cumulative Saved Search Tally Challenge

This article is relevant if you are seeking to produce a cumulative (or running) total via NetSuite Saved Search technology.


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})
    ORDER BY {internalid}

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.

Be Sociable, Share!

Marty Zigman

Holding all three official certifications, Marty is Southern California's 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 25 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to setup a conversation.

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

| Tags: , , , , , | Category: NetSuite, Reporting, Technical | 34 Comments


  1. Michael
    Posted June 3, 2015 at 3:17 pm | Permalink

    This is amazing, many thanks to you and Robert.

  2. Dolly
    Posted July 7, 2015 at 10:37 am | Permalink

    Hi Marty,

    Thanks for sharing this. I did try doing this in a saved search. I replaced the amount in your numeric formula with the internal Id of the amount field I wanted to have a running total for.

    Unfortunately, it did not work. The numeric formula only returned the exact same amount field, without any cumulative totals. Can you pls. let me know why it is not working & how to fix this in the saved search? Truly appreciate it!

    Thanks a lot!


  3. Dolly
    Posted July 9, 2015 at 9:18 am | Permalink

    Hi Marty,

    I tweaked the formula & retried it. It now works & gives the desired results! Pretty awesome!

    Thanks a lot!

  4. Posted July 22, 2015 at 7:24 am | Permalink

    This is really great and many thanks!

    Some may find this useful:
    I’m working on a transaction search for ‘Available to Promise’. I need a tallied sum that partitions over ‘item’ and then ‘location’. By trial and error I found that simply separating the fields by a comma does the trick here. Here’s my syntax:

    sum/* comment */(NVL({item.locationquantityavailable},0)+NVL({quantity},0)-NVL({quantityshiprecv},0)) OVER(PARTITION BY {item},{item.inventorylocation} ORDER BY {custcol_eta},{internalid},{linesequencenumber} ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

    Notice that I also used commas for the order by to get the tallied sum ordered accordingly.

  5. Posted July 24, 2015 at 6:37 pm | Permalink

    Thank you Matthew. Excellent, we need all the examples we can get of this powerful capacity!

  6. Posted August 25, 2015 at 9:00 pm | Permalink

    Thanks for your original post and the update. We are trying to solve a running total issue for inventory items which are impacted with issues (WO) and receipts (POs) where the quantity of the transaction is always pos. but we want issues to be subracted from the total and receipts to be added. Can you think of a way to use Robert’s solution to achieve a running total?

  7. Posted August 29, 2015 at 10:16 pm | Permalink

    Hi Jim,

    Couldn’t you create a conditional column based on transaction type to get quantity elements positive and negative and then use the technique against that?


  8. Jane
    Posted September 21, 2015 at 11:21 am | Permalink

    Thank you , this is so helpful!
    I am very new to NetSuite and using formulas in saved searches. I am finding your site to be invaluable.
    how would I tweak this formula if I just need the final total , and not a running total?

  9. Posted September 21, 2015 at 4:49 pm | Permalink

    Hi Jane,

    Did you try to use the “Show Totals” under the Results Tab?


  10. Jane
    Posted September 23, 2015 at 9:38 am | Permalink

    oh, I just realized it was that I had a typo on my formula when i was putting it in so yes..this was all I needed for my second column. Perfect..thanks!
    sum({amount})OVER(PARTITION BY {name})

  11. Posted October 23, 2015 at 12:51 pm | Permalink

    I have attempted this for the last 3 days with no luck. Looking to created a running total for inventory items to monitor what transactions are creating negative on hand balances, no luck.

  12. Posted October 24, 2015 at 12:10 pm | Permalink

    Hi Dan,

    Are you able to get any running totals working? Can you share more details to see if we can work through it?


  13. Elliot
    Posted November 2, 2015 at 10:24 am | Permalink

    Hi Dolly/Marty,

    I have the same problem where my formula matches the amount field, without any cumulative totals. Any idea what I’m doing wrong?

  14. Posted December 1, 2015 at 4:01 am | Permalink

    Hi Marty,

    I am facing a problem where in in one column i have count of all the transaction and in other the count of transaction with specific status. I now want a third with the percentage of above 2. Is it possible in Netsuite using saved search?

  15. Posted December 24, 2015 at 4:35 pm | Permalink

    Hi Mitisha,

    I don’t believe it can be done with native saved search as you need to get the grand total to then later divide the results. Naturally, this can be done if you take the saved search into SuiteScript.


  16. Posted March 16, 2016 at 5:53 am | Permalink

    Hi Marty,

    Thanks for this! I’ve used this in a couple of saved searches successfully, but now I’m trying to do the same with a summarized saved search. I’d like to get transaction YTD amounts, grouped by customer. I’ve tried different summary types, modifying the clauses, etc. I either get an Unexpected Error in saving or I get an invalid expression in the column. Any ideas?

    Also for Mitisha’s question, one might try to create a summarized saved search with a formula (Numeric), summary type MIN, and a formula like this:


    You can probably modify the count to include a case statement as well.

  17. Posted March 16, 2016 at 6:06 am | Permalink

    Hi George,

    You seem to be on the right track. Those error messages are common until you dial it in. I have no offer: it is trial and error.


  18. Craig
    Posted March 30, 2016 at 10:27 pm | Permalink

    I’d be interested to see if you can get this to work on a summarised saved search, too. I’ve been playing around for a while, but in SQL you’d usually use a CTE or similar to group the data before the partition.

  19. Alien
    Posted June 25, 2016 at 6:04 pm | Permalink

    I’m trying to make an Empty Bin Report, I can’t figure out how to sum all items on a Bin(could be different items) so if the SUM is = 0 mean that the Bin is empty and only show me that bins.

    Any help?

  20. Posted June 26, 2016 at 8:24 pm | Permalink

    Hello Alieno,

    Are you familiar with the Criteria and the Summary tab? That tab is similar to a SQL Having Clause which allows you to produce an Aggregate Count and then set specific criteria to see if the result set would qualify.


  21. Unai
    Posted August 26, 2016 at 11:00 am | Permalink

    It would be great if you could use the same approach using the analytic functions to perform a calculation after having grouped.
    I tried to do it by using a function the way it is described here, and it works. The problem is that then I want to group the result set, and it won’t let you. You can’t have the function as a group by.
    It’d be great if somebody knew how to do this

  22. Ashbaq Ahamed
    Posted February 28, 2017 at 2:49 am | Permalink

    Hi marty,

    I tried your formula for cummulative totals
    but it is showing error.

    SUM({custrecord_hm_bc_payroll_net_pay}) OVER(PARTITION BY {custrecord_hm_bc_payroll_batch})

    Summary type : SUM

    Can you please check, is that correct…?

    Thanks in advance.

  23. Posted March 18, 2017 at 6:20 am | Permalink

    Hello Ashbaq,

    The best I can offer is that it is trial and error and we all go through it. Be sure to really learn how Oracle works on these Analytical functions. Here is an article that may help you gain more insight.

  24. Stephen Gray
    Posted April 7, 2017 at 6:38 am | Permalink

    Hi Marty

    Slightly tenuous link to above but I’ve spent some time on this to no avail and I thought you might be able to help.

    I need to use the count() function in a formula. I’m trying to return the 2nd last sale transaction for each customer in a saved search. The logic I’m using is that if I use the rank formula to rank each transaction by date then add 1 to that and divide the result by the count of the transaction and that is equal to 1 then I’ve got the 2nd last transaction.

    My first step was to just use the count() function in any formula (e.g. COUNT({transaction.number}))
    but anytime I use count() in a formula NetSuite doesn’t even return the column in the results

    Any ideas?


  25. Stephen Gray
    Posted April 7, 2017 at 6:40 am | Permalink

    The reason I’m asking on this thread is because you’ve used sum() in a formula above. I cant seem to use any of the summary type functions in a formula


  26. Posted April 7, 2017 at 3:29 pm | Permalink


    These analytic formulas can not use the native Summary features which effectively are Equivalent to SQL Group By Operators.


  27. Posted April 7, 2017 at 3:31 pm | Permalink

    All the analytic functions should work including Count. See this link for a listing of Oracle PLSQL Analytic Functions.


  28. Marshall
    Posted September 14, 2017 at 5:29 am | Permalink

    Thanks for the info on this formula. However, it appears that when a product has more than one page it doesn’t calculate all the pages or at least doesn’t match our actual on hand numbers. I’m sure it’s something I’m doing or not doing. Would you be able to shed some light on it for us?

  29. Posted September 17, 2017 at 2:51 pm | Permalink

    Hi Marshall,

    I have found that you may need to think carefully about the order the transactions are in for your formula to be reliable. It’s hard for me to assess in general. But I have definitely been able to get the next page to get the right results once I gave careful consideration to the Partition By clause.


  30. Posted December 26, 2017 at 6:53 pm | Permalink

    Hi Marty,

    I’ve been looking for a way to do the 80/20 principle for god knows how long and I stumbled across your link from the NS forums. So I used your formula above as per your first screenshot (hoping to get something like that, total based on Amount)

    sum/* comment */({transaction.amount})
    ORDER BY {internalid}

    and this is just giving me the ‘amount’ from my ‘2017 Amount Sales’.

    I’ve been trying to convert this excel formula to your formula so that I can achieve the results I need. I’ve been trying for nearly 2 days and getting no-where.

    Excel Formula – =SUM($E$2:E3)/$E$10 (E2 being the 1st items sales amount and then going down the list E2 being the 2nd items sales amount) then dividing it by E10 (Total amount of sales).

    I’ve tried to do this –

    sum/* comment */({transaction.amount})/CASE WHEN TO_CHAR({transaction.trandate},’YYYY’)=’2017′ THEN {transaction.amount} ELSE NULL END
    ORDER BY {internalid}

    and tried this too

    sum/* comment */({transaction.amount})+({transaction.amount})/CASE WHEN TO_CHAR({transaction.trandate},’YYYY’)=’2017′ THEN {transaction.amount} ELSE NULL END
    ORDER BY {internalid}

    Either I get invalid expression or getting weird results like the same figure or ‘1’.

    is there any way you can help me, please? I’ve almost pulled out most of my hair.

    Thank you

  31. Posted January 6, 2018 at 12:29 pm | Permalink

    Hi Mayur,

    I believe you need additional handling for your nulls. You can test further if the result set returns a Null and if so, then possibly return a zero. See how this will return a Zero: ISNULL((45 / NULLIF( 0, 0 )),0)


  32. jeremy
    Posted January 26, 2018 at 5:16 pm | Permalink

    2018 and still relevant. Thank you guys

2 Trackbacks

  1. […] Solving the NetSuite Cumulative Saved Search Tally Challenge […]

  2. […] additional analysis can be performed.  The real opening to this solution is in my article, “Solving the NetSuite Cumulative Saved Search Tally Challenge‘.  Here, the trick is to learn how to drive an Oracle based aggregate search into a standard […]

Post a Comment

Your email is never published nor shared. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>