Looking at the standard financial reports in NetSuite, it seems that the Balance Sheet (and Trial Balance) show the values in the various accounts at a fixed point in time (e.g. to end of last month), whereas the Comparative Profit and Loss show the values for transactions on the accounts for a set period (e.g. last month).
I have assumed (from looking at the configuration of these reports) that this date / date range is part of the underlying 'type' of report - and I can't have a report that mixes both types of data?
From what I can find online, I can replicate the figures returned on both types of these reports via Saved Searches, but in order to get a value for the account at a fixed point in time (e.g. to end of last month) I have to sum all transactions on that account from the start of NetSuite to the date required.
Is there an alternative way to get this data? These searches are already very slow to run (and we only have 3 years data in NetSuite) - surely as our account grows these will become unusable?
Is there a way in SuiteScript to return the value of an account at a point in time without having to run a saved search? I have a complex Financial dashboard that my Head of Finance has asked me to build in NetSuite, and after generating 2 different searches of transactions, the SuiteScript is now timing out. Is my only option to turn this into a Scheduled Script that will send the results via email? My problem with this is that the report needs to be run on an 'ad-hoc' basis, not on a simple, repeating schedule.
I've had a quick look at Analytics to see if that could provide an alternative, but it would seem the Accounts Dataset only provides fields relating to the Account setup, so I assume I would still need to run a Transaction Dataset to sum all transactions to the date required (which I assume would be subject to the same timeout issues).
I need to be able to show the value of some accounts (or groups of accounts) as at the end of each month for the year, along with other values showing just the total of transactions on an account for each month.