Accurate Kit Costing

Learn how to use SuiteQL to achieve advanced reporting beyond the grasp of Saved Searches

Introduction

A recent client gave us a challenge: is it possible to create a report that shows what the average cost of Kit items were as of a given point in time? As will be explained below, this was not a simple request, and the data was not reachable using Saved Search functionality. Instead, we used a combination of the recently accessible “SuiteQL” SQL query ability in NetSuite and custom reporting to solve the request. If you would like to see how to expand your reporting toolkit using these tools, read on!

“Kit” Items

Kit items in NetSuite are essentially a group of other items that can easily be sold together on a Sales Order, without the need to formally “assemble” the components into an Assembly. Many clients that sell packages or bundles of different products prefer to use Kits rather than Assembly Items, since unlike Assembly Items, with Kits, there is no need to maintain work orders, bills of material, and the like. The component items remain as separate items in inventory and are available to be sold on their own. For this reason, a “Kit” does not really have a cost in NetSuite. It can almost be thought of as a bill of materials without any actual assembly taking place until the “Kit” is sold – at that point, NS consumes the component items based on the quantity of each component in the Kit and the quantity of Kits sold.

Given the above, when we talk the “cost” of a Kit, what we really are after is the cost of all the component parts multiplied by the number of each component within the Kit. We essentially want to artificially construct the Kit and project what the cost would be if it were sold at a given point in time.

Understanding the Table Structure

Before we figure out how to solve the problem, we need to map out where the data lives. I like to use Excel to create sample tables to visualize the work that NetSuite will need to do to present the data in the desired output. The sample tables that I create in Excel do not necessarily need to line up perfectly with NetSuite’s tables at this point; I actually find it more efficient to initially create tables based on my general assumptions. I then can think about my “attack plan” – where my main report data lives and what elements I can bring in using joins. Once I have a general idea of how to build a search or query, I then get the specific tables using NetSuite tools such as the Analytics Browser or the Record Catalogue, and then can think about whether a Saved Search can get to the data, or I need to use a Query Tool (more about the Query Tool below…).

Kit Avg Cost Tables

In our example, we need to bring together the following tables:

1. The “transaction” table
2. The “transactionLine” table
3. The “transactionAccountingLine” table
4. The “account” table
5. The “item” table
6. The “itemMember” table

Looking at the above, it quickly became clear that Saved Search would not be possible, since if I would start from a transaction search I would not be able to hop over to the item table and from there to the “item member” table to find the quantities and parent kits. Starting from an item would also not be possible, since it would require starting from the Kit items, joining to another table to get the components and their quantities within each kit, and then a third hop to the transactions of the component items.

Prolecto SuiteQL Query Tool

Thankfully, we have a couple of new tools at our disposal that are in many respects much more powerful than Saved Searches. These are the Prolecto SuiteQL Query Tool (free of charge to our clients as part of the Prolecto Utilities Bundle) and more recently, the Prolecto Query Renderer (see Marty Zigman’s article here). These tools build upon the “N/query module” that NetSuite has made available in 2019 to provide the ability to reach virtually any data in the database. In my opinion, these tools and others like them that are emerging in the marketplace may eventually replace Saved Searches completely.

After much trial and error, I was able to get accurate as-of date reporting, showing the Average Cost of Kit items. Here is the main Query used to create the report:

SELECT
type
,internal_id
,kit
, SUM(avg__kit_cost*comp_qty) as kit_avg_cost

FROM

(	SELECT
	BUILTIN.DF(im.parentitem) as kit
	, BUILTIN.DF(i.itemtype) as type
	,i.id as internal_id
	, BUILTIN.DF(im.item) as component
        , MAX(t.trandate)
	, AVG(im.quantity) as comp_qty
	, SUM(tl.quantity) as trx_qty
	, SUM(tal.amount) as trx_amt
	,(SUM(tal.amount)/NULLIF(SUM(tl.quantity),0)) as avg__kit_cost

	FROM
	itemMember im
	INNER JOIN transactionline tl ON tl.item = im.item
	INNER JOIN transactionaccountingline tal ON tl.id =    tal.transactionline AND tl.transaction = tal.transaction
	INNER JOIN transaction t ON t.id = tl.transaction AND t.id = tal.transaction
	INNER JOIN account a ON tal.account=a.id
	INNER JOIN item i on im.parentitem = i.id


	WHERE
	BUILTIN.DF(i.itemtype) = 'Kit/Package'
	AND
	a.sspecacct='InvtAsset'
	AND
	t.posting = 'T'
       
	GROUP BY
	BUILTIN.DF(im.parentitem),BUILTIN.DF(i.itemtype), i.id, BUILTIN.DF(im.item)

)

GROUP BY
type, internal_id, kit

Content Renderer

Here is a snippet of what the output looks like using the Prolecto Query Renderer:

Postscript

In the end, due to the data volume in this particular client’s account, the query could not run quickly enough and would frequently time out. I then worked with Boban D., my senior colleague at Prolecto who created a custom record structure to hold monthly results, allowing the query to run smaller chunks of data at a time. That solution deserves an article in its own right, though.

Conclusion

I hope this article helped demonstrate the power of using tools that are possible using the SuiteQL methods, and more importantly, the strength of being part of a very talented group of NetSuite consultants. If you would like to learn more about how our creative custom tools can help you break out of the limits of native reporting, we’d be happy to hear from you!