This article is relevant if you are working with NetSuite commissions and you are seeking to perform analysis between your generated commissions and related invoices.
Background
NetSuite offers a commission module as an add-on to the overall fundamental base system. The commission module is pretty powerful and works for many customers. Yet there are times that you need to ask questions and you may find challenges. For some customers, they wish to have NetSuite commissions their way.
At the time of this article, the commissions system is not exposed via the SuiteScript and SuiteTalk (web services) API. I suspect in due course, this will change. Also, some of the data structures are not exposed via Saved Search. But most of the commission data structures are exposed via ODBC. That is good news for custom reporting and analysis.
For a recent client who is extensively using NetSuite commissions to run their business, they needed to match up the NetSuite Accounts Receivable world (Invoices) with Accounts Payable (Commissions) to explain business performance. NetSuite offers reports that show Accounts Receivable view points, and other reports that show Accounts Payable, but they don’t currently have a single report that pulls these two worlds together. And Saved Search won’t help because it is missing a critical element in that view: the link between the AR and the subsequent AP that is generated form sales performance.
Deeper Analysis into NetSuite Commissions with ODBC
NetSuite makes the offer to solve these concerns using ODBC which then allows you to use third party reporting tools, such as SQL Server Reporting Services, or Crystal Reports, to output information. That generally works well. I have written a number of articles on this technology.
I speculate that NetSuite commissions are calculated behind the scenes nightly. During this process, NetSuite analyzes the commissionable transactions according to the rules you have setup to indicate which invoices are candidates to create commission records. NetSuite uses an ODBC link table called “commission_authorization_link” to act as a bridge between invoices and commissions. This transaction table holds both the invoice header and lines and the commission header and lines. On first glance, you might say, “got it!”. Yet, it is important to note that there isn’t a one-to-one relationship between a NetSuite Invoice line item and the subsequent NetSuite Commission line item.
For many, this is not problem. But for others, here is the challenge. NetSuite will summarize all the invoice lines that are effectively meeting the same Commission Schedule to apply a specifically defined commission rate. To make this clearer, if you have an invoice with three line items totaling $10,000 USD all using the same commission schedule with a commission rate of 20%, you will end up with a single line item on the Commission record for $2,000 USD. The calculation will be correct. But you will lose the ability to directly relate invoice lines to commission lines. When trying to “explain” the AR world and the AP world in one place, the capacity to link these structures may be foundational.
Suggested Enhancement
While we don’t have a perfect solution because the API is not exposed, my hope is that the structure of these records will change to support a one-to-one relationship between the AR lines and the commission lines. NetSuite’s fundamental system will continue to work as-is. We can still use the commission_authorization_link table but we would have line level sequence/reference IDs and likely item IDs that definitively link. With that structure in hand, a report, data warehouse or OLAP cube can then be designed to help accountants and financial analysts quickly answer questions.
Taking Commission Matters into Your Own Hands
In practice, Commissions can be a complex subject because executives invent all kinds of programs to drive sales performance. NetSuite’s platform is very powerful because you can do it yourself and have complete control over the situation. Have a look at this article to learn how we solved other commissions challenges. If you are looking to get more out of NetSuite, contact us.
See Related Articles
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
