Learn How To Craft a NetSuite ABC Analysis Report Using Advanced SuiteQL Techniques

This article is relevant if you are looking to create a cumulative or running total using NetSuite’s SuiteQL technology.

Background

Back in 2015, I published an article, Solving the NetSuite Cumulative Saved Search Tally Challenge, highlighting a breakthrough method to drive NetSuite: embedding a comment in a Saved Search formula to leverage an Oracle PLSQL Analytical Function within NetSuite’s internal SQL engine. This helped solve a common challenge that frequently presents itself in business data: determining the percent of a total.

Fast forward over eight years, and our firm has made great advances by utilizing NetSuite SuiteQL technologies. Moving past the familiar data reach limitations of NetSuite Saved Search, we’re now tackling the unique challenges posed by SuiteQL. As of now, NetSuite SuiteQL doesn’t support key Oracle Database Analytical Functions, with the SUM([expression]) OVER() (ORDER BY [expression]) function being particularly challenging due to its absence. 

While strategizing with the Management Team for the new year, we aimed to gain a deeper understanding of our revenue distribution across customers, guided by the Pareto principle: a significant portion of our revenue comes from a smaller segment of our clients. To analyze this, we needed to execute a query that would facilitate an ‘ABC’ revenue categorization by customer.

In solving this challenge, I’ve uncovered valuable patterns and insights crucial for anyone looking to drive and report with NetSuite SQL aggregates.

Patterns to Overcome Missing NetSuite Oracle Analytical Functions

Creating an ABC report using SuiteQL presents a valuable learning opportunity for NetSuite Administrators and Developers, especially in navigating some of the more intricate SuiteQL patterns. Here’s a step-by-step strategy to generate this report to aid your understanding of the SuiteQL below:

1. Collecting Detailed Data: Start by gathering detailed data, focusing on the income generated by each customer.
2. Aggregation and Ranking: Aggregate the income data and rank it from highest to lowest. NetSuite’s support for the DENSE_RANK analytical function is beneficial here; otherwise, I would have had to be a bit more creative.
3. Calculating Total Income: Due to the absence of the SUM Analytical Function in SuiteQL, acquire the total income through a separate query.
4. Determining Percentage of Total: With the total income figure, calculate each customer’s income as a percentage of this total. This step is often crucial in addressing concerns such as profit margin calculations.
5. Cumulative Income and Percentage: SuiteQL lacks the Oracle Analytical function for cumulative calculations (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Overcome this by executing self-table-joins to accumulate values appropriately.
6. Row Segmentation: Utilize the accumulated percentage data to categorize rows into ‘A,’ ‘B,’ or ‘C’ segments based on predefined threshold criteria.

This “walking the data approach” provides a successful method to analyze and segment income data effectively within the constraints and capabilities of NetSuite’s SuiteQL.

Presenting NetSuite SQL to Accmulate, Rank and Segment

The SQL patterns outlined here are versatile solutions for a variety of standard business aggregation needs within NetSuite. The SQL runs in various NetSuite add-on query tools. We provide our clients with a no-cost tool for this purpose, as detailed in our 2021 article NetSuite SQL Query Tool. Once the queries are crafted, integrating them into our complimentary SQL Rendering and Reporting tools is a logical step, enhancing the end-user experience.

-- first get revenue transactions for date period; 
-- I offer more columns than actually needed as they may be valuable for other segmentation exercises;
-- the WITH syntax allows us to treat the results as a table in subsequent queries
WITH revenue AS (
	SELECT
		transaction.trandate
		,transaction.type
		,transaction.tranid
		,transactionline.accountinglinetype
		,BUILTIN.DF(customer.id) AS customer_text
		,BUILTIN.DF(transactionline.item) AS item_text 
		,NVL(transactionline.creditforeignamount,0) - NVL(transactionline.debitforeignamount,0) AS income
	FROM transaction 
	INNER JOIN transactionline ON (transaction.id = transactionline.transaction AND transactionline.accountinglinetype = 'INCOME')
	INNER JOIN customer ON transaction.entity = customer.id
	WHERE transaction.posting = 'T' AND transaction.trandate => '01-01-2011'  -- format according to account date
	ORDER BY customer.id
),

-- develop income ranking by customer using SuiteQL analytical function;
-- fortunately, DENSE_RANK is supported which will sort and provide us an important index for subsequent queries;
-- change customer_text to another value if you want 
rank AS
(
	SELECT 
		DENSE_RANK() OVER(ORDER BY ROUND(SUM(income),2) DESC) AS ranking
		,customer_text
		,ROUND(SUM(income),0) AS income
	FROM revenue
	GROUP BY customer_text
)
,
-- get the total revenue from the ranking work so we can produce a percentage
-- we need this because NetSuite SQL does not support SUM Analytical functions
total as (
	SELECT ROUND(SUM(income),2) AS income_total
	FROM rank 
)

-- get the data from rank using self-joins and the fact we have an ordered list to cumulate;
-- using a cartesian join, we get all the values from the total table as well;
-- using subselects, we gather data from the other tables
SELECT 
	r1.ranking
	,r1.customer_text
	,TO_CHAR(r1.income, '99,999,999') AS income
	,TO_CHAR(income_total, '99,999,999') as income_total
	,TO_CHAR((SELECT SUM(income) FROM rank r2 WHERE r2.ranking <= r1.ranking), '99,999,999') AS income_cumulative
	,TO_CHAR(ROUND(r1.income/income_total * 100, 2)) || '%' as percentage
	,TO_CHAR(ROUND((SELECT SUM(income) FROM rank r2 WHERE r2.ranking <= r1.ranking) / income_total * 100,2)) || '%' as percentage_cumulative
	,CASE 
		WHEN (SELECT SUM(income) FROM rank r2 WHERE r2.ranking <= r1.ranking) / income_total * 100 < 20 THEN 'A'
		WHEN (SELECT SUM(income) FROM rank r2 WHERE r2.ranking <= r1.ranking) / income_total * 100 < 80 THEN 'B'
		ELSE 'C'
	  END AS 'segment'
FROM rank r1, total
ORDER BY ranking

Click the image to see the resulting data set.

Embracing Expertise: Systems Integration Philosophy and the Value of Service in NetSuite Mastery

Our approach to this project aligns with the core principles of our firm. As specialists in Systems Integration, we dedicate ourselves to refining our expertise in NetSuite, continually deepening our understanding to devise innovative solutions for our clients. We place great emphasis on selecting team members who not only possess strong technical knowledge but also those seeking to grow their leadership qualities. As a service-focused firm, we offer our clients all our Intellectual Property (known as Labs) without licensing fees. Our belief lies in attentive listening and addressing client needs by being innovative yet holding our integrity.

If you found this article relevant, feel free to sign up for notifications to new articles as I post them. If you possess exceptional skills and are interested in joining a world-class practice, let’s have a conversation.

Marty Zigman

Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - YouTube