One of the weak points in NetSuite was reporting. The options available were not flexible and integration possibilities were limited. During recent releases, NetSuite added new capabilities like Workbooks, SuiteQL and introduced new datasource for SuiteAnalytics Connect (ODBC/JDBC). We will explain how to generate Trial Balance Report using SQL query in our article. Same query can be used both in SuiteQL as part of SuiteScript and ODBC via NetSuite2.com datasource.

Trial Balance report can be easily generated using legacy reporting solution but it neither be reused in SuiteScript nor accessed through ODBC. There is possibility to capture the data through Saved Search but it requires additional calculation and processing to have the final data ready. We will explain this approach on another article.

There will be some parameters required for the query to be processed:

1. Internal ID of the Subsidiary the report should be generated for
2. Fiscal Calendar of the Subsidiary
3. Number of the Retained Earning Account
4. Accounting Period details for the reporting months

We will be looking into 2 separate queries. Year to Date and Periodic Trial Balance reports. Only difference between those queries is the filter details in the WHERE clause. Starting point of the SQL query will be TransactionAccountingLine table where the GL Impact of the transactions are stored. Other required tables are added to the query as well.

Filtering for the periodic report is not complex. All the posting transactions will be included in the results for a specific accounting period (month). Retained Earnings balance is calculated separately based on balances of all other accounts.

Filtering for the Year to Date report requires additional logic based on the account types. Amounts for Income, Expense, OthIncome, OthExpense, COGS accounts should be included for the current fiscal year of the subsidiary. Therefore, Start Date of the Subsidiary’s Fiscal Calendar needs to be added to the query. Amounts from the rest of the accounts should be included from the beginning. 

Results will include the sum of the amounts for each account. Instead, Debit and Credit columns can also be shown in the results from the TransactionAccountingLine table. We also added the Department, Class and Location dimensions but those can be removed in case not necessary.

Both queries are available on our GitHub page. The query for the Year to Date Trial Balance can be accessed here and the query for the Periodic Trial Balance can be accessed here.