• FOOTER report .NEED2
.BLOCK .WITHIN tot, totwhite, totblack, totother .RT .PRINTLINE "---"
.RT .PRINTLN sum(w_column) .END WITHIN
.RIGHT .PR "Page", page_number ("zN") .NL4
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. Deposits are listed in one column, withdrawals in another, and a running balance is listed in a third. The base tables used are the following:
174
• The "customer" table contains the name and address of a customer.
• The "account" table associates an account number with a customer name and address (because a customer may have more than one account). It also contains the balance of an account as of an arbitrary date. In actual accounting applications, this balance must be updated outside of the Report-Writer.
• The "transact" table contains a description of all transactions for an account. This contains columns "transnum" (the transaction number), "acctnum" (the account I
number), "date" (the date of the transaction), "amount" (the dollar amount of \"
transaction), and "type" (the type of transaction: 0 for deposits, 1 for withdrawals).
Report-Writer Reference Manual COT-DATA
,{-
i;"~?
,
'{
The report-fonnatting statements are discussed below:
• After the report is named, the query used to provide data for the report is shown.
This essentially retrieves the "transact" table, with data from the other tables joined in. The calculation of the columns "amt," "withdrawal," and "deposit" is also shown.
"Withdrawal" is set to "amount" if type is I, and set to zero if not. "Deposit" is set to "amount" if type equals 0, and set to zero otherwise. "Amt" is calculated as a signed value of "amount," which is negative for withdrawals and positive for deposits. "Amt" is used in calculating the running balance.
• The order of the data is described in the .sort statement. In the example output shown, only one account is shown for a name, though this type of sort order does not limit the report to that type of content.
• The .rormreeds statement tells the Report-Writer to insert fonnfeed 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 .rormat statements provide default fonnats for some of the output columns.
Note the use of "-" in the fonnat for "acctnum" to force hyphens in specific places in the output.
The .head name statement begins the set of fonnatting statements done at the start of each new name. The .newpage statement tells the 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. Next, the address is printed and lines are skipped.
• The .head acctnum block prints the opening balance, column headings, and sets a temporary fonnat for "acctnum" (so that it is printed for the first transaction only).
Notice that the positions associated with the columns are detennined from a scan of the fonnatting statements in the .detail section. Even a position for the "amt"
column is detennined, even though it is somewhat hidden in the cumulative sum function.
• The .head tdate block simply sets a temporary format for "date," so that the date is printed only the first time it is encountered.
• The .detail block simply prints out the lines in the report. It also detennines the default margins and column positions from an analysis of these statements. Notice that the fonnats for "date" and "acctnum," which specify nonprinting fonnats, may be overridden by the .trormat statements specified in the header text for "date" and
Account Example
The "cum(acctnum) sum(amt,balance)" aggregate specifies the calculation and printing of the running balance. The first part, "cum(acctnum)" specifies that the running balance is a cumulative aggregate, which is initialized at the most recent //
break in "acctnum." 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 "acctnum "). 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 accnum block prints out summations of the "withdrawal" and "deposit"
columns and the closing balance of the account. This figure, the "sum(amt,balance)"
aggregate, is calculated as the sum of "amt" for a specific "acctnum" (because of the context), and is then initialized to the value of "balance" at the start of"acctnum."
Remember that the figure is negative for withdrawals and positive for deposits.
Again, 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 .foot name block specifies the printing of an ending statement.
• The .head page block describes the heading shown at the top of each page. Note that 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 simply specifies a few lines to force at the end of each page.
176 Report-Writer Reference Manual DDT-DATA
(
.FORMAT acctnum(" nn\e-nnnnnn\e-n "), tdate (d"Ol/02/03"), withdrawal, deposit, amt, balance (" $$$, $$$, $$S. zz")
.TFORMAT acctnum(" nn\e-nnnnnn\e-n ") .FOOT acctnurn
Account Example
. DETAIL
.PR acctnum(b16), tdate(b16), .T+B.P transnum(nnnnnn), deposit, withdrawal .T+S .P cum(acctnum) sum(amt, balance) .NL .HEAD page
.NL 2
.PR "Customer: ", name .CE.P nDate: ", current_date (d"February 3,1901"), .RT .PR "Page ", page_number .NL4 Topofthehill NJ 05436
Account: 74-902543-6
Account 74-902543-6 totals.
End of accounts for: Big Daddy
178 Report-Writer Reference Manual
Page
Opening balance: $234.657.00
Withdrawal Balance
Closing balance: $388.545.34
ODT-DATA /
\
"-.
(
Account 48-821908-2 totals.End of accounts for: Fast Sally
Opening balance: Sl.245.00
withdrawal Balance
Closing balance: 5707.70