Specifying Account Numbers in Formulas and in Rows
An account number reference specifies the accounts from which you want to take data.
You can specify:
-
A single account number
or
-
A range of account numbers.
You can choose to print a range of information in one of two ways:
-
consolidated (in a single row)
-
detailed (one row for each account in the range)
-
You can use these formulas for account groups:
\\acctgroup (sort by account group)
\\acctgroupsort (account-sorted group)
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:
-
A sample formula that places data from a range of accounts into a single spreadsheet cell:
=FRAMT("BALP","1200:1500")
-
A reference in column A of a specification row that indicates which account data to use for the row (a D or a T in column D will determine whether each account is listed separately or totaled on a single line):
|
A |
B |
C |
D |
|
%-1000~1100 |
|
|
|
In this example, the "%" sign is a wildcard character that takes the place of one or more characters. (See Wild-card Characters in Account number References.)
Use FR Paste
If you cannot remember how to specify accounts and account ranges, choose FR Paste from the FR menu of Financial Statement Designer. FR Paste lets you use the Finder and various range fields to specify the accounts you want. See FR Paste.
Account references affect performance
Remember that Financial Reporter can generate your reports faster when it doesn't have to perform a lot of extra account lookups. For this reason, check that the account processing order in the report matches the way you specify account references. (See Account Ordering, Processing Order, and Account Segment Ranges.)
Standard account reference notation
These are examples of acceptable account number references. Note, however, that all of these examples assume that segment 1 is the account segment and segment 2 is the department segment. See also: Specifying Ranges of Accounts when the Account Segment is not First in the Account Structure.
|
100-C |
Account 100, department C |
|
100:200 |
Accounts between 100 and 200, no matter which department they are in |
|
100-A:200-F|ACCT |
Accounts between 100 and 200 in departments A through F, processed by account segment. |
|
100~200-F |
Accounts in department F with an account segment value between 100 and 200 |
Account number references can contain the following symbols:
"-" Account segment separator defined in G/L Options. (The hyphen is the default separator.)
":" Separates account numbers in a range.
"~" Separates segment codes in a segment range.
You can enter account references in any of the following ways:
-
aaaaaaaa or
aaaa-aaaaA specific G/L account number composed of two segments with four characters each. Financial Reporter will get figures from general ledger account aaaa-aaaa.
Note in this example that account numbers can be entered with or without their segment separators "-". However, if you are specifying ranges of accounts, we suggest you include separators to remove ambiguity from your specifications.
-
Marks a range of general ledger account numbers. Financial Reporter fills out the first number in the range with blanks, and the last number with Zs.
In other words, it looks for account numbers between "aaaaA " and "bbbbBZZZZZZZZZZZZZZZ".
100:200 specifies all account numbers that start with the first three characters between 100 and 200. This range includes accounts with 1 or more segments.
100-A:200-F specifies all accounts with an Account segment value between 100 and 200, and with a Department segment value between A and F (for any order other than ACCTID order). If the order is ACCTID, the constraint is ACCTID => 100-A and ACCTID <= 200-F.
100:100 specifies all accounts that start with 100, no matter what the account structure. 100%% is another way of specifying this range.
Note in the second example (above) that if the order is ACCTID, "150-H" will be included because it falls between "100-A" and "200-F." However, for non-ACCTID order Financial Reporter takes only those accounts that fall within both ranges: the range of accounts and the range of departments, so that 100-A:200-F is the same as 100~200-A~F (shown below).
-
aaaa%% or aaaa:aaaa
A range of all account numbers that start with aaaa. In this case, aaaa%% is just an easier way to write aaaa:aaaa.
-
%%
References all accounts in the ledger. This is just a shorthand way of writing "blanks" to "ZZZZZZZZZZZZZZZZZZ". %% cannot be used for a particular segment.
-
-
Marks a range of general ledger account numbers by indicating the range of segment 1 and a fixed second segment. Financial Reporter will get the figures from each account within the range that have the segment 2 code.
100~200-F specifies all accounts with an account segment code between 100 and 200, and with a department segment code of F.
100~200-A~F specifies all accounts with an account segment code between 100 and 200, and with a department segment code from A to F.
-
A range of account segment codes. This special-case code will match the account segment, no matter what position it has within the account number. The code is useful if the account segment is not segment 1. (Note that this code matches the account segment only if you show a single segment.)
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.
The "~" separator allows you to specify a range for one segment, rather than a range of complete account numbers. This is particularly useful when the account segment is not the first segment in the account number. For example, if your account numbers have a department segment first (such as 10-4000 and 20-4000), the reference 4000~8000 will include 10-4000, 20-5000, and 10-6000.
-
A range of 3-segment account numbers. The middle segment is between "aa" and "ff," and order is by account segment (specified by "|ACCT"). For information on ordering an account reference, see Account Ordering.
-
An individual account number and a range of account numbers. The square brackets indicate the account structure codes used by the account numbers.
-
A set of ranges and individual accounts with each specification separated by a comma.
See also
Account Ordering, Processing Order, and Account Segment Ranges