Saved searches are the lifeblood of data analysis within NetSuite, but sometimes the data you want can feel just out of reach. Luckily for you, we’ve spent years accumulating tips and tricks we use daily that help our clients get the results they’re looking for. Below are a few of our most useful techniques that have proven relevant across all industries and data types.
MAIN LINE
The Main Line functionality is fundamental to understanding the output of data when executing a transactional saved search. By adding Main Line to the criteria, you can filter the information of the transaction to line-level data or header-level data.
When Main Line is True, only header data will be shown. This is useful when you just want to know the overall amount of a sales order, for example, instead of dealing with each line of every order unnecessarily.
Main Line is False will give you access to line level information. Using the amount in this scenario, your results will show all lines in order, including the amount from each line.
If you don’t use Main Line to separate your data, both header and line-level information will appear in the search, which can severely affect the way your numbers are displayed.
‘IS MINE’
Your sales reps want to see a report of their sales this month, but you don’t want them to see each other’s, nor do you want to have to create 10+ different searches. A handy criterion to keep in mind for these types of requests is the ‘ismine’ feature.
By adding ‘Sales Rep’ to the Criteria tab of the saved search settings, then choosing -Mine in the pop-up dialog box, the search will dynamically display the results for the Sales Rep viewing the search. This is also useful when creating searches for Support Reps, Account Managers, Technicians, and more.
CASE WHEN
Your CEO wants to see last month’s sales by location, but you don’t want to pull a saved search of all the sales to Excel and spend the time massaging the data to find the requested data. A case-when statement in a formula column is useful in situations like this.
By using a Case When formula in a Results column, you can dynamically display the totals for each location (or any other segment) in their own column. Here’s how the formula works:
- CASE WHEN comparison_expr THEN return_expr ELSE else_expr END
- Looks confusing, but less so when you see an example:
- CASE WHEN {location} = ‘Omaha’ THEN {amount} ELSE 0.00 END
If you were to use this formula for your location on a search for Sales Orders by Sales Rep, each line would either show the total order amount if the line represents an Omaha order (as long as Main Line is True in your criteria), or 0.00 if the order was for a different location. What to do with all these zeroes? The next tip will explain.
GROUPING
Grouping your results is a highly effective way to summarize data into tolerable results. By filling in the Summary Type column in the Results lines of your saved search, you can consolidate your data as you want. Here is an explanation of the different summary types:
Group – This is the first decision to make when grouping data. What do I want to see the results split by? In the example above, the answer is Sales Rep. By using the Group Summary Type in the Sales Rep line of your results, you will only see one line per rep. The rest of the summarized data depends on what other Summary Types you choose for your other Results lines.
Sum – the most used summary type alongside group is sum. Sum will add up the data in each line that applies to the grouped category. For example, if you wanted to show sales totals for each of your sales reps, you would group by SalesRep and sum by amount.
In the example above, we would use Sum on the Formula line with the Case When statement to total up the sales for Omaha while still only having one line per sales rep, which is the Grouped attribute.
Count, Minimum, Maximum, Average – the rest of the summary types besides Sum all work similarly alongside group results. In each case, the SummaryType works to output the desired data, whether it be number of sales orders (count), average deal size (average), largest sale (Maximum) or smallest customer (Minimum).
Mastering saved searches takes years of time and troubleshooting, but plugging these tips and tricks into a growing catalog of expertise will help any NetSuite user on their journey! Want to learn more about saved searches?
