Generate (Periodic) Trial Balance Report Data With Saved Search

February 9, 2021September 26, 2024SelcukSaved 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. You can reach through the Contact Us page for any communication.