How to Create Running Totals in NetSuite Saved Searches and Reports

Running Balance in NetSuite Reports:NetSuite provides a direct option for adding a running balance column when customizing reports. This feature is available for specific default columns and any columns added to a report. By checking the "Running Balance" option for a chosen column, a new column displaying the cumulative balance will be appended to its right.Running Totals in NetSuite Saved Searches:For saved searches, running totals can be achieved using SQL analytic functions directly within a formula field. NetSuite's saved search functionality supports expressions like SUM() OVER() to calculate cumulative values. For example, to get a running total of an amount field, a formula similar to SUM({amount}) OVER (ORDER BY {date_field}) could be used, where {amount} is the field to sum and {date_field} defines the order of accumulation.Legacy SuiteScript Approach (SuiteScript 1.0):Historically, achieving running totals in saved searches sometimes involved custom SuiteScript. The following example demonstrates a SuiteScript 1.0 pattern for processing search results and populating a sublist. It is important to note that SuiteScript 1.0 is considered legacy, and SuiteScript 2.x is the current standard for new development. This approach typically requires advanced scripting knowledge.

javascript// assumes you setup the search and results which // include anypreprocessing to add running totals if ( searchresults.length > 0 )    {        // first, add the columns to the sublist based on the columnsin the results        var result  searchresults[0];        var column_list  result.getAllColumns();        var col_len  column_list.length;        for (i0; i<col_len; i++)        {            var col  column_list[i];            var col_name  col.getName();            var col_label col.getLabel();            var col_form  col.getFormula();            var col_func  col.getFunction();            nlapiLogExecution('DEBUG', 'column name, label, formula,function', col_name + '|'+ col_label + '|'+ col_form + '|'+ col_func);            // if there is no label, use the field name            if (!col_label)            {                col_label  col_name;            }            var fld_name  'custpage_fld_' + i;        }        // now, show the results; loop through each result        var rlen searchresults.length;        for (ctr0;ctr<rlen;ctr++)        {            var result  searchresults[ctr];            // for each result, loop the columns            for (i0; i<col_len; i++)            {                var col  column_list[i];                var col_name  col.getName();                var col_label col.getLabel();                // if there is no label, use the field name                if (!col_label)                {                    col_label  col_name;                }                var fld_name  'custpage_fld_' + i;                var value  result.getText(col);                if (!value)                {                    value  "";                }                if (value.length  0)                {                    value  result.getValue(col);                }                var lin  parseInt(ctr) + parseInt(1);                if (value)                {                    YOURLIST.setLineItemValue(fld_name, lin, value);                }            }        }    }