F. Report-Writer Report Examples : ACCOUNT Example
 
Share this page                  
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
Column Name
Type
Length
Nulls
Defaults
"c name"
char
20
yes
no
address
char
20
yes
no
city
char
20
yes
no
state
char
2
yes
no
zip
integer
4
yes
no
balance
decimal
(12,2)
yes
no
Customer Data for the Sample Report
"c name"
Address
city
state
zip
P.J. Megabucks
1 Panorama Lane
Hilltop
CT
12345
C. Richard Runn
123 Primer Path
Reading
PA
23456
Account Table Definition
Column Name
Type
Length
Nulls
Defaults
name
char
20
yes
no
"acct num"
integer
4
yes
no
balance
decimal
(12,2)
yes
no
Account Data for the Sample Report
name
acctnum
balance
P.J. Megabucks
749025436
234657.00
C. Richard Runn
488219082
1245.00
Transact Table Definition
Column Name
Type
Length
Nulls
Defaults
acctnum
integer
4
yes
no
tdate
date
 
yes
no
transnum
integer
4
yes
no
type
integer
4
yes
no
amount
decimal
(12,2)
yes
no
Transact Data for the Sample Report
acctnum
Tdate
transnum
type
amount
749025436
01-jul-1998
0101
0
100000.00
749025436
01-jul-1998
0102
1
50500.00
749025436
01-jul-1998
0103
1
24.56
749025436
01-jul-1998
0104
1
10100.00
749025436
15-jul-1998
0105
0
50000.00
749025436
17-jul-1998
0106
1
10143.54
749025436
17-jul-1998
0107
1
243.56
749025436
22-jul-1998
0108
1
100.00
749025436
23-jul-1998
0109
1
25000.00
749025436
23-jul-1998
0110
0
100000.00
488219082
25-may-1998
0101
1
200.00
488219082
03-jul-1998
0102
0
250.00
488219082
05-jul-1998
0103
1
320.34
488219082
05-jul-1998
0104
0
65.23
488219082
08-jul-1998
0105
1
100.00
488219082
10-jul-1998
0106
1
56.32
488219082
16-jul-1998
0107
1
24.71
488219082
20-jul-1998
0108
1
120.00
488219082
25-jul-1998
0109
1
31.16
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