Generate (Periodic) Trial Balance Report Data With Saved Search
As a part of company structure, we needed to generate Trial Balance report for our parent company and send periodically. However, NetSuite does not allow reports to be generated via SuiteScript. That pushed us to find a different approach to capture the content of a Trial Balance report.
We wanted to try our chances with the help of the formula fields in a Saved Transaction Search. At the beginning, the results were inconsistent and it was a challenge to fine tune the formulas. After couple of trials, it was possible to achieve the necessary data.
Going into detail, let’s review the search criteria and columns. Below table is for the columns which is same for both Periodic Trial Balance and Trial Balance Reports. Basically it calculates the difference for each GL Account.
| FIELD | SUMMARY TYPE | FUNCTION | FORMULA | SUMMARY LABEL |
| Account | Group | |||
| Formula (Numeric) | Sum | Round To Hunderts | CASE WHEN SUM({creditamount}) – SUM({debitamount}) < 0 THEN SUM({debitamount}) – SUM({creditamount}) WHEN SUM({creditamount}) IS null THEN SUM({debitamount}) ELSE 0 END |
Debit |
| Formula (Numeric) | Sum | Round To Hunderts | CASE WHEN SUM({creditamount}) – SUM({debitamount}) > 0 THEN SUM({creditamount}) – SUM({debitamount}) WHEN SUM({debitamount}) IS null THEN SUM({creditamount}) ELSE 0 END |
Credit |
Continuing with the criteria, we needed to add a summary line which again is shared for both reports. This ensures that irrelevant data to be excluded and all we have is numeric data for the calculation.
| SUMMARY TYPE | FIELD | OPERATOR | VALUE | FORMULA |
| Sum | Formula (Numeric) | Not Equal To | 0 | CASE WHEN SUM({creditamount}) – SUM({debitamount}) = 0 THEN 0 WHEN SUM({debitamount}) is null AND SUM({creditamount}) is null THEN 0 ELSE 1 END |
The difference between the two reports is visible when the standard criteria is set. For the Periodic Trial Balance report, below criteria is enough to retrieve the required results.
| FILTER | OPERATOR | VALUE | AND/OR |
| Subsidiary | Any Of | Subsidiary Name | And |
| Posting | is | true | And |
| Posting Period | Any Of | Accounting Period |
For the Trial Balance report, more complex criteria is required. This is to ensure that correct set of the values are captured for the reporting period and the complete data is captured for the whole period.
| PARENS | FILTER | OPERATOR | VALUE | PARENS | AND/OR |
| Subsidiary | Any Of | Subsidiary Name | And | ||
| Posting | is | true | And | ||
| (( | Account Type | Any Of | Income Expense Other Income Other Expense COGS |
And | |
| Accounting Period : Start Date | On Or Before | Report Month End Date | And | ||
| Accounting Period : Start Date | On Or After | Report Fiscal Year Start Date | ) | Or | |
| ( | Account Type | None Of | Income Expense Other Income Other Expense COGS |
And | |
| Accounting Period : Start Date | On Or Before | Report Month End Date | )) |
One thing to consider is the Consolidated Exchange Rate setting under the Results subtab. If None is selected, report will show data for the related subsidiaries currency. When Per-Account is selected, data will be for the currency of the parent subsidiary.
That’s it. You’ve the data for your report. One last calculation is required for the Retained Earnings account which applies to both reports. Total debit/credit value of the all accounts must be subtracted from the Retained Earnings account to find the correct value for the report. This can be done after extracting the data outside of NetSuite or via SuiteScript if you are planning to develop your solution.
We would like to hear your opinion if you liked this solution or if you think there is a place for improvement. If you have any comments on this solution, please use the section below. For any other communication, please use the form on the Contact Us page.