Sunday, July 7, 2019

Round the Decimal Numbers Returned by the % of Total Function in Saved Search Results

When selecting % of Total from the Function drop down menu in the Saved Search Results tab, NetSuite shows the results with 5 decimal places. The functions in the Function menu are applied last to the search, therefore its not possible to modify the results and round the numbers.
A workaround for this scenario is to use the RATIO_TO_REPORT() OVER () formula in the Formula column, and then select one of the Round functions from the Function drop down menu.

The steps below show how to create a Transaction Saved Search to show the total sales per customer and the percentage related to the total sales of the company. The search results include both scenarios described before for the purpose of comparison.

1. Navigate to Lists > Search > Saved Searches > New.
2. Select Transaction.
3. Under the Criteria tab > Standard subtab add two filters:

  • Type is any of Cash Sale, Invoice.
  • Main Line is true.

4. Under the Results tab > Columns subtab add four fields:

  • Field = Customer (Main Line)Fields… ID
    Summary Type = Group.
  • Field = Amount
    Summary Type = Sum.
  • Field = Formula (Percent)
    Summary Type = Sum
    Function = Round to Hundreds
    Formula = RATIO_TO_REPORT(sum({amount})) OVER () 
  • Field = Amount
    Summary Type = Sum
    Function = % of Total.

5. Click Save.

No comments:

Post a Comment