## 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.

This site uses Akismet to reduce spam. Learn how your comment data is processed.