This article is relevant if you are seeking to produce a Trial Balance with NetSuite Saved Searches.
Background
Many times during our client engagements, we need to work with general ledger account balance data for various analysis or systems integration work. Often times, NetSuite reports are not exactly what we need. We may need to display a set of balances on a form. Many times, a common request I hear is to produce a comparative balance sheet. The current NetSuite report tools can produce a comparative balance sheet but the column definitions need to be hard coded making them harder to use. Ideally, columns would be dynamic based on today’s date.
In these cases where you need more dynamic control, going to Saved Search can be very helpful. However, advanced saved searches using NetSuite’s underlying Oracle functions can take time to learn,
Trial Balance by Saved Search Illustration
To help the accounting community learn how NetSuite can produce financial numbers, it is important to remember that there is no general ledger as one might think in a traditional sense. Instead, all balances are generated through the use of transaction details summarized. This is why NetSuite is a real time system with no need to update the general ledger — it’s always current! The key to producing financial numbers is to remember these rules:
- Balance Sheet: Sum all transaction amounts since inception to the date you want your balance.
- Income Statement: Sum all transaction amounts from the beginning of the fiscal period to the date you want your balance.
- Retained Earnings: Sum all income statement transaction amounts since inception to the last day of the previous fiscal period.
To help NetSuite accounting minding individuals understand how to produce these balances, the Trial Balance is a relatively simple financial report; yet it is somewhat challenging to produce with a Saved Search. Hence, it is a good opportunity to see how to build one here. We get started by defining a Saved Search of type Transaction.
Key Trial Balance Saved Search Criteria and Result Definition
I assume you have basic saved search skills. So let’s go.
Set the criteria on a Transaction type Saved Search:
- Posting = True
- Account.InternalID Not Null
Next, define the result sets. For the most part, I use summary based formula fields. See screen shot for the definition.
The key value to produce is the balance which is defined as “NVL({debitamount},0)-NVL({creditamount},0)”. Although not necessary to know here, you may be interested in this article about the polarity of the amount field if you do a lot of NetSuite financial work. See The Pluses and Minuses of NetSuite Financial Statement Polarity. This NetSuite Saved Search makes good examples for some advanced searching concepts:
- Classifying Accounts as “Balance Sheet” or “Income Statement” using conditional logic
- Two techniques to Extract out the Year based on the Accounting Period End Date and using it to qualify amounts
Get the Saved Search Bundle
If you are a NetSuite end customer, you are welcome to the bundle. My intention is to build a library of financial based NetSuite saved searches as we solve client concerns. With no obligation, send me a request with your account ID and I will make it available to download. If you are in a situation and need a professional that understands accounting and NetSuite advanced Saved Searches, let’s have a conversation.
I’m trying to build a report (whether it’s a report builder or Saved Search) to show Beginning Balance, Debits, Credits, and Ending Balance for a given year. Your saved search is close but not exactly what I’m looking for. I need to be able to select the date ranges rather than just using the system date.
Please help!
Hi Heidi,
The trick to this is to use case statement formulas. You must start from inception and then aggregate all the way to the beginning balance. Did you download our bundle so that you can study how I wrote it? Send me a note here to make a request for it.
Marty
I have an auditor’s request to produce an export file of the year’s transactions from 1/1 to 12/31. Have you used the SuiteAnalytics Connect (ODBC) tool, and would this be the best route versus running the saved search multiple times in small increments? Your recommended approach is appreciated, as the searches will time-out. Thanks.
Hi Dante,
The solution I offered here uses Saved Searches. Thus, to get it with ODBC is a different approach (should be possible on Posting Transactions table). However, you could put this search into a Persistent mode so that it runs in the background to help avoid timeouts.
I’m trying to produce a GL transaction list in Document, Local & Group currency. Standard reporting seems to only show document & group?
Hello John,
This question appears to be off topic. Feel free to contact me directly with a screen shot here: https://www.prolecto.com/services/netsuite-care/
Marty
hi Marty,
Do you know of a way to setup a saved search to pull trial balance data dynamically where we want NetSuite to ask the user for the accounting period to pull the trial balance vs. using sysdate function? Thank you
Hi Al,
Interesting question. You might be able to get the Accounting Period to become a drop down in the filter criteria. Have you tried?
Marty
I am trying to created a trial balance search and came across your blog. In order to get the balance sheet balances on the search, it looks like you have a formula for each previous year. What if you have been using NS for the past 10 years?
The general pattern using a saved search requires you set up each column with relative date functions. Once set up, it will move according to time.
So I copied your search above, All but the Account.Internal ID null I had to set it as not empty.
I added a few other columns we needed, such as the name of our entities, and my income section [have not tested the balance sheet section yet] is pulling in the ending balance as if i was looking at the register. I only want the current YTD.
Can you think of any reason why this would not work?
Thanks for the help.
Hi Elizabeth,
Did you download our bundle or did you craft it on your own? I recommend starting with our bundle and then using it as a template so that you can build off it. Send me a request here:
https://www.prolecto.com/services/innovations/
Marty
I tried to input some of your Formula (Text) formulas and noticed that there isn’t an “IN’ command available to me. Is there something that I am missing?
Hi Michael,
This “IN” clause should work. See this reference: https://www.techonthenet.com/oracle/in.php
You can also reach me to get the bundle and then you will have the code. Send me a request here:
https://www.prolecto.com/services/innovations/
Hello Marty,
We have downloaded your bundle into our SB environment but unfortunately it is not working for us. I think it has to do with our multiple entities and currencies.
I wanted to thank you for your time and help
Elizabeth
Elizabeth,
You may need to refine the saved search criteria just a bit to get it to tie out. We use the Trial Balance searches all the time for the starting point for reproducing NetSuite financial statement elements using saved search.
Marty
Hi Marty,
Great site. I’ve been coming here to help guide me in building Saved Searches to export data from NetSuite. How would you go about creating a monthly TB instead of the annual version shown here? I tried replacing YEAR with MONTH, but that yielded no data. I like that if I can extract this data correctly, then I should be able to post all historical values to a Prior Year’s Retained Earnings account in my target system instead of calculating it from Net Income.
Rafael
Hello Rafael. You should be able to get there. The key is to understand the criteria well. As I suggested in the article, if you are a NetSuite customer, I can send you our bundled saved search and you take it apart to build on it. Contact me, supply your Account ID, and ask for the Financial Search bundle.
Marty
Hi Marty,
Do you think is possible to achieve with Multi-Book Accounting?
Alex
I am not sure I understand your question. Do you have multi-book and you are trying to produce a trial balance via saved search?
Marty
How would you adjust the formula so that you get the balance as of the day in the “TO” field in a filter?
So instead of looking at the SYSDATE it is looking at the filter.
I am trying to create a saved search where the user can select the end date, and be able to know what the account balance was as of that date.
Hi Robert,
Out of the box, the tools are too limited. But we can create a SuiteLet that would produce this experience.
Marty
Hi Marty
Our Fiscal Year is April to March, therefore the Formula does not work. Any hint on how I can change it to a Period reference instead of year from period end and sysdate?
Thanks
Stefan,
You are on the right track. We basically need something else to understand if the transaction falls in your fiscal period. My example is leveraging Oracle’s TRUNC pattern to extract out the year. In your case, given the way the system will link to the accounting period end date, I probably would hard code your date boundaries and then perform some date math to determine if I am in the current year versus the previous year.
Reach out to me here to ask for the Financial Saved Search bundle.
Marty
Note, some readers have asked how you can get the previous NetSuite year income statement balances to show. The issue with the way I supply the search is that it contains no accounting period criteria. Thus, NetSuite is intelligent enough to roll up the previous year’s income statement elements to retained earnings. To change this, modify the criteria to look like the following:
Marty
Hi Marty,
Is it possible to generate Multi-Book Trial Balance via saved searches? Thanks
Hello Bryan,
Yes, we should be able to get to it. If you take the pattern I have produced, you can access the “Multi-Book Accounting Transaction Fields…” link table. Finally, we will produce an article on how to generate Trial Balances with SQL in due course.
Marty
Hi, I replicated this saved search. However, balance sheet accounts balances are also coming for just the filtered date range. Balance sheet accounts should come with balance since inception.
Hello Sanchi,
One must be careful with date filters in these reports. All balance sheet accounts must come from inception. That is why we do column date math in the income statement section.
Marty
Hey Marty,
I have added date filter in saved search.
While selecting a particular date range, balances of all the accounts are appearing only for that range. However, in case of balance sheet accounts, balance should always appear since inception. Please help if I going wrong somewhere
As mentioned, you can’t add date filters to the saved search. You need to use formula math in your columns.
Marty
Can you guide me what formula should I Use to have Balance sheet account values since inception and income account balance for a particular period
Hello Sanchi,
The formulas are illustrated in this image: https://blog.prolecto.com/wp-content/uploads/2016/09/prolecto-trial-balance-netsuite-saved-search-201609101.png
Next, you can make a request, as per this article, to download these searches into the account. Next, the key to getting periods you want is to use Oracle Date based Formulas that provide you with the month (period) you are seeking to isolate and report on. These formulas are effectively advanced Saved Search techinques. If you want to engage in assistance, My team can help you directly. Contact us at https://www.prolecto.com/contact-us/
Marty
Please give a formula for Period Apr to Mar
Hi Marty,
The formulas that are illustrated in the image are for the period Jan to Dec. Please help with the formula for the period Apr to Mar
Hi Marty, what can be done to show the account balances in both Consolidated (USD) and Local/Subsidiary Currencies?
Hello Ariana,
You can only drive a trial balance based on the subsidiary currency as this is fundamentally being calculated on every transaction via currency price. I speak about foreign currency in this article which references many related articles:
https://blog.prolecto.com/2022/11/19/considerations-for-activating-netsuite-foreign-currency/
The consolidated reporting currency (that subsidiary’s currency setting) will need to take into account CTA which too, can be done, via Saved Search. Feel free to reach out to us: https://www.prolecto.com/contact-us/
Inventory Worksheets are not returning correct amounts. How can I get all transaction accounting lines for these?
Hello Nathan,
This question appears out of context with the article. Feel free to contact us directly at https://www.prolecto.com/contact-us
Marty