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.

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.

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 set up a conversation.

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

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

35 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!

    Dolly.

  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

    Marty,
    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?

    Marty

  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?

    Marty

  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?

    Marty

  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.

    Marty

  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:

    sum({amount})/count({internalid})

    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.

    Marty

  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.

    Marty

  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
    Formula(numeric)

    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?

    Thanks
    Stephen

  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

    thanks
    Stephen

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

    Stephen,

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

    Marty

  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.

    Marty

  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.

    Marty

  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})
    OVER(PARTITION BY {name}
    ORDER BY {internalid}
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

    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
    OVER(PARTITION BY {name}
    ORDER BY {internalid}
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

    and tried this too

    sum/* comment */({transaction.amount})+({transaction.amount})/CASE WHEN TO_CHAR({transaction.trandate},’YYYY’)=’2017′ THEN {transaction.amount} ELSE NULL END
    OVER(PARTITION BY {name}
    ORDER BY {internalid}
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

    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)

    Marty

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

    2018 and still relevant. Thank you guys

  33. Don
    Posted July 19, 2018 at 9:58 am | Permalink

    I realize this is a really old post but seems like it’s still getting comments so…

    Any idea why this seems to work on some record/field combinations but not others? Here’s a test setup. I’m searching an item record for a specific kit. The kit has 2 member items and 6 transaction records.

    This formula returns 1 row with the value 2 for the 2 member items: count /*comment*/ ({memberitem.internalid}) over (partition by {internalid})

    This formula returns 6 rows with the value 6 for the 6 transactions: count /*comment*/ ({transaction.internalid}) over (partition by {internalid})

    So I get the expected results count when using member item fields but I get duplicate rows when using transaction fields?

  34. Posted July 22, 2018 at 12:30 pm | Permalink

    Hi Don,

    Most likely, you are reaching out via a join to a transaction and you need to set the join condition to be mainline = true.

    Also, see this article on how to search kits: http://blog.prolecto.com/2016/07/10/how-to-generate-netsuite-kit-item-members-weighted-price/

    Marty

  35. S Feld
    Posted November 3, 2018 at 9:18 pm | Permalink

    Hi Marty,

    I’m trying to utilize this cumulative search as a search criteria (within a CASE function see below) but i keep on getting error when trying to execute, i’m trying to figure out if it should be working and I’m doing something wrong or this simply doesn’t work under as a part of the criteria

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>