NetSuite Saved Searches as Pivot Tables


One of the great features of NetSuite is the “Export to Excel” function which allows users to export data to Excel. Using this feature, users can export a large set of data to Excel and manipulate that data outside NetSuite using Pivot Tables and other formulas. However, a user should not discount the ability of NetSuite to accomplish much—if not all—of this functionality directly within the product.

NetSuite Saved Searches accommodate the use of SQL functions directly within the search results. Using this functionality, reports can be built that employ a greater level of logic and control than using only the standard user interface. Through the use of SQL functions in NetSuite Saved Search results, we can accomplish “if/then” logic, pivot tables, and other functionality ordinarily reserved for Excel. Building these reports directly within NetSuite helps to further centralize access to the data, and eliminates yet another document from being passed around via email. Your search, like all other NetSuite searches, will always display the most up-to-date information because it’s built on the data in your account.

For example, you want to analyze support case metrics for your customer service team. You would like to see how many cases are at each status within your four case types. The resulting report is a Grid, with the “Types” down the left column, and the “Statuses” across the top row. The summarized count of data for combination makes up the data of the table. It’s a pivot table in Excel, and we can build it directly in NetSuite without any custom development.

Tip Image 1
Click image to enlarge

We accomplish this by using Numeric formulas in our search results, and a CASE statement as the formula. Each of our columns across the top of our report (case statuses, in this example) will need a result column in our search, each with its own basic CASE statement. With each of these columns and statements, we will be telling the system “when the case status is a, add to the tally for a and only a. When the case status is b, add to the tally for b and only b,….”

Tip Image 2
Click image to enlarge

Note that each column has used the “Sum” type to summarize each of those tallies. Additionally, we’ve added the “Type” field from the case, and used the “Group” feature. In this way, we are Grouping by Type, and summarizing the count of case statuses for each status. Finally, we gave each column a clean Summary Label to represent the Case Status (in the far right column).

When the search is executed in NetSuite, the results look just like an Excel Pivot Table:

Tip Image 3
Click image to enlarge

This same approach can be used for many types of data analysis in your NetSuite account, such as:

Not only can we perform the searching and reporting directly within NetSuite, we can also control, using the built-in interface, the details of presentation, as well as publish this to dashboards, link from shortcuts, and otherwise leverage the standard NetSuite functionality.