Creating a Dynamic Transaction Saved Search in NetSuite

NetSuite saved searches are a powerful way to gain insight into your business data. At Left Ledger Inc., we’re always looking for creative ways to help you make the most of your NetSuite environment. In this post, we’ll walk through how to build a transaction saved search that groups data by month and compares this year’s revenue against last year’s. Plus, we’ll show you how to sort the months in calendar order and even add a “Delta” column that highlights performance differences in green or red!

Step 1: Build a Transaction Saved Search Grouped by Month

First, we need to create a saved search that groups your transaction data by month. Follow these steps:

1. Create a New Transaction Saved Search:

   Navigate to Reports > Saved Searches > All Saved Searches > New and select Transaction.

2. Group by Month:

   In the Results tab, add a field that groups your transactions by month. You can use the built?in date grouping by selecting Transaction Date : Month or create a custom formula:

   TO_CHAR({trandate}, 'Month')

   Set the Summary Type for this field to Group. This will generate up to 12 rows (one for each month with data).

3. Add Revenue Columns:

   Add two additional columns to display revenue totals for:

   • This Year:

     Create a Formula (Numeric) field with the Summary Type Sum. Use a formula such as:

     CASE WHEN TO_CHAR({trandate}, 'YYYY') = TO_CHAR({today}, 'YYYY') THEN {amount} ELSE 0 END

   • Last Year:

     Similarly, add another Formula (Numeric) field with the Summary Type Sum:

     CASE WHEN TO_CHAR({trandate}, 'YYYY') = TO_CHAR({today}, 'YYYY') - 1 THEN {amount} ELSE 0 END

These steps will give you three columns: Month, This Year Revenue, and Last Year Revenue.

---

Step 2: Sorting by Calendar Month Order

By default, grouping by month may not yield a calendar order (January to December). To ensure the rows are sorted correctly, follow these additional steps:

1. Add a Hidden Month Number Column:

   Create a new Formula (Numeric) field in the Results tab with a Summary Type of Group:

   TO_NUMBER(TO_CHAR({trandate}, 'MM'))

   This returns a numerical value (1 for January, 2 for February, etc.).

2. Sort by the Month Number:

   In the sorting options, add this new column and set it to sort in ascending order.

3. Hide the Column (Optional):

   If you don’t want the month number to appear in your final results, mark this column as Hidden.

Now, your saved search will display the months in proper calendar order from January to December.

---

---

Step 3: Adding and Colorizing the “Delta” Column

To give an instant visual cue on performance, you can add a “Delta” column that calculates the difference between this year’s and last year’s revenue. You can also colorize the results to show green for positive growth and red for negative.

---

1. Add the Delta Column:

   Add another Formula (Numeric) field to the Results tab, setting the Summary Type to Sum:

   CASE 
       WHEN TO_CHAR({trandate}, 'YYYY') = TO_CHAR({today}, 'YYYY') THEN {amount}
       ELSE 0 
   END
   -
   CASE 
       WHEN TO_CHAR({trandate}, 'YYYY') = TO_CHAR({today}, 'YYYY') - 1 THEN {amount}
       ELSE 0 
   END

   Label this column as Delta.

2. Apply Conditional Highlighting:

   Go to the Highlighting tab to set up conditional formatting:

   • Green for Positive Delta:

     Create a condition for the Delta field where the value is greater than 0. Choose a green background (or text color).

   • Red for Negative Delta:

     Create another condition for the Delta field where the value is less than 0, and select a red color.

When you run your saved search, you will see the “Delta” column displaying the month-over-month difference, with visual cues that help you quickly identify months with growth or decline.

Final Thoughts

By leveraging formula fields and summary types in NetSuite saved searches, you can create dynamic, visually engaging reports that offer insights at a glance. This step-by-step guide has shown how to compare this year’s revenue against last year’s month by month, sort the results in calendar order, and even highlight performance changes.

At Left Ledger Inc., we specialize in tailoring NetSuite solutions to meet your unique business needs. If you have questions or need further customization, feel free to reach out to our team!