Account Exclusions with Sage Intelligence
How account exclusions work across Sage Intelligence Reporting software.
The exclusion functionality will allow you to perform a mathematical function that will exclude an account (or set of accounts) from a defined range. For example, if want to see a total for all accounts in the range 1000 to 1030 but exclude the account 1020 as you know it is a miscellaneous holding account, you can. Take note that just as an account can be excluded from a range you can also included accounts in a range. This article will only focus on exclusions though.
As Sage Intelligence software has evolved, the exclusion functionality has been upgraded. This article will outline the difference in functionality between the prior exclusion logic and the latest in exclusion logic. At the time of writing, the only integration that has been shipped with the latest exclusion logic is Sage 500 2016 Intelligence Reporting.
1.1 How to use an exclusion
Sage Intelligence software identifies an exclusion with the “-“ symbol. The key is to ensure there is a space on either side of the symbol, else the system may mistake it for an account delimiter. If you would like to exclude a whole range, then simply surround the range with brackets. Here is an example of excluding a single account: “1000 TO 1030 – 1020” This will return all accounts between and including 1000 and 1030 but EXCLUDE account 1020. “1000 TO 1030 – (1020 TO 1022)” This will return all the accounts between and including 1000 and 1030 but EXCLUDE accounts between and including 1020 to 1022.
1.2 Previewing a row set with an exclusion
Using the above example, when a row set is created a convenient preview is provided so you can see exactly which accounts are included in the set. Even though an account has been excluded it will still be in the preview. The reason for this is that we don't want to completely ignore the account but simply want to exclude its total from the resulting balance. To do so the account is included in the row set so that it can be subtracted from the total balance.
1.3 Drilling down on a row set with an exclusion
A fabulous feature that Sage Intelligence allows is to “drill down” on a balance, so you can see what is making up the total balance. If a drill down is performed on the above range (“1000 TO 1030 – 1020”), you will notice the following:
The account that has been excluded (1020) is still visible in the drill down, and it has been listed twice. This is because the account is required in the mathematical formula to calculate the correct total. If you are in any doubt the correct total has been carried through, highlight the total column cells (excluding the excluded account), and you will see the balance is the same as on the report.
1.4 Upgraded exclusion technology and drill downs
Sage Intelligence integrations which have the latest exclusion technology turned on (i.e Sage 500 2016 Intelligence Reporting) will notice a key difference in the drill down on a range with an exclusion. Here is an example of excluding a single account: “1000 TO 1030 – 1020” When a drill down is performed, you will notice the 1020 account is still listed twice, however the symbol preceding the amount will be opposite – this will negate the amount in the final total.
A total row has been included for quick an easy reference that the drill down total is the same as the balance total in the report.