About Account Number References
Account Reference Syntax and Examples
About Wildcard Characters in Account Numbers
An account number reference specifies the accounts from which you want to retrieve General Ledger data.
Tip: In the Statement Designer, use FR Paste to specify accounts and account ranges without manually entering formulas. For more information, see About FR Paste.
Specifying Accounts and Ranges of Accounts
You can specify a single account number, or a range of account numbers in consolidated (single-row) or detailed (one row per account) format.
The formula =FRAMT("BALP","1200:1500") retrieves data from a range of accounts into a single spreadsheet cell.
When specifying account groups, you can use these formulas:
The rules for specifying an account number reference are the same, whether the reference is in a function or in column A of a specification row.
Column A of a specification row specifies account data to use for the row:
| Column | |||
|---|---|---|---|
| A | B | C | D |
| %-1000~1100 |
In this example, % is a wildcard character that takes the place of one or more characters. For more information, see About Wildcard Characters in Account Numbers.
In column D, you can enter D to specify that each account is listed separately, or T to specify that each account is totaled on a single line.
For more information, see Account Reference Syntax and Examples.
Account References and Performance
Financial Reporter can generate reports faster when it does not have to look up information for a substantial number of extra accounts. For this reason, check that the account processing order in the report matches the way you specify account references.
For more information, see About Account Order in Financial Statements.
Restricting Account Selection
Financial Reporter selects which account data is to appear in a financial report by evaluating three separate account number filters:
- The account number reference in column A of the report line.
- The account filtering expression in column B (if there is one).
- The print range options for the entire report.
Normally, an account has to satisfy the requirements of all three filters in order to be included in the report. However, Financial Reporter also provides a mechanism for specifying a report range, and for including accounts which lie outside that range.
The easiest way to add account numbers to a financial statement is using FR Paste. For more information, see About FR Paste.
Specifying Account Structures
To restrict account number selection to a particular account structure, use a formula such as aaaa[ACCT], bbbb~cccc-A[MANU], which specifies an individual account number and a range of account numbers. The square brackets indicate the account structure codes used by the account numbers.
Specifying a Segment to Override a Segment Range
You can override a segment range specified on the Print Financial Statements screen by including the segment values you want on the line in the column A account number reference.
Example: If you want to compare the performance of each department to the total sales for the organization, you can specify the total organization's sales on a line as follows: 4000~4999-A, 4000~4999-B, 4000~4999-C, 4000~4999-D. In this example, you would print reports specifying a segment range of A, of B, and so on.
Turning off Report-level Account Range Filtering
A $ prefix turns off report-level account range filtering for the particular account number reference. If you use a $ prefix, any account numbers that match the account reference you specify and match the selection criteria in column B will appear on the report, regardless of the restrictions imposed by Financial Reporter print settings.
You can also use the $ prefix with the %% wildcard characters to include any account in the ledger allowed by the criteria in column B for the row. (%% is an account number reference that includes all accounts, no matter what their account structure.)
Specifying Ranges of Accounts when the Account Segment is not First in the Account Structure
Account references work best when the account segment is the first segment of your account numbers. Not only is it easier to specify account ranges, but Financial Reporter processes them more quickly as well.
If you do not have the account segment first, you can still specify a range of account segment codes and print the accounts in account segment order.
For example:
- 4000~8000 specifies all accounts with an account segment code from 4000 to 8000, no matter how many segments are in the number, and no matter where the account segment is in the account structure.
- A~C-400~600|ACCT specifies all accounts with an account segment value between 400 and 600, and with a Department segment value from A to C. |ACCT indicates that the accounts are ordered by account segment.
- %-400~600-%|ACCT specifies account segment values from 400 to 600, no matter what the value of other segments. Again, the accounts are ordered by account segment.
- %%|ACCT specifies all account numbers ordered by the account segment. To specify a range or account segments, add a criterion in column B, such as ACCTSEGVAL >= "4000" AND ACCTSEGVAL >= "6000".
The last example is the most flexible, because it specifies a range of account segment values no matter where the account segment lies in the account structure. This is especially useful if you have more than one account structure.