Population Example
The POPULATION example demonstrates the use of the Report-Writer in formatting a report of census data, by region and state, for the United States. The base tables for this report are ''' .. / the following:
• "Region" contains region names associated with region abbreviations.
• "State" contains state names, as well as state abbreviations, and associated region abbreviations.
• "Pop" contains population data for each state for different census years.
The report formatter statements are discussed below. Notice that the output for this report is followed by a description and listing of a slightly different set of statements that can be used to produce the same output.
• The .query statement shows the database query needed to set up the data in the form required to write the report. Essentially, the query sets up a table with one row for each state, including the columns "region" (name of region), "state" (name of state),
"tot" (the total population of the state), "totwhite," "totblack," and "totother"
(populations of three racial groups).
• The query contains a parameter, "$Year," which is used in the where clause to select data for only one census year. In the example shown, you can select the data for 1970 by running the report with the command:
report rwsqldb pop' (year=1970), You can also run the report with the following:
report
In this case the Report-Writer prompts you for the report name, database name, and value for
"year."
166
• The .sort statement specifies a sorting of the data by "region," and within region, by "state." This also defines potential break actions for changes in value of "region" I
and "state." \.
Report-Writer Reference Manual ODT-DATA
(
(
• The .format statement sets up a default format for a set of columns in the report.
These are used not only for the printing of the actual data but also for the printing of subtotals based on that data. Note that the four numeric columns ("tot,"
"totwhite," "totblack," and "totother") are given the same format specification.
Actually, the .format statement is not strictly needed, but provides a convenient way to specify the same format for a number of columns.
• The .header report statement is followed by a set of formatting statements that are run at the start of the report. There is nothing particularly elegant about the formatting statements, which write out the centered title seen at the top of the report.
Note the value of parameter "year" is preceded by a dollar sign to indicate that it is a parameter. Also, underlined column headings are printed in this section. The locations of the headings are based on the positions of the column names given as parameters to the .rt (right justify) statements. These positions are determined by the location at which the associated column is printed in the .detail statements.
• The .header region statement is followed by a set of formatting statements that are run at the start of each region. The .need statement insures that at least four lines are available on a page before printing the heading for "region." This assures that the heading and the detail lines for at least two states are printed on a page.
• The .detail statement is followed by formatting statements that are processed for every row created by the query. In this case, rows are created for each state, and the statements specify printing of the actual population data. By analyzing these statements, the Report-Writer determines the "positions" of the columns used throughout the report in the .rt statements.
• The .footer statement is followed by formatting statements, which are processed after the last state in each region is read and the requisite .detail formatting statements are processed. A .need statement is included simply to assure that the two lines in the footer are printed on the same page. A region heading is printed and followed on the same line with the values of some subtotals for the region. The formats used in printing the subtotals are those specified in the .format statement at the start of the report.
• The statements following the .footer report statement are almost identical to those following the .footer region statement, except for the heading and length of the dashed line separators. The values of the subtotals, however, are different because
of the different context. '
Population Example
168
• The statements following the .header page statement specify the title seen at the top of the second page of the report, as well as a respecification of the column headings.
(A macro facility is planned to simplify this respecification in the future.)
• The footer page statement starts the block of statements printed at the bottom of \ each page, including the current page number. Because the .right statement has no parameters, the text is justified to the right margin (detennined as the rightmost position printed in the fonnatting statements in the report).
/* POPULATION Population Report */
.NAME pop . QUERY
select region. region, state. state,
tot = pop.totwhite + pop.totblack + pop.totc::her.
pop.totwhite, pop.to::black, pop.totothe from region, state, pop
where state.statabbrev = pop.statabbrev and state.regabbrev = region.regabbrev and pop. year
=
$Year.SORT region, state
.FORMAT tot, totwhite, totblack, totother (" Z,ZZZ,ZZZ,ZZZ") . HEADER report
Report-Writer Reference Manual ODT-DATA
(
\
"-(
.PR sum(tot) , sum(totwhite), sum(totblack), sum (totother) .NL2
• FOOTER report
.NEED 2 .RT tot .PR "---" .RT totwhite .P "---"
.RT totblack .P "---" .RT totother .P "---,, .NL .PR "USA Totals" • T tot
.PR sum(tot) , sum(totwhite), sum(totblack), sum (totother) .NL
• HEADER page
.RIGHT .PR "Page", page_number("zN") .NL4
Report output:
Population of the United States. by Race Data for the Year - 1970
Total pop White Pop Black pop Re9ion, East North Central
Illinois 11.113.976 9.600.381 1.425.674
Indiana 5.193.669 4.820.324 357.464
Michi9an 8.875.083 7.833.474 991.066
Ohio 10.652.017 9.646.997 970.477
Wisconsin 4.417.731 4.258.959 128.224
Other Pop
---Totals, East North Central 40.252.476 36.160.135 3.872.905 219.436
Population Example
Region: East South Central
Alabama 3,444,165 2,528,983 908,247 6,935
Kentucky 3,218,706 2,971.232 241,292 6,182
Mississippi 2,216,912 1.393,283 815,770 7,859
Tennessee 3,923,687 3,283,432 631. 696 8,559 , /
--- ---
---Totals: East South Central 12,803,470 10,176,930 2,597,005 29,535 \
Region: Middle Atlantic
New Jersey 7,168,164 6,349,908 770,292 47,964
New York 18,190,740 15,790,307 2,166,933 233,500
Pennsyl vania 11,793,909 10,737,732 1,016,514 39,663
--- ---
---Totals: Middle Atlantic 37,152,813 32,877,947 3,953,739 321.127
Region: Mountain
Arizona 1,770,900 1. 604, 948 53,344 112,608
Colorado 2,207,259 2,112,352 66,411 28,496
Idaho 712,567 698,802 2,130 11,635
---Totals: Mountain 6,281,562 7,798,087 180,382 303,093
Region: New Engla!':.d
"
connecticut 3,031,709 2,835,458 181,177 15,074
Maine 992,048 985,276 2,800 3,972 /
Massachusetts 5,689,170 5,477,624 175,817 35,729
New Hampshire 737,681 733,106 2,505 2,070
Rhode Island 946,725 914,757 25,338 6,630
Vermont 444,330 442,553 761 1. 016
--- ---
---Totals: New Engla::c. 11,841,663 11,388,774 388,398 64,491
Source: US Departtent of the interior. Bureau of the Census. Page 1
Popula tion by Stat.e and Region: 1970
Total Pop White Pop Black Pop O':.!ler Pop
Region: Pacific
Alaska 300,382 236,767 8,911 54,704
California 19,953,134 17,761,032 1,400,143 791. 959
Ha-..aii 768,561 298,160 7,573 ~62, 828
Oregon 2,091,385 2,032,079 26,308 32,998 / '
Washington 3,409,169 3,251.055 71,308 86,806 (
~
--- ---
'-Totals: Pacific 26,522,631 23,579,093 1,514,243 ,,429,295
170 Report-Writer Reference Manual ODT-DATA
(
Region: South A.tlantic
Delaware 548.104 466.459 78.276 3.369
District of Columbia 756.510 209.272 537.712 9.526
Florida 6.789.443 5.711.411 :.049.578 28.454
Gear;ia 4.589.575 3.387.516 1.190.779 11.280
Maryland 3.922.399 3.193.021 701. 341 28.037
North Carolina 5.082.059 3.891.510 1. 137.664 52.885
South Carolina 2.590.516 1. 794.430 789.041 7.045
Virginia 4.648.494 3.757.478 865.388 25.628
West Virqinia 1.744.237 1. 666. 870 73.931 3.436
---
---Totals. South Atlantic 30.671.331 24.077.967 6.423.710 169.660
Reqion. West Horth Central
I ow. 2.824.376 2.782.762 32.596 9.018
Kansas 2.246.578 2.122.068 106.977 11.533
Kinnesota 3.804.971 3.736.038 34.868 34.065
Missouri 4.676.501 4.177.495 480.172 18.834
Nebraska 1. 483. 493 1.4323.867 39.911 10.715
Arkansas 1.923.295 1.561.108 357.225 4.962
Louisiana 3.641.306 2.539.547 1.088.734 13.025
Oklahoma 2.559.229 2.275.104 177.910 106.218
Texas 11.196.730 9.696.569 1. 4l9. 677 80.484
---
---Total •• West South Central 19.320.560 16.072.328 3.043.543 204.689
---
--- ---
---USA Totals 203.165.699 177.612.309 22.672.570 2.880.820
Source: US Department of the Interior, Bureau of the Census. Page 2