Published on December 11, 2021.

I was recently asked by a NetSuite client to add a new sales report to their custom management portal. This is a client that I first wrote about in October of 2021, when I added timesheets-related functionality to their custom employee portal.

The goal of the requested report is to provide managers with a quick overview of their sales pipeline. For each sales rep, they want to see the total of the rep's open sales orders, the total of their open estimates (where the probability of closing the sale is greater than or equal to 70%), and the sales rep's supervisor.

In this post, I'll share the SuiteQL query that I developed to fulfill this request, and the steps that I went through to create it. I'll also introduce the SQL WITH clause, and explain why and how I used it in this query.

But first, so that you can get a sense of how the query is used in the portal, here's a quick animation that shows the report.

Sales Totals By Rep

The first query that I developed sums up the sales orders for each rep.

Here's the query.

SELECT
	Employee AS RepID,
	SUM( ForeignTotal ) AS ForeignTotal
FROM
	Transaction
WHERE
	( ( Type = 'SalesOrd' ) AND ( BUILTIN.CF( Status ) = 'SalesOrd:G' ) )
GROUP BY
	Employee

The results look like this.

As you can see, I'm simply filtering on the Transaction table, and requesting Sales Orders ( Type = 'SalesOrd' ) that are open ( BUILTIN.CF( Status ) = 'SalesOrd:G' ). I'm using the BUILTIN.CF function on the table's Status column to filter for the open orders.

That's a good start. However, we also need the total of each rep's estimates.

Estimate Total by Rep

To get the total estimates by rep, I made a few changes to the original query.

SELECT
	Employee AS RepID,
	SUM( ForeignTotal ) AS ForeignTotal
FROM
	Transaction
WHERE
	( ( Type = 'Estimate' ) AND ( BUILTIN.CF( Status ) = 'Estimate:A' ) AND ( Probability >= .70 ) )
GROUP BY
	Employee

In this query, I'm specifying that I want transactions of type "Estimate," that the status of the transactions should be Open, and that I only want estimates where the probability of closing the sale is greater than or equal to 70%. Notice the subtle difference in the Status criteria. In this case, I'm using the BUILTIN.CF on the Status column, and the value that I'm requesting is "Estimate:A" (which is the "Open" status for estimates.

Understanding the Transaction Statuses, BUILTIN.CF, and the BUILTIN.DF Functions

If you're not familiar with transaction statues, take a look at this query.

SELECT DISTINCT
	Type,
	Status,
	BUILTIN.CF( Status ) AS CFStatus,
	BUILTIN.DF( Status ) AS DFStatus
FROM
	Transaction
ORDER BY
	Type,
	Status

The results will look something like this.

The table shows the various statuses for each transaction type. The "status" column is essentially a code for the status. The "cfstatus" value is based on the BUILTIN.CF function. It's that value that we can use in the WHERE clause of SuiteQL queries run against the Transactions table. The "dfstatus" value is based on the BUILTIN.DF function, and it's the one that you'll most likely want to use when you want to display transaction statuses in reports.

Combining the Two Queries

At this point, we have two queries. One totals the sales orders by reps, and the other totals the estimates by reps. Let's combine those two queries.

SELECT
	Employee AS RepID,
	Type,	
	SUM( ForeignTotal ) AS ForeignTotal
FROM
	Transaction
WHERE
	( ( Type = 'SalesOrd' ) AND ( BUILTIN.CF( Status ) = 'SalesOrd:G' ) )
	OR ( ( Type = 'Estimate' ) AND ( BUILTIN.CF( Status ) = 'Estimate:A' ) AND ( Probability >= .70 ) )
GROUP BY
	Employee,
	Type

That's a little better. However, we now have potentially two rows for each rep - one with the total sales orders, and the other with the total estimates. What we really want is a single row for each rep, with the two totals as individual columns in each rows. In other words, we sort of want the SQL equivalent of a "pivot."

Using the WITH Clause

In order to handle the pivot, I'm going to use a SQL clause that I haven't mentioned in previous posts. It's the WITH clause, and it helps to make complex queries a little easier to develop and manage.

Using the WITH clause, we can create one or more subqueries, and then refer to them in the SELECT statement. It's as if those subqueries become SQL views or virtual tables. And once we've defined them, we can do all sorts of interesting things with them - filter on them, join to them, and so on. These subqueries are often referred to as "common table expressions" (CTEs).

The WITH clause is most helpful when you need to refer to the same subquery multiple times. In this example, I'm using it to simplify the query.

Here's the query.

WITH 
	SalesSummary AS
		(
			SELECT
				Employee AS RepID,
				Type,	
				SUM( ForeignTotal ) AS ForeignTotal
			FROM
				Transaction
			WHERE
				( ( Type = 'SalesOrd' ) AND ( BUILTIN.CF( Status ) = 'SalesOrd:G' ) )
				OR ( ( Type = 'Estimate' ) AND ( BUILTIN.CF( Status ) = 'Estimate:A' ) AND ( Probability >= .70 ) )
			GROUP BY
				Employee,
				Type
		)
SELECT
	RepID,
	( Employee.LastName || ', ' || Employee.FirstName ) AS RepName,
	( Supervisor.LastName || ', ' || Supervisor.FirstName ) AS Supervisor,
	SUM ( CASE WHEN Type = 'SalesOrd' THEN ForeignTotal ELSE 0 END ) AS SalesTotal,
	SUM ( CASE WHEN Type = 'Estimate' THEN ForeignTotal ELSE 0 END ) AS EstimatesTotal
FROM
	SalesSummary
	INNER JOIN Employee ON
		( Employee.ID = SalesSummary.RepID )
	INNER JOIN Employee AS Supervisor ON
		( Supervisor.ID = Employee.Supervisor )
GROUP BY
	RepID,
	( Employee.LastName || ', ' || Employee.FirstName ),
	( Supervisor.LastName || ', ' || Supervisor.FirstName )
ORDER BY
	RepName

Notice that the query starts with the WITH clause. I'm using it to take the previous query and define it as a subquery named SalesSummary. At that point, I can refer to the subquery just as if it were a table. (Note that you can define multiple subqueries with the WITH clause.)

Once the subquery has been defined, the actual SELECT clause is pretty straightforward. I'm joining to the Employee table twice, once to get the employee record associated with the salesrep, and a second time to get the rep's supervisor. I'm concatenating the first and last names to get the full names of the reps and supervisors.

I'm using CASE statements to determine if a row is a total of sales orders or estimates. I'm grouping by the rep and their supervisor, and sorting the resulting rows by the employee's name.

Wrapping Up

This was a fun project to work on, and a nice addition to my client's portal. In the portal version of the report, users can click on a sales rep to "drill down" into the transactions that are being summarized.

I hope you find this query to be helpful. Examining it is a good way to learn about a number of SuiteQL-related topics, including transaction statuses and how you can use the BUILTIN.CF function to filter on them, the WITH clause, and how you can simulate pivoting.