This viewer is used to create and maintain GL Report Column Layouts.
Required for the newer Custom GL Reporting.
The production of Cash Flow reports requires that certain report rows have column formulas that are different from the column definitions specified in the GL Column Layout. It turns out that this can be accomplished by changing the meaning of certain operand tokens used in the column formulas. See the discussion of the Misc. field in GL Report Row Layout for details.
Report Col Id:
Uniquely defines this column. Required.
GL Report Col Name:
Describes this column. This field is optional but highly recommended.
This section is used to enter the column definitions needed for the report. For each column you can define the column’s width, a heading for the column, and what information is to print in the column.
Each line defines one column. The first line defines the leftmost column, the second line defines the second column from the left, etc.
Wdth:
The Wdth field sets the width (in characters) of the column.
Column Heading:
The Column Heading field sets the text to appear at the top of the column. The number of characters should not exceed the width.
HJ
The HJ field sets the Heading Justification (L=Left, C=Centered, R=Right). If this is left blank, the Report Writer will make a pretty good guess at the appropriate value.
Formula:
The Formula field contains the formula that defines the actual contents of the field. A formula is a set of ‘operators’ and ‘operands’ which are strung together to form an ‘algebraic expression’ which is used to compute the value that is to be printed in the column.
There are two types of operands: alphabetic and numeric
Alphabetic operands include:
ID that evaluates to a GL Account’s Id field.
NUMBER that is the same as ID.
NAME that evaluates to a GL Account’s Name.
ENTITYID which evaluates to a GL Entity’s ID.
ENTITYNAME that evaluates to a GL Entity’s Name
Numeric operands consist of a ‘Base Operand’ that may optionally be followed by one or more ‘Operand Modifiers’. Base Operands include:
NET (net postings for a specified GL Reporting Period)
BEG (balance at the beginning of a specified GL Reporting Period)
END (balance at the end of a specified GL Reporting Period)
BEGY (opening balance at the beginning of the year)
BEGQ (opening balance at the beginning of the report period's quarter)
ENDY (synonymous with 'END', added for symmetry)
ENDQ (synonymous with 'END', added for symmetry)
NETY (the year to date net at the ending of the report period)
NETQ (the quarter to date net at the ending of the report period)
Note that NETQ is equivalent to Quarter To Date and NETY is equivalent to Year To Date.
Operand Modifiers for numeric operands include:
.<number> will set the Reporting Period for the token to the indicated period.
Example:
The token ‘BEG.1’ will evaluate to the beginning balance for the first period of the current fiscal year. So the function ‘END - BEG.1’ will evaluate to the year-to-date net for the account.
.P,.Pn, ,Pnn where ‘n’ represent a digit. Without a ‘.P’ modifier, the operand refers to the reporting account period (i.e. the reporting period specified when the report is run). Adding a .P. modifier causes a shift to the prior accounting period. The ‘.Pn’ and ‘.Pnn’ modifiers cause a shift to the nth (or nnth) prior period.
Examples:
END gets the balance at the end of the GL Reporting Period.
END.P or END.P1 gets the balance at the end of the prior GL Reporting Period.
END.P12 gets the balance at the end of the 12th prior GL Reporting period.
.B tells the report writer to get the budgeted amount instead of the actual amount.
.Axxxxx forces the report writer to get the amount for the GL Account with the Id of ‘xxxxxx’ instead of the account identified for the row that is currently being printed. For example. NET.A1001 will get the actual balance for the current reporting period for account 1001.
(This feature is especially useful when it is used to refer to total accounts defined via the DefineTot RowType.)
.Exxxxx is similar to Axxxxx but forces a particular GL Entity. A change has been made with regards to the ‘.Exxxxx’ account modifier. Previously, if ‘.E’ is used alone (i.e. without the xxxxx) the balance was computed across all entities within the General Ledger. This has been changed so that ‘.E’ will request that the balance be computed across all entities requested when the report is run. To have the balance computed across all entities, you should use the modified ‘.EALL’ where ‘ALL’ must be all in uppercase.
The .Gxxxxx account modifier allows the retrieval of a single account balance computed as the sum of the balances of the account across all entities within a specified GL Entity Group. If xxxxx is blank (e.g. .G), the balance will be computed across all entities requested when the report is run.
Combining Modifiers
Operand Modifiers can be combined. For example END.P.B will retrieve the budgeted ending balance for the prior period.
What kind of ‘operators’ can I have in a formula?
The normal arithmetic operators of ‘+’ ‘-‘ ‘*’ and ‘/’ are available. In addition, the percentage operator % can be used to compute the value of one operator as a percentage of another. These operators work only in conjunction with numeric operands.
A blank formula will generate a blank column.
A formula containing a single operand will print the value of that operand.
A formula containing an ‘algebraic expression’ will be computed using normal arithmetic methods. Note that parentheses may be used to group ‘sub expressions’ within a formula.
Example: the formula ‘NET – NET.B’ will print the variance of the net posting (for the current account, for the reporting period) from the budgeted amount.
Example: the formula ‘END % (END.Acash1 + END.Acash2)’
will print the ending balance (for the current account, for the specified reporting period) as a percentage of the sum of the ending balances of the two accounts ‘cash1’ and ‘cash2’.
Note that you can easily request nonsensical (but none the less valid) formulas.