The EXTERNAL FILE ADDITION facility is available if the content of the report and the organization of it cannot be reproduced by means of standard report writer selection criteria, the use of an external file may address the requirements.
If the intent is to produce a user-defined report based upon the content of a sub-ledger, the external file is the only method which can be used for extracting information from those records. The external file becomes the vehicle through which GL accounts are accumulated and reported on as defined in the record field parameters and the total parameters.
When defining a user-defined report not using external files, it is normally sufficient for the user to draft out the general layout of the report indicating report titles, column headings and the column contents and allow the system to generate the associated report based on control breaks. This level of detail preparation is insufficient when working with external files. When defining a report for an external file, it is necessary for the user to have the complete report defined beforehand, including totals and subtotals that are required on the report. The reason for this is that the user is going to define not only the contents of each line on the report, whether that be headings, detail or total, but will also be responsible for determining when the totals and subtotals are to take place in the report.
NOTE: Due to the amount of time involved in developing a user-defined reporting external file, it is important that the account content of the report remain relatively constant. If not, then additional work will be required to maintain the report whenever new accounts are added or deleted.
External files require that you build a map which describes the selection and aggregation of accounts in the body of the report. These maps (external files) give you greater control over the content and formatting of the report. The GL selection record allows the specification of ranges of account numbers. This means that you can calculate the surplus to date for balance sheet purposes by specifying all revenue and all expense accounts summed to one line on the report. The net balance will yield the surplus.
There are two types of records in the external file:
-
The report code record specifies a sequence for a line within the report and also the description that is to be printed. Other elements of this record provide format
-
The selection record specifies the range of accounts and also the percentage of the total to be allocated to a report line. The percentage gives some measure of cost allocation in this reporting
The defining of an external file involves three steps:
-
Establish an external file reference number and associated
-
Define the reference print line number and associated description for every print line on the report.
-
Define the GL account numbers or sub-ledger account numbers whose accounting data is to be printed on the specified line of the
The external file as noted is used in conjunction with a user-defined report. In this context, the user- defined report will establish the titles for the top of each page of the report. The external file will then define the headings, sub-headings and the description related to each detail line of the resulting report. The external file will define the GL accounts whose dollar values will make up the printed values shown on the report. The external file will define when the totals and sub-totals will be printed within that external report as well as the associated line and page skipping. The user- defined report record field definitions and total fields definitions define what fields are to be printed on the report for both the detail line printing and the total line printing. It is with this recognized merging of definition of the user-defined report and the external file report that we will use in the following section of the documentation.
NOTE:
If the change involved in an external file involves the adding of a brand new line to the report, this is accomplished through the CREATE EXTERNAL FILE option. If, on the other hand, the change required to an external file is to add another range of GL numbers to an existing line of the report, this is accomplished through an external file change operation.
#1 - ESTABLISH EXTERNAL FILE NUMBER
The ESTABLISH EXTERNAL FILE NUMBER module displays the list of currently existing external files on its menu.
FILE NAME #
When creating the external file, you must give the system a 2-digit number for a file name. The actual file name created on the computer will be BAEX## (The ## will be replaced by
the 2-digit number specified). This is the file name to enter in the report header record (see Basic User Defined Parameters) to allow you to create this type of report.
FILE NAME TO CREATE
This is a user-defined file name, maximum size 20 characters which will be displayed on the user-defined external file menu.
#2 - DEFINE A PRINT LINE
The DEFINE A PRINT LINE can be a header line, a detail line, a detail line referring to journal entries, a journal entry line, a negative line or a total line. With the exception of heading and total lines, the system will expect the definition of a series of one or more GL account numbers or ranges of account numbers whose accounting balances make up the desired print line contents. This will be defined fully in #3 - GL Selection Record.
01 = EXTERNAL REPORT CODE (34)
<CR> to allow the system to default a code number beginning at 1000 and incrementing by 10 each new record.
Enter the number you want to use.
02 = DESCRIPTION (36)
Enter the description you want printed on the report for this line.
03 = CODE [T##/N/H/D/DJ/J] <>
'T##' is total record where 01 is the highest level and 99 the lowest.
'N' prints the line with a reversal of the sign on the field. This would be appropriate where you want to show revenue accounts but now show the negative sign on them.
'D' is a detail record with GL selection records attached.
'DJ' is a detail record with GL selection records attached and prints the journal entries immediately after the detail line.
'J' prints the journal entries only and gives control of the location where these are to be printed.
04 = SP BEFORE [##/TP/NP] <1>
Enter the number of lines to skip or 'TP' for top of page and 'NP' for top of page starting page numbering at 1 again.
05 = SP AFTER [##/TP/NP] <1>
As above.
06 = INDENTATION [-99/100] <0>
Enter a number to adjust the location of the columns as described in your report detail. This allows you to shift columns for effect.
FIELD # TO CHANGE <CR>
Enter the field to change or <CR> to accept this record.
#3 - GL SELECTION RECORD
-
The GL SELECTION RECORD is associated with every non-heading and non-total print line as defined in #2 Define A Print Line. The user defines one or more sets of GL account numbers which make up the associated printing line. For a print line, these GL selection records must be in the order of the GL chart of accounts. Each record may consist of a range of general ledger account numbers which may be as small as a single GL account number or as large as is appropriate. The GL account number specified must be valid as they will be validated against the GL master
01 = STARTING G/L ACCOUNT
Enter a valid GL or sub-ledger account number.
<CR> indicates to the system there are no more GL selection records appropriate to the associated record print line.
02 = ENDING G/L ACCOUNT
As above.
03 = PRORATION % <100.00%>
Normally enter <CR>.
A pro-ration %, if entered, indicates that only the specified percentage of the range of accounts specified above are to be included in the dollar figures associated in the print line. By this means a portion of the GL account could be specified related to one line on the report and another percentage on another line of the report. NOTE: There is no verification to identify if more than 100 per cent of an account has been included in a report.
FIELD # TO CHANGE <CR>
Enter the field to change or <CR> to accept this record.