Trial Balance by Subsidiary in Reporting Currency (USD)
Our auditors are asking for a report for a consolidated trial balance by subsidiary in USD, where each column is a subsidiary.
We were able to do this using the SuiteAnalytics Workbook pivot feature, but we can only do this for local currency. The CTA calculation only applies to reports.
Has anyone else run into this problem before? Right now, I’m thinking about putting together a suitelet with the posting period as a select field. In it’s post method I’d envoke a task that would trigger a map reduce script to generate the desired CSV file. But getting the right data and pivoting the subsidiaries to columns seems like a somewhat heavy lift.
Any advice would be appreciated.
You’re right that getting the numbers exactly right would be tricky.
I believe your starting point might be something like this if you go the Saved Search + Script route. Loosely taken from Marty: How to Produce a NetSuite Trial Balance with Saved Searches
var transactionSearchObj = search.create({
type: "transaction",
settings:[{"name":"consolidationtype","value":"ACCTTYPE"}],
filters:
[
["posting","is","T"],
"AND",
["formulatext: NVL(TO_CHAR({account.internalid}),'o')","isnot","o"]
],
columns:
[
search.createColumn({
name: "formulatext",
summary: "GROUP",
formula: "CASE WHEN {accounttype} IN ('Bank', 'Accounts Receivable', 'Other Asset', 'Fixed Asset', 'Deferred Expense', 'Accounts Payable', 'Other Current Liability', 'Deferred Revenue', 'Equity', 'Non Posting') THEN 'Balance Sheet' WHEN {accounttype} IN ('Income', 'Cost of Goods Sold', 'Expense', 'Other Expense', 'Other Income') THEN 'Income Statement' ELSE 'Balance Sheet' END",
label: "Classification"
}),
search.createColumn({
name: "formulatext",
summary: "GROUP",
formula: "CASE WHEN TRUNC({accountingperiod.enddate},'Y') < TRUNC(SYSDATE, 'Y') THEN (CASE WHEN {accounttype} IN ('Income', 'Cost of Goods Sold', 'Expense', 'Other Expense', 'Other Income') THEN 'None' ELSE {account.number} END) ELSE {account.number} END",
label: "Number"
}),
search.createColumn({
name: "formulatext",
summary: "GROUP",
formula: "CASE WHEN TRUNC({accountingperiod.enddate},'Y') < TRUNC(SYSDATE, 'Y') THEN (CASE WHEN {accounttype} IN ('Income', 'Cost of Goods Sold', 'Expense', 'Other Expense', 'Other Income') THEN 'Retained Earnings' ELSE {account.name} END) ELSE {account.name} END",
label: "Account"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "NVL({debitamount},0)-NVL({creditamount},0)",
label: "Total"
})
]
});
var searchResultCount = transactionSearchObj.runPaged().count;
log.debug("transactionSearchObj result count",searchResultCount);
transactionSearchObj.run().each(function(result){
// .run().each has a limit of 4,000 results
return true;
});
/*
transactionSearchObj.id="customsearch1742333655280";
transactionSearchObj.title="Test finance pl (copy)";
var newSearchId = transactionSearchObj.save();
*/
