Forget About Zero Rows In Your Balance Reports With ACCOUNTS…WITHHISTORY
One of the major goals of Velixo 6, which included a major update to the user-acclaimed Acumatica ERP reporting add-in, was to improve the experience around redundant zero balance rows in our reports.
It’s easy to see how all zero rows (especially when one has multiple accounts to keep track of!) produce a fair bit of visual clutter and may detract the reader’s attention from the other, more meaningful report lines.
To help us avoid the issue of such zero rows in our account balance reports, Velixo 6 brings on board two brand-new list functions: ACCOUNTSWITHHISTORY and ACCOUNTSANDSUBACCOUNTSWITHHISTORY.
To start slow, let’s look at an example of the first function.
There might be a few of us who are already familiar with Velixo 5’s EXPANDACCOUNTRANGE. At first glance, ACCOUNTSWITHHISTORY does exactly the same: it simply returns a list of accounts in the specified range. It turns out, however, that there is a very tangible difference in the functions’ output.
Let’s see if you can spot it!
Here is a sample trial balance using Velixo 5’s EXPANDACCOUNTRANGE function:
And here is the same trial balance, built using Velixo 6’s ACCOUNTSWITHHISTORY:
You have three seconds!
One…
Two…
Correct! With the new ACCOUNTSWITHHISTORY function, there are no zero rows in our trial balance!
You can think of it as EXPANDACCOUNTRANGE on steroids: in addition to outputting a dynamic account code array from the range expression (such as 10000:39999), it will also filter out those accounts in the range that have neither balance nor activity in the specified period interval.
If you’re interested in how the function works internally, it’s not rocket science: ACCOUNTSWITHHISTORY analyzes each account satisfying the range expression – one by one – and then decides whether to filter it out. If you omit any optional arguments, by default, the function checks for any non-zero balances or turnovers across all ledgers, branches and sub-accounts of the Acumatica connection.
You can further restrict the search, however, by ordering the function to only check for balances and activity within the specified ledger, subaccount, and/or branch ranges:
Note, though, that it’s very important to match the parameters of ACCOUNTSWITHHISTORY (including the optional ones!) with the parameters of your balance calculation functions:
In the example above, all balance and turnover Velixo functions are restricted by the ACTUAL ledger, PRODWHOLE branch, and the financial period of 01-2018.
According to the rule above, we need to specify exactly the same parameters to the ACCOUNTSWITHHISTORY function, and do not impose any additional filtering, such as by subaccount. This will ensure:
- On the one hand, that no zero rows are present in the trial balance;
- On the other hand, that we don’t accidentally omit any legitimate accounts with balance or activity.
Does your company produce detailed balance reports by sub-account?
Still not a problem.
For that purpose, we can use the second Velixo 6 function, called ACCOUNTSANDSUBACCOUNTSWITHHISTORY. It has all the same parameters as ACCOUNTSWITHHISTORY, but instead of outputting just a list of accounts, it produces a table of account-and-sub-account combinations.
Note the functions’ two-dimensional spill range:
Using a similar mechanism to the one described earlier, ACCOUNTSANDSUBACCOUNTSWITHHISTORY filters out all such account-and-sub-account combinations that have no balance or activity in the specified financial period range.
Are you ready for the master level yet?
Let’s make matters even more complex by asserting that we’re actually only interested in the first segment of the sub-account – which, in the example above, is just the first three symbols of the sub-account code.
Can we somehow make ACCOUNTSANDSUBACCOUNTSWITHHISTORY respect that and only show us unique combinations like “10100 – 000”, “10100 – CON”, “10100 – ELE”, etc.? And if so, can we make sure that the corresponding balance functions also aggregate the second sub-account segment away?
The answer is “Yes” – to both questions.
Let me introduce yet another Velixo 6 function, named UNIQUEBYPATTERN. It has three simple inputs and one array-valued output.
As an input, it takes:
- A source range – can also be a table or a dynamic array.
- A numeric index of a column in the source range.
- A pattern by which to aggregate the values in that column. The pattern takes the form of a string like ###???, where the “#” symbol means “keep this symbol in the output”, and “?” means “omit this symbol from the output”.
As a result, the function “reduces” the source table by collapsing the values in the specified column by the pattern. Then, it displays the unique rows of the resulting table.
It may sound complex – but bear with me, it’ll make total sense when we see an example.
As usual with Excel, the “source table” argument can conveniently be a result of another function. Let us look at what happens if we pass the result of our ACCOUNTSANDSUBACCOUNTSWITHHISTORY call through UNIQUEBYPATTERN.
We’ll specify 2 as the column index (exactly where the sub-accounts reside in ACCOUNTSANDSUBACCOUNTSWITHHISTORY’s output), and design the pattern string to “keep” the first three symbols of the sub-account while “omitting” the second segment.
The result?
Just what we wanted – a table of account codes and the first segments of sub-account codes – and recall that we’re still ensuring that all those combinations have non-zero balance or activity in the period range!
After solving this riddle, the only remaining task is to specify a proper range expression for the sub-account argument in all our balance and turnover functions. We need to do this in order to sum up the balances across the second sub-account segment, which we’ve omitted from the report. The end result looks like this:
Problem solved.
To sum up, we’ve just seen Velixo 6’s two new powerful functions designed to help us avoid zero rows in our account balance reports. The rule of thumb is as follows:
- If we need balances at the account level, we need to use ACCOUNTSWITHHISTORY.
- If details per sub-account are required, make use of ACCOUNTSANDSUBACCOUNTSWITHHISTORY.
- Finally, in scenarios where we’re only interested in details only for some sub-account segments, the result ACCOUNTSANDSUBACCOUNTSWITHHISTORY’s result can further be processed using the powerful UNIQUEBYPATTERN function.
Now, having thrown the zero rows aboard – it is happy sailing with Velixo 6!