ACCOUNT Example
The ACCOUNT example shows a fairly complex report that could be written from some accounting data. For each account, the report prints the name and address of a customer, followed by a listing of transactions in an account. The report lists deposits in one column, withdrawals in another, and a running balance in a third.
The following base tables are used:
• The table, “customer table,” which contains the name and address of a customer
• The “account” table, which associates an account number with a customer name and address (because a customer can have more than one account)
This table also contains the balance of an account as of an arbitrary date. In actual accounting applications, this balance must be updated outside of Report-Writer.
• The “transact” table, which contains a description of all transactions for an account
This table contains columns, transnum (the transaction number), acct num (the account number), tdate (the date of the transaction), amount (the dollar amount of transaction), and type (the type of transaction: 0 for deposits, 1 for withdrawals).
The following tables provide additional details on each of the database table layouts:
Customer Table Definition
Customer Data for the Sample Report
Account Table Definition
Account Data for the Sample Report
Transact Table Definition
Transact Data for the Sample Report
The report formatting statements are described here:
• The .delimid statement enables recognition of delimited identifiers used as a table name (customer table) and column names (c name and acct num).
• The .declare section declares the variables $min_limit and $max_limit, to be given values in response to a run‑time prompt, and $final_balance, with an initial value of 0.00.
• The query, shown after the .longremark section, provides data for the report. This query retrieves the transact table, with data from the other tables joined in. The specification also shows the calculation of the columns amt, withdrawal, and deposit. The withdrawal value is set to amount if type is 1; otherwise, it is set to zero. The depositvalue is set to amount if type equals 0; otherwise, it is set to zero. The value of amt is calculated as a signed value of amount, which is negative for withdrawals and positive for deposits. The amt value is used in calculating the running balance.
• The .sort statement describes the order of the data. In the example output, only one account appears for each name, although this sort order would print additional accounts for each name if they existed in the data.
• The .formfeeds statement inserts form feed characters at the start of the report and at the end of each page of the report. Because no .pagelength statement is specified, a default page size of 61 lines is assumed. The default is determined by the output for the report. The output can be to a screen, file, or printer.
• The .format statements provide default formats for some of the output columns. The hyphen (‑) in the format for acct num forces hyphens in specific places in the output.
• The .header statement begins the set of formatting statements to be executed at the start of each new name. The .newpage statement tells Report-Writer to skip to the top of a new page and to set the page number to the value 1 at the start of each new name. The next statements print the address and skip some lines.
• The .head acct num block prints the opening balance, column headings, and sets a temporary format for acct num (so that it is printed for the first transaction only). Report-Writer determines the positions associated with the columns from a scan of the formatting statements in the .detail section, including a position for the amt column, which is somewhat hidden in the cumulative sum function.
• The .head tdate block sets a temporary format for tdate, so that Report-Writer prints the date only the first time it encounters that particular date value.
• The .detail block prints out the lines in the report. It also determines the default margins and column positions from an analysis of these statements. The formats for tdate and acct num, which specify nonprinting formats, can be overridden by the .tformat statements specified in the header text for tdate and acct num.
The "cum("acct num") sum(amt,balance)" aggregate specifies the calculation and printing of the running balance. The first part, "cum("acct num")" specifies that the running balance is a cumulative aggregate, which is initialized at the most recent break in "acct num." The rest, "sum(amt,balance)," specifies that the cumulative aggregate is a sum of "amt," and that the cumulative is to be initialized to the value of "balance" when the report starts (at the most recent break in "acct num"). The format to be used is specified as the default for "amt" because the aggregate specification is not followed by a parenthesized format.
• The .foot acct num block prints out summations of the withdrawal and deposit columns and the closing balance of the account, as calculated in the "sum(amt,balance)" aggregate. Report-Writer calculates the closing balance as the sum of amt for a specific acct num (because of the context), and then initializes it to the value of balance at the start of "acct num." Remember that the figure is negative for withdrawals and positive for deposits. Because the aggregate specification is not followed by a parenthesized format specification, the .format statement for amt at the beginning of the report is used as the default format for the aggregate.
The .if‑.elseif‑.endif block prints a "Balance below ..." or "Balance exceeds ..." message if the customer's closing balance is less than the established minimum ($min_limit) or greater than the established maximum ($max_limit).
• The .foot name block specifies the printing of an ending statement.
• The .head page block describes the heading shown at the top of each page. The .newpage statement in the .head name statements forces the printing of the page header on the first page (which normally does not happen).
• The .foot page block tells Report-Writer to skip some lines at the end of each page.
The pages following the report specification contain sample reports generated with the following values:
• In the first example, $min_limit = 500.00 and $max_limit = 100,000.00
• In the second example, $min_limit = 1,000.00 and $max_limit = 250,000.00
/* ACCOUNT - example of bank statement report. */
.NAME account_delim
.OUTPUT account_delim.out
.LONGREMARK
The ACCOUNT_DELIM report shows a fairly complex report that could be
written for some accounting data. For each account, the report prints
the name and address of a customer, followed by a listing of
transactions in an account. Deposits are listed in one column,
withdrawals in another, and a running balance is listed in a third.
The report orders the transactions in LIFO date order. It also
demonstrates the use of:
o schema.tablename
o delimited identifiers
o decimal datatypes
.ENDREMARK
.DELIMID
.DECLARE
min_limit = decimal(12,2) with prompt
'Enter minimum balance flag level: ',
max_limit = decimal(12,2) with prompt
'Enter maximum balance flag level: ',
final_balance = decimal(12,2) with value '0.00'
.QUERY
SELECT "c tbl"."c name", "c tbl".address, "c tbl".city,
"c tbl".state, "c tbl".zip,
a."acct num", a.balance,
t.transnum, t.tdate,
t.amount * t.type AS withdrawal,
t.amount * (1 - t.type) AS deposit,
(t.amount * (1 - t.type)) - (t.amount *
t.type) AS amt
FROM transact t, account a, dave."customer table" "c tbl"
WHERE a."acct num" = t.acctnum and "c tbl"."c name" = a.name
.SORT "c name", "acct num", tdate:d, transnum
.FORMAT "acct num" (' nn\-nnnnnn\-n '),
tdate (d'01/02/03'),
withdrawal, deposit, amt, balance (' $$$,$$$,$$$.zz')
.HEAD "c name"
.NEWPAGE
.NL 3
.PRINT "c name"
.NL
.PRINT address
.NL
.PRINT city (c0),' ', state (c0),' ', zip ('nnnnn')
.NL 4
.FOOT "c name"
.NL 3
.PRINT 'End of accounts for: ', "c name"
.NL
.HEAD "acct num"
.NL 3
.PRINT 'Account: ', "acct num"
.RT amt
.PRINT 'Opening balance:', balance
.NL 2
.UL
.CE "acct num"
.PRINT 'Account'
.CE tdate
.PRINT 'Date'
.CE transnum
.PRINT 'Transaction'
.RT deposit
.PRINT 'Deposit'
.RT withdrawal
.PRINT 'Withdrawal'
.RT amt
.PRINT 'Balance'
.NL
.NOU
.TFORMAT "acct num" (' nn\-nnnnnn\-n ')
.FOOT "acct num"
.NL 2
.PRINT 'Account', "acct num", 'totals.'
.TAB deposit
.PRINT sum(deposit)
.TAB withdrawal
.PRINT sum(withdrawal)
.NL 2
.LET final_balance = sum(amt, balance)
.RT amt
.PRINT 'Closing balance:', $final_balance (' $$$,$$$,$$$.zz')
.IF $final_balance < $min_limit .THEN
.NL
.PRINT '*** Balance below established minimum of ',
$min_limit,' ***'
.ELSEIF $final_balance > $max_limit .THEN'
.NL
.PRINT '*** Balance exceeds established maximum of ',
$max_limit,' ***'
.ENDIF
.HEAD tdate
.TFORMAT tdate (d'01/02/03 ')
.DETAIL
.PRINT "acct num" (b16), tdate (b16),
.TAB +8
.PRINT transnum ('nnnn'), deposit, withdrawal
.TAB +5
.PRINT cum("acct num") sum(amt, balance)
.NL
.HEAD page
.NL 2
.PRINT 'Customer: ', "c name"
.CE
.PRINT 'Date: ', current_date (d'February 3, 1901'),
.RT
.PRINT 'Page ', page_number
.NL 4
.FOOT page
.NL 3
Customer: P.J. Megabucks Date: July 27, 2000 Page 1
P.J. Megabucks
1 Panorama Lane
Hilltop CT 12345
Account: 74-902543-6 Opening balance: $234,657.00
Account Date Transaction Deposit Withdrawal Balance
74-902543-6 93/07/23 0109 $25,000.00 $288,545.34
0110 $100,000.00 $388,545.34
93/07/22 0108 $100.00 $313,545.34
93/07/17 0106 $10,143.54 $313,888.90
0107 $243.56 $313,645.34
93/07/15 0105 $50,000.00 $324,032.44
0101 $100,000.00 $274,032.44
93/07/01 0102 $50,500.00 $184,157.00
0103 $24.56 $174,032.44
0104 $10,100.00 $174,057.00
Account 74-902543-6 totals. $250,000.00 $96,111.66
Closing balance: $388,545.34
*** Balance exceeds established maximum of 250000.00 ***
End of accounts for: P.J. Megabucks
Customer: C. Richard Runn Date: July 27, 2000 Page 1
C. Richard Runn
123 Primer Path
Reading PA 23456
Account: 48-821908-2 Opening balance: $234,657.00
Account Date Transaction Deposit Withdrawal Balance
48-821908-2 93/07/25 0109 $31.16 $707.70
93/07/20 0108 $120.00 $738.86
93/07/16 0107 $24.71 $858.86
93/07/10 0106 $56.32 $883.57
93/07/08 0105 $100.00 $939.89
93/07/05 0103 $320.34 $974.66
0104 $65.23 $1,039.89
93/07/03 0102 $250.00 $1,295.00
93/05/25 0101 $200.00 $1,045.00
Account 48-821908-2 totals. $315.23 $852.53
Closing balance: $707.70
End of accounts for: C. Richard Runn