Was this helpful?
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
Column Name
Type
Length
Nulls
Defaults
regabbrev
char
3
yes
no
region
char
20
yes
no
Region Data for the Sample Report
regabb
region
ENC
East North Central
ESC
East South Central
M
Mountain
MA
Middle Atlantic
NE
New England
P
Pacific
SA
South Atlantic
WNC
West North Central
WSC
West South Central
State Table Definition
Column Name
Type
Length
Nulls
Defaults
regabbrev
char
3
yes
no
stateabbrev
char
2
yes
no
state
char
20
yes
no
State Data for the Sample Report
regabb
Statab
state
ENC
IL
Illinois
ENC
IN
Indiana
ENC
MI
Michigan
ENC
OH
Ohio
ENC
WI
Wisconsin
ESC
AL
Alabama
ESC
KY
Kentucky
ESC
MS
Mississippi
ESC
TN
Tennessee
MA
NJ
New Jersey
MA
NY
New York
MA
PN
Pennsylvania
M
AZ
Arizona
M
CO
Colorado
M
ID
Idaho
M
MT
Montana
M
NV
Nevada
M
NM
New Mexico
M
UT
Utah
M
WY
Wyoming
NE
CN
Connecticut
NE
ME
Maine
NE
MA
Massachusetts
NE
NH
New Hampshire
NE
RI
Rhode Island
NE
VT
Vermont
P
AK
Alaska
P
CA
California
P
HI
Hawaii
P
OR
Oregon
P
WA
Washington
SA
DE
Delaware
SA
DC
District of Columbia
SA
FL
Florida
SA
GA
Georgia
SA
MD
Maryland
SA
NC
North Carolina
SA
SC
South Carolina
SA
VA
Virginia
SA
WV
West Virginia
WNC
IA
Iowa
WNC
KS
Kansas
WNC
MN
Minnesota
WNC
MO
Missouri
WNC
NB
Nebraska
WNC
ND
North Dakota
WNC
SD
South Dakota
WSC
AR
Arkansas
WSC
LA
Louisiana
WSC
OK
Oklahoma
WSC
TX
Texas
Population Table Definition
Column Name
Type
Length
Nulls
Defaults
year
integer
4
yes
no
stateabbrev
char
2
yes
no
tot_18to65
integer
4
yes
no
tot_under18
integer
4
yes
no
tot_over65
integer
4
yes
no
Population Data for the Sample Report
year
statab
tot_18to65
tot_under18
tot_over65
1970
IL
9600381
1425674
87921
1970
IN
4820324
357464
15881
1970
MI
7833474
991066
50543
1970
OH
9646997
970477
34543
1970
WI
4258959
128224
30548
1970
AL
2528983
908247
6935
1970
KY
2971232
241292
6182
1970
MS
1393283
815770
7859
1970
TN
3283432
631696
8559
1970
NJ
6349908
770292
47964
1970
NY
15790307
2166933
233500
1970
PN
10737732
1016514
39663
1970
AZ
1604948
53344
112608
1970
CO
2212352
66411
28496
1970
ID
698802
2130
11635
1970
MT
663043
1995
29371
1970
NV
448177
27762
12799
1970
NM
915815
19555
80630
1970
UT
1031926
6617
20730
1970
WY
323024
2568
6824
1970
CN
2835458
181177
15074
1970
ME
985276
2800
3972
1970
MA
5477624
175817
35729
1970
NH
733106
2505
2070
1970
RI
914757
25338
6630
1970
VT
442553
761
1016
1970
AK
236767
8911
54704
1970
CA
17761032
1400143
791959
1970
HI
298160
7573
462828
1970
OR
2032079
26308
32998
1970
WA
3251055
71308
86806
1970
DE
466459
78276
3369
1970
DC
209272
537712
9526
1970
FL
5711411
1049578
28454
1970
GA
3387516
1190779
11280
1970
MD
3193021
701341
28037
1970
NC
3891510
1137664
52885
1970
SC
1794430
789041
7045
1970
VA
3757478
865388
25628
1970
WV
1666870
73931
3436
1970
IA
2782762
32596
9018
1970
KS
2122068
106977
17533
1970
MN
3736038
34868
34065
1970
MO
4177495
480172
18834
1970
NB
1432867
39911
10715
1970
ND
599485
2494
15782
1970
SD
630333
1627
33547
1970
AR
1561108
357225
4962
1970
LA
2539547
1088734
13025
1970
OK
2275104
177910
106218
1970
TX
9696569
1419677
80484
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
Last modified date: 11/09/2022