POPULATION Example
The POPULATION example demonstrates the use of Report-Writer in formatting a report of census data, by region and state, for the United States. The base tables for this report are as follows:
• “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
Additional details for each of these table layouts are provided in the tables that follow:
Region Table Definition
Region Data for the Sample Report
State Table Definition
State Data for the Sample Report
Population Table Definition
Population Data for the Sample Report
Report formatting statements for the POPULATION report are explained here:
• 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), tot_under18, tot_18to65, and tot_over65 (populations of three age groups).
• The query contains a variable, $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 must enclose the entire variable=value clause and its delimiting parentheses within double quotes to pass it through Windows or Linux.
report rwsqldb pop "(year=1970)"
You can also run the report with the command:
report
In this case Report-Writer prompts you for the report name, database name, and value for $year.
• 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 and state.
• 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. The four numeric columns (tot, tot_under18, tot_18to65, and tot_over65) have 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 precedes a set of formatting statements that execute at the start of the report and write out the centered title at the top of the report. The dollar sign preceding year in the print statement for the second line of this title indicates that year is a variable entered at run time. The statements in this section also print underlined column headings. Report-Writer determines the locations of the headings from the positions of the column names given as variables to the .right (right justify) statements. Report-Writer determines the column positions from print locations for the associated columns in the .detail statements.
• The .header region statement precedes a set of formatting statements that execute 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 Report-Writer prints the heading and the detail lines for at least two states on a page.
• The .detail statement precedes a set of formatting statements to be processed for every row created by the query. The statements in this section create rows for each state and specify printing of the actual population data. By analyzing these statements, Report-Writer determines the positions of the columns used throughout the report in the .rt statements.
• The .footer statement precedes a set of formatting statements to be processed after Report-Writer has read the last state in each region and has processed the requisite .detail formatting statements. This report specification uses a .need statement to insure that the two lines in the footer both print on the same page. The statements in this section print a region heading, 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.
• The statements following the .header page statement specify the title at the top of the second page of the report, as well as a re‑specification of the column headings.
• The .footer page statement starts the block of statements that print 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 (determined as the right-most position printed in the formatting statements in the report).
/* POPULATION - Population Report */
.NAME pop
.OUTPUT pop.out
.LONGREMARK
The POPULATION report demonstrates a fairly common type
of report with subtotaling.
.ENDREMARK
.QUERY
select region.region, state.state,
pop.tot_18to65 + pop.tot_under18 + pop.tot_over65 as tot,
pop.tot_18to65, pop.tot_under18, pop.tot_over65
from region, state, pop
where state.statabbrev = pop.statabbrev
and state.regabbrev = region.regabbrev
and pop.year = $year
.SORT region, state
.DECLARE year = varchar(4) with prompt 'Enter Year:'
.FORMAT tot, tot_18to65, tot_under18, tot_over65 (' zzz,zzz,zzz')
.HEADER report
.NEWLINE 3
.UL .CE .PR 'Population of the United States, by Age Group' .NOU
.NEWLINE .CE .PR 'Data for the Year - ', $year(c4) .NL 2
.U .RT tot .PR 'Total Pop' .RT tot_18to65 .PR '18 to 65'
.RT tot_under18 .PR 'Under 18' .RT tot_over65 .P 'Over 65'
.NOU .NL 2
.HEADER region
.NEED 4 .PR 'Region: ', region .NL
.DETAIL
.NEED 2 .T5 .PR state(c20)
.T+11 .PR tot, tot_18to65, tot_under18, tot_over65 .NL
.FOOTER region
.NEED 2 .RT tot .PR '----------' .RT tot_18to65 .P '----------'
.RT tot_under18 .P '----------' .RT tot_over65 .P '----------'
.NL .PR 'Totals: ', region (c0) .T tot
.PR sum(tot), sum(tot_18to65), sum(tot_under18), sum(tot_over65)
.NL 2
.FOOTER report
.NEED 2
.RT tot .PR '-------------' .RT tot_18to65 .P '-------------'
.RT tot_under18 .P '-------------'
.RT tot_over65 .P '-------------' .NL
.PR 'USA Totals' .T tot
.PR sum(tot), sum(tot_18to65), sum(tot_under18), sum(tot_over65)
.NL
.HEADER page
.NL 3 .PR 'Population by State and Region: ', $year .NL 2
.U .RT tot .P 'Total Pop' .RT tot_18to65 .P '18 to 65'
.RT tot_under18 .P 'Under 18' .RT tot_over65 .P 'Over 65' .NOU
.NL 2
.FOOTER page
.NL
.PR 'Source: US Department of the Interior, Bureau of the
Census.'
.RIGHT .PR 'Page', page_number('zN') .NL 4
Population of the United States, by Age Group
Data for the Year - 1970
Total Pop 18 to 65 Under 18 Over 65
Region: East North Central
Illinois 11,113,976 9,600,381 1,425,674 87,921
Indiana 5,193,669 4,820,324 357,464 15,881
Michigan 8,875,083 7,833,474 991,066 50,543
Ohio 10,652,017 9,646,997 970,477 34,543
Wisconsin 4,417,731 4,258,959 128,224 30,548
---------- ---------- ---------- ----------
Totals: East North Central 40,252,476 36,160,135 3,872,905 219,436
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
Pennsylvania 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
Montana 694,409 663,043 1,995 29,371
Nevada 488,738 448,177 27,762 12,799
New Mexico 1,016,000 915,815 19,555 80,630
Utah 1,059,273 1,031,926 6,617 20,730
Wyoming 332,416 323,024 2,568 6,824
---------- ---------- ---------- ----------
Totals: Mountain 8,281,562 7,798,087 180,382 303,093
Region: New England
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 England 11,841,663 11,388,774 388,398 64,491
Source: US Department of the Interior, Bureau of the Census. Page 1
Population by State and Region: 1970
Total Pop 18 to 65 Under 18 Over 65
Region: Pacific
Alaska 300,382 236,767 8,911 54,704
California 19,953,134 17,761,032 1,400,143 791,959
Hawaii 768,561 298,160 7,573 462,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 1,429,295
Region: South Atlantic
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 1,049,578 28,454
Georgia 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 Virginia 1,744,237 1,666,870 73,931 3,436
---------- ---------- ---------- ----------
Totals: South Atlantic 30,671,337 24,077,967 6,423,710 169,660
Region: West North Central
Iowa 2,824,376 2,782,762 32,596 9,018
Kansas 2,246,578 2,122,068 106,977 17,533
Minnesota 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,432,867 39,911 10,715
North Dakota 617,761 599,485 2,494 15,782
South Dakota 665,507 630,333 1,627 33,547
---------- ---------- ---------- ----------
Totals: West North Central 16,319,187 15,481,048 698,645 139,494
Region: West South Central
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,232 2,275,104 177,910 106,218
Texas 11,196,730 9,696,569 1,419,677 80,484
---------- ---------- ---------- ----------
Totals: West South Central 19,320,563 16,072,328 3,043,546 204,689
---------- ----------- ------------ ------------
USA Totals 203,165,702 177,612,309 22,672,573 2,880,820
Source: US Department of the Interior, Bureau of the Census. Page 2