Published on September 6, 2022.

One of my NetSuite clients is growing rapidly, and as a result, so is their NetSuite usage - especially the number of transactions that they are generating. They're concerned that they'll need to upgrade their NetSuite Service Tier. The service tier determines the number of transaction lines that can be processed each month, as well as the number of users, the size of files stored in an account's File Cabinet, and more.

My client asked me to create a query that they can use to determine their Transaction Line Usage. Specifically, they wanted to know the number of transaction lines being generated each month over the past 6 months (which is what NetSuite uses to determine the best service tier for an account).

In this post, I'll share the query that I developed for this client, and a few additional queries that I ended up writing as well.

Determining Provisioned and Used Transaction Lines

To get an account's provisioned and used Transaction Lines, navigate to: Setup > Company > Billing Information

As I mentioned above, usage is based on a rolling average over the past 6 months.

Number of Transaction Lines Generated In Last 6 Months

The first query that I developed for this client counts the number of transaction lines that have been generated each month for the past 6 months.

Here's that query.

SELECT
	* 
FROM 
	(
		SELECT
			TO_CHAR( Transaction.CreatedDate, 'YYYY-MM' ) AS TranMonth,
			COUNT(*) AS TranLineCount
		FROM
			Transaction
			INNER JOIN TransactionLine ON
				( TransactionLine.Transaction = Transaction.ID )
		WHERE
			( Transaction.CreatedDate BETWEEN BUILTIN.RELATIVE_RANGES( 'LRH', 'START' ) AND BUILTIN.RELATIVE_RANGES( 'LRH', 'END' ) )
		GROUP BY
			TO_CHAR( Transaction.CreatedDate, 'YYYY-MM' ) 
	)
ORDER BY
	TranMonth

I'm using the TO_CHAR function on the "Transaction.CreatedDate" to calculate a combination of the year and month that each transaction was created. The data itself is sourced from the Transaction table, and I'm joining to the TransactionLine table so that I can get a count of the lines.

Also, notice that I'm using dynamic calendar ranges to filter the transactions based on their creation date. I wrote about dynamic calendar ranges, and how you can use them via the BUILTIN.RELATIVE_RANGES function, in this post: https://timdietrich.me/blog/netsuite-suiteql-dynamic-calendar-date-ranges/

In this case, I'm using the start and end dates of the "Last Rolling Half." In other words, the start and end dates for the past 6 months.

If you want to see what those dynamic dates are, you can run this query:

SELECT
	BUILTIN.RELATIVE_RANGES( 'LRH', 'START' ) AS LAST_ROLLING_HALF_START,
	BUILTIN.RELATIVE_RANGES( 'LRH', 'END' ) AS LAST_ROLLING_HALF_END
FROM
	Dual	

One final comment about this first query: You might have noticed that I'm using a subquery, and that the outer query is simply sorting its results. The reason for this is that I've been running into issues when using a combination of GROUP BY and ORDER BY. This technique of treating the main query as a subquery, and ordering the results in the outermost query, is a workaround.

Number of Transaction Lines Generated In Last 6 Months, Grouped By Transaction Type

After presenting my client with the first query and its results, they asked for a summary of the transaction lines based on the transaction types. To get those values, I modified the original query slightly.

SELECT
	* 
FROM 
	(
		SELECT
			TO_CHAR( Transaction.CreatedDate, 'YYYY-MM' ) AS TranMonth,
			Transaction.Type AS TranType,
			COUNT(*) AS TranLineCount
		FROM
			Transaction
			INNER JOIN TransactionLine ON
				( TransactionLine.Transaction = Transaction.ID )
		WHERE
			( Transaction.CreatedDate BETWEEN BUILTIN.RELATIVE_RANGES( 'LRH', 'START' ) AND BUILTIN.RELATIVE_RANGES( 'LRH', 'END' ) )
		GROUP BY
			TO_CHAR( Transaction.CreatedDate, 'YYYY-MM' ),
			Transaction.Type
	)
ORDER BY
	TranMonth,
	TranType

I've simply added the "Transaction.Type" column to the results, and that I'm now grouping on the year/month combination as well as the transaction type. I'm also sorting the results based on the year/month combination and transaction type.

Number of Transactions Generated In Last 6 Months, By Transaction Type

My client asked for one additional query. They wanted to know the total number of transactions (not lines, but the actual transactions) for the past 6 months. Here's what that query looked like.

SELECT
	* 
FROM 
	(
		SELECT
			TO_CHAR( Transaction.CreatedDate, 'YYYY-MM' ) AS TranMonth,
			Transaction.Type AS TranType,
			COUNT(*) AS TranCount
		FROM
			Transaction
		WHERE
			( Transaction.CreatedDate BETWEEN BUILTIN.RELATIVE_RANGES( 'LRH', 'START' ) AND BUILTIN.RELATIVE_RANGES( 'LRH', 'END' ) )
		GROUP BY
			TO_CHAR( Transaction.CreatedDate, 'YYYY-MM' ),
			Transaction.Type
	)
ORDER BY
	TranMonth,
	TranType

Wrapping Up

The query for "Number of Transaction Lines Generated In Last 6 Months, Grouped By Transaction Type" is now available in the SuiteQL Query Tool's "Remote Library." To load it, search for "Transaction Line Usage."

As always, I hope you've found the queries that I've shared in this post to be helpful.