NetSuite: Using SuiteQL to Get Order Status Summaries

A NetSuite client recently asked me for help with a couple of queries that are being used to present some high level, summarized data on a management dashboard application. I thought I'd share those queries in this post.

Sales Order Status Summary

The first request was for a query that would list the various statuses of sales orders, with the number of orders in each status, and the total amounts. Here's the query that I created.

SELECT
	Status,
	BUILTIN.DF( Status ) AS StatusName,
	COUNT( * ) AS TransactionCount,
	SUM( ForeignTotal ) AS TotalAmount
FROM 
	Transaction 
WHERE 
	( Type = 'SalesOrd' )
GROUP BY
	Status,
	BUILTIN.DF( Status )
ORDER BY
	Status

The reason that I'm including both the Status column (the character code for the status) and the status name (which I'm getting using the BUILTIN.DF function) is so that I can sort the statuses in a logical order, based on how sales order transactions progress through NetSuite. So the first transactions that are listed are those pending approval, followed by orders that are pending fulfillment, and so on.

Purchase Order Status Summary

The second request was for a similar query but based on purchase orders. Here's the query.

SELECT
	Status,
	BUILTIN.DF( Status ) AS StatusName,
	COUNT( * ) AS TransactionCount,
	SUM( ( ForeignTotal * -1) ) AS TotalAmount
FROM 
	Transaction 
WHERE 
	( Type = 'PurchOrd' )
GROUP BY
	Status,
	BUILTIN.DF( Status )
ORDER BY
	Status

This query is very similar to the one used to summarize sales orders. One difference is the transaction type that I'm filtering on. In this case, I'm asking for transactions of type "PurchOrd." The other difference is that I'm requesting the sum of the negative values of the transactions. The reason for this is that the amounts for purchase order transactions are stored as negative values.

Wrapping Up

The queries that I've shared above are very basic, but I receive these types of requests quite often. I hope that you find them to be helpful.

If you have any questions about the queries, or SuiteQL in general, please feel free to contact me.