This article explains how to create rows in a financial statement that combine the amounts of several base accounts.
Strategies
- Option 1 – Include Individual Base Account Lines with Hidden Rows and Total
- Option 2 - Use BaseAccount Argument of TSGLAmount Function
Option 1 – Include Individual Base Account Lines with Hidden Rows and Total
In this case, one would design the report to include one row per individual base account that is to be combined. This section of the report would typically have static account numbers (i.e., the account list is not driven by a query). Followed by this group of accounts would be a total line (using the SUBTOTAL function). Finally, each of the rows showing the individual accounts of the group would be hidden.
Option 2 - Use BaseAccount Argument of TSGLAmount Function
The BaseAccount argument can be used to identify a single account but can also be used to identify a set of accounts. When a set of accounts is identified, the amounts from each account in the set will be combined in the result of the TSGLAmount function.
BaseAccount Argument is Single Account
When a text value is used for the BaseAccount argument and the value consists only of numeric digits (0 to 9 and .), the value is assumed to be a single account number.
BaseAccount Argument is a Filter
When the BaseAccount argument includes non-numeric digits, the value is assumed to be a filter. All accounts that match the criteria defined by the filter will be included and combined in the result. A filter can consist of just one expression or a group of expressions.
When a filter consists of a group of expressions, each expression must be separated by the “OR” keyword – <expression> OR <expression> OR (<complex-expression>)
When a given expression is more complex, meaning that it involves more than one operator (e.g., AND operators), the filter expression should be enclosed in parenthesis if there is more than one filter expression in the filter.
- Types of Filter Expressions
- Account Range Expression
An account range expression consists of a starting account number and an ending account number. All accounts contained within the range will be included in the result. Example – "[BaseAccount]>='1001' AND [BaseAccount]<='1080'" The TSGLBaseRange function provides a simple way to produce this type of expression. - Account Ranges Expression With Exclusion
- Sometimes it is necessary to exclude specific accounts from a range. This can be done by using the not-equal operator (“<>”). Example – "[BaseAccount]>='1001' AND [BaseAccount]<='1080' AND [BaseAccount]<>'1050'"
- Specific Account Expression
- An individual expression can identify a specific account by using the equal operator. Example – [BaseAccount]='1001'"
- Account Type Expression
- All accounts of a given type can be included by using the Account Type (ATYPE) column. Example – "[ATYPE]='Cost'"
- Examples of Filters with Multiple Expressions –
- Note that individual filter expressions are highlighted and each expression is separated by the “ OR “ operator…
- "[BaseAccount]='1001' OR [BaseAccount]='1003' OR [BaseAccount]='1009'"
- "([BaseAccount]>='1001' AND [BaseAccount]<='1003') OR [BaseAccount]='1009'"
- "([BaseAccount]>='1001' AND [BaseAccount]<='1003') OR [BaseAccount]='1009' OR [ATYPE]='Noncurrent asset'"
- "([BaseAccount]>='1001' AND [BaseAccount]<='1009') OR [BaseAccount]<>'1005'"