mPower Your Saved Search Skills
Author: Troy Pilkington
Saved searches are the lifeblood of data analysis within NetSuite, but sometimesthe data you want can feel just out of reach. Luckily for you, we’ve spent yearsaccumulating tips and tricks we use daily that help our clients get the resultsthey’re looking for. Below are a few of our most useful techniques that haveproven relevant across all industries and data types.
MAIN LINE
The Main Line functionality is fundamental to understanding the output of datawhen executing a transactional saved search. By adding Main Line to the criteria,you can fi lter the information of the transaction to line level data or header leveldata.
When Main Line is True, only header data will be shown. This is useful when youjust want to know the overall amount of a sales order, for example, instead ofdealing with each line of every order unnecessarily.
Main Line is False will give you access to line level information. Using amount inthis scenario, your results will show all lines in an order, including the amountfrom each line.
If you don’t use Main Line to separate your data, both header and line levelinformation will appear in the search, which can severely aff ect the way your numbersare displayed.
‘IS MINE’
Your sales reps want to see a report of their sales this month, but you don’t wantthem to see each other’s, nor do you want to have to create 10+ diff erentsearches. 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, thenchoosing -Mine- in the pop-up dialog box, the search will dynamically displaythe results for the Sales Rep viewing the search. This is also useful when creatingsearches 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 asaved search of all the sales to Excel and spend the time massaging the data tofi nd the requested data. A Case When statement in a Formula column is useful insituations like this.
By using a Case When formula in a Results column, you can dynamically displaythe totals for each location (or any other segment) in their own column. Here’show 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 bySales Rep, each line would either show the total order amount if the linerepresents a Omaha order (as long as Main Line is True in your criteria), or 0.00if the order was for a diff erent location. What to do with all these zeroes? Thenext tip will explain.
GROUPING
Grouping your results is a highly eff ective way to summarize data into tolerableresults. By fi lling in the Summary Type column in the Results lines of your savedsearch, you can consolidate your data as you want. Here is an explanation of thediff erent Summary Types:
Group – This is the fi rst decision to make when grouping data. What do I want tosee the results split by? In the example above, the answer is Sales Rep. By usingthe Group Summary Type in the Sales Rep line of your results, you will only seeone line per rep. The rest of the summarized data depends on what otherSummary Types you choose for your other Results lines.
Sum – the most used Summary Type alongside Group is Sum. Sum will add upthe data in each line that applies to the Grouped category. For example, if youwanted 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 Formula line with the Case Whenstatement to total up the sales for Omaha, while still only having one line persales rep, which is the Grouped attribute.
Count, Minimum, Maximum, Average – the rest of the Summary Types besidesSum all work similarly alongside Grouped 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 smallestcustomer (Minimum).
Mastering saved searches takes years of time and troubleshooting, but pluggingthese tips and tricks into a growing catalog of expertise will help any NetSuiteuser on their journey! Want to learn more about saved searches?
