ACCELL/SQL: Creating Reports with RPT Report Writer

Full text

(1)R. ACCELL/SQL: Creating Reports with RPT Report Writer.

(2) E )> #40-> 57657(9054 !(*7(3,495 (20-5740( #! 22 70./98 7,8,7;,+ 7049,+ 04 #! 5 6(79 5- 9/08 +5*:3,49 3(> ), 7,675+:*,+ 97(483099,+ 97(48*70),+ 8957,+ 04 ( 7,970,;(2 8>89,3 57 97(482(9,+ 0495 (4> 2(4.:(., 57 *536:9,7 2(4.:(., 04 (4> -573 57 )> (4> 3,(48 ,2,*97540* 3,*/(40*(2 3(.4,90* 5690*(2 */,30*(2 3(4:(2 57 59/,7<08, <09/5:9 9/, 67057 <7099,4 *548,49 5- #40-> 57657(9054 #40-> 57657(9054 3(1,8 45 7,67,8,49(90548 57 <(77(490,8 <09/ 7,86,*9 95 9/, *549,498 5- 9/08 +5*:3,49 (4+ 86,*0-0*(22> +08*2(038 (4> 03620,+ <(77(490,8 5- 3,7*/(49()0209> 57 -094,88 -57 (4> 6(790*:2(7 6:7658, :79/,7 #40-> 57657(9054 7,8,7;,8 9/, 70./9 95 7,;08, 9/08 +5*:3,49 (4+ 95 3(1, */(4.,8 -753 903, 95 903, 04 098 *549,49 <09/5:9 ),04. 5)20.(9,+ 95 4590-> (4> 6,7854 58:*/ 7,;080548 57 */(4.,8 "/, !5-9<(7, +,8*70),+ 04 9/08 +5*:3,49 08 -:7408/,+ :4+,7 ( !5-9<(7, 0*,48, .7,,3,49 "/, !5-9<(7, 3(> ), :8,+ 57 *560,+ 542> 04 (**57+(4*, <09/ 9/, 9,738 5- 9/, 20*,48, (.7,,3,49 9 08 (.(0489 9/, 2(< 95 *56> 9/, !5-9<(7, 54 9(6, +081 57 (4> 59/,7 3,+0:3 -57 (4> 6:7658, 59/,7 9/(4 9/(9 +,8*70),+ 04 9/, 20*,48, (.7,,3,49 #40-> 57657(9054 ;(2:,8 (4+ (667,*0(9,8 (4> *533,498 >5: 3(> /(;, *54*,7404. 5:7 675+:*98 57 9/08 +5*:3,49 2,(8, (++7,88 *533,498 95. 75+:*9 (4(.,7 #40-> 57657(9054 7,4( 5:2,;(7+ !9, !(*7(3,495

(3) & #' $! (4+ 9/, #40-> 5.5 (7, 7,.089,7,+ 97(+,3(718 5- #40-> 57657(9054 #40-> (9(!,7;,7 08 ( 97(+,3(71 5- #40-> 57657(9054 #& 08 ( 7,.089,7,+ 97(+,3(71 5- 9/, 6,4 75:6 04 9/, #409,+ !9(9,8 (4+ 59/,7 *5:4970,8 "/, & %04+5< !>89,3 08 ( 675+:*9 5- 9/, (88(*/:8,998 48909:9, 5",*/4525.> 590- ! (4+ !590- (7, 97(+,3(718 5- 6,4 !5-9<(7, 5:4+(9054 4* !'! 08 ( 7,.089,7,+ 97(+,3(71 (4+ ! !,7;,7 @0)7(7> (4+ 6,4 !,7;,7 (7, 97(+,3(718 5- !>)(8, 4* & 08 ( 7,.089,7,+ 97(+,3(71 5- 4-5730= !5-9<(7, 4* ( 8:)80+0(7> 5- ! 08 ( 97(+,3(71 5- 536:9,7 885*0(9,8 49,74(9054(2 4* 08 ( 7,.089,7,+ 97(+,3(71 5- 7(*2, 57657(9054 !:4 08 ( 7,.089,7,+ 97(+,3(71 (4+ !:4$0,< !:4@ !:4@ &,%! !:4! @! (4+ 6,4 %04+5<8 (7, 97(+,3(718 5- !:4 0*758>89,38 22 ! 97(+,3(718 (7, 97(+,3(718 57 7,.089,7,+ 97(+,3(718 5- ! 49,74(9054(2 4* ! 89(9054 08 20*,48,+ ,=*2:80;,2> 95 !:4 0*758>89,38 4* 5;,22 08 ( 7,.089,7,+ 97(+,3(71 5- 5;,22 4* (*04958/ 08 ( 97(+,3(71 5- 662, 536:9,7 4* 0*7585-9 ! !@! (4+ %04+5<8 (7, 7,.089,7,+ 97(+,3(718 5- 0*7585-9 57657(9054 22 59/,7 675+:*98 57 8,7;0*,8 3,49054,+ /,7,04 3(> ), 7,.089,7,+ 97(+,3(718 97(+,3(718 57 8,7;0*, 3(718 5- 9/,07 7,86,*90;, 3(4:-(*9:7,78 *536(40,8 57 57.(40?(90548. (79 :3),7

(4) . 2.

(5) . . . . ' 0 (/ $-,.0 .(0$.

(6) . . $-,.0 .(0$. ,*-,+$+0/ $-,.0 +-10 $-,.0 ".(-0 . . ,3 $-,.0 .(0$. .,"$//$/ $-,.0 ".(-0 $-,.0 2$+0/ $-,.0 &(+ 0(,+ 2$+0/ +# 2$+0 $"0(,+/ ,.0(+& 2$+0/ +# 2$+0 $"0(,+/ +-10 0 2$+0/ +# 2$+0 $"0(,+/ .,"$//(+& 2$+0 $"0(,+/ . . '$ $-,.0 .(0$. 0()(0($/ '$ (./ 0()(04 '$ 0()(04 . . ,+%(&1.(+& $-,.0 '$ !)$ / &$ $-,.0 $+$. 0(+& !)$ / &$ $-,.0 '$ !)$ / &$ $-,.0 !)$ )$*$+0/ +# //,"( 0$# ,+%(&1. 0(,+ .( !)$/ ' +&(+& !)$ (5$ ,+%(&1. 0(,+ .( !)$/ $+$. ) ,+%(&1. 0(,+ .( !)$/ (/-) 4 ,.* 0 ,+%(&1. 0(,+ .( !)$/ )$*$+0 !)$ ,+%(&1. 0(,+ .( !)$/ . . . .

(7) . . 1()#(+& 0'$ $-,.0 ".(-0 $"0(,+/ +-10 $"0(,+ +2(.,+*$+0 $"0(,+ $"0(,+ ##(+& . *$0$./ 0, 1$.4 $-,.0 ,.* 0 $"0(,+ $)$"0(+& .,* 1)0(-)$ $-,.0 ,.* 0/ . . 3.

(8) ##(,& / +$1$/0 1-. 4. -/+ 1 "/(.1 . . /(1(,& $.-/1 -/+ 1 $"1(-, $.-/1 -/+ 1 $"1(-, 5,1 4 '$ (,.21 1 1$+$,1 3$,1 $"1(-,0 !$%-/$ /$.-/1 %1$/ /$.-/1 '$ #$/ ,# %--1$/ !$%-/$ 0-/1)$5 #$1 (* %1$/ 0-/1)$5 -/+ 1 $"1(-, 1 1$+$,10 '$ ./(,1 1 1$+$,1 '$ 0)(. 1 1$+$,1 '$ 0-/1 1 1$+$,1 '$ ,$$# 1 1$+$,1 0(,& -" * 2,"1(-,0 0(,& 1/(,& /( !*$0 (/$"1 -," 1$, 1(-, .$/ 1-/ ,#(/$"1 -," 1$, 1(-, .$/ 1-/ .

(9)

(10)

(11)

(12) . 201-+(6(,& 1'$ $.-/1 "/(.1 ##(,& 201-+ / +$1$/ /-+.1$/ ##(,& 201-+ $.-/1 -/+ 1 $*$"1-/ /(1(,& ,# 0(,& 7--)0 --) 5,1 4 0(,& 7--)0 ,1$&/ 1(,& 1'$ 7--) . . . . . .

(13)

(14)

(15) . . ' 1 (0 , 4./$00(-, .

(16). 0(,& 4./$00(-,0 4./$00(-, 3 *2 1(-, 2** *2$0 *(# 4./$00(-,0 . . 1 . -*2+,0 1 -*2+, 5,1 4 *(# 1 -*2+, 1 5.$0 1 -*2+, 1$&-/($0 . . -,01 ,10 2+$/(" -,01 ,10 1$ -,01 ,10 .

(17).

(18) *#/)+/) 4*' '(#5-4 #4' 02.#4 +.' 0/34#/43 42+/) 0/34#/43 '1'#4+/) *#2#%4'23 504'& *#2#%4'2 42+/)3 . . !#2+#$-'3 '4'2.+/+/) !#2+#$-' 81'3 /+4+#-+9+/) !#2+#$-'3 5+-4:+/ !#2+#$-'3 . . . 5/%4+0/3 ))2')#4' 5/%4+0/3 ))2')#4' 5/%4+0/ 8/4#7 *' 5+-4:+/ ))2')#4' 5/%4+0/3 :00,3 .

(19)

(20) . #.'& 712'33+0/3. . . 0)+%#- 712'33+0/3 . . 1'2#4023 5.'2+% 1'2#4023 42+/) 1'2#4023 *' +2'%4 0/%#4'/#4+0/ 1'2#402 *' /&+2'%4 0/%#4'/#4+0/ 1'2#402 *' 5$342+/) 1'2#402 0.1#2+30/ 1'2#4023 0.1#2+/) 42+/)3 0.1#2+/) #4'3 #/& +.'3 '34+/) (02 5-- !#-5'3 0)+%#- 1'2#4023 . . .

(21)

(22) .

(23)

(24) . . . #(4'2 3024",'8 . . #(4'2 2'1024 .

(25). #6) . . . . $'(02' 2'1024 . . $'(02' 3024",'8 . . $0440. .#2)+/ . . %05/4 . . " . . " . 5.

(26) 6. .

(27). "#0!') . . ",2 . . #+" . . . . . . . . $,,0#. . . &#!"#. . . '$ .

(28). '+"#3 . . '+-10 . . . . )#$0 *!.%'+ . . )#+%0& . . *!3 . . *"4 .

(29). . . *'+ . . +##" . . -!%# . . . . -.'+0 . . . . . . .

(30). . . . . . . . . /#-!.!0,. . . /#0 . . /('- . .

(31) *')+ . . .

(32). +'( %)!#& . . +'+$ . . .#+" . . . .

(33) . .

(34) . . /%($ ()+% &+ (')+ " +* $ * & '$,%&* - $'(% &+ (')+ - $'(% &+ (')+ - $'(% &+ (')+ .

(35)

(36)

(37) . /%($ ')% ++ ) .

(38) . /%($ ,)"* ) ) ++,* . . 7.

(39) 8.

(40)

(41) This manual, Creating Reports With RPT Report Writer, tells you how to develop an application report using Report Writer and gives complete syntax descriptions and examples of Report Writer statements. This document is written for application developers with intermediate knowledge of their RDBMS SQL and basic programming skills. This manual uses the following syntax conventions to describe ACCELL/SQL statements and functions:. boldface. Boldface words are command keywords or literal strings that you must type exactly as shown.. italic words. Italic words indicate words, variables, numbers, or expressions that you must provide. Examples are table names, column names, and constants.. ITALIC. UPPERCASE ITALIC words are configuration variables.. UPPERCASE. UPPERCASE words in examples are keywords.. Keywords are not case sensitive: you can type either uppercase or lowercase letters. UPPERCASE words also refer to data types, such as DATE and TEXT.. (). Parentheses enclosing an item in the syntax indicate that several elements make up a single item. If the parentheses are in boldface type, they are part of the syntax and must be included.. []. Square brackets indicate that the enclosed word or item is optional and may be left out. If the brackets are in boldface type, they are part of the syntax and must be included. 9.

(42) ||. Vertical bars enclosing a stacked list of options mean that you can choose one of the listed options, but only one of them.. .... Ellipsis points indicate that you can repeat the immediately preceding item any number of times, as needed.. “Title”. Chapter and section titles are enclosed by a pair of quotation marks.. ”variable”. The name of a developer-defined variable can be enclosed by a pair of quotation marks. Variable names are case sensitive: uppercase and lowercase letters are not equivalent. For example, ”company_address” and ”Company_Address” are two different variables.. This manual contains specially marked paragraphs containing noteworthy information: Tip A tip contains a suggested action or other helpful information. Dependencies Intersecting circles indicate a feature or statement that is dependent upon a particular user interface, operating system, or RDBMS. . Warning A warning cautions against an action that could cause data loss or damage to the database. Additional Help Additional Help tells you where to find more information about the described topics. ACCELL/SQL ACCELL/SQL notes provide information about using Report Writer with the ACCELL/SQL application development tool.. 10.

(43) Report Writer Basics. 11.

(44) . . This chapter gives an overview of the RPT and the basic information that is necessary to develop a script and generate a report. Chapter topics include the following: What is Report Writer? Report writer components How Report Writer processes a report script The Report Writer utilities Configuring a report Dependencies The SQL script examples used in this chapter and other chapters in this manual show only the portable ANSI statements and syntax. The exact SQL statement that you use depends on your RDBMS. When using Report Writer with ACCELL/SQL , see ACCELL/SQL: RDBMS Integration for special Report Writer/RPT information.. 12. Report Writer Basics.

(45) What is Report Writer? is a nonprocedural report processor that uses an English-based language to format and generate application reports. The RPT Report Writer lets you quickly and easily design and create formats for multiple-level, tabular reports. Using a RDBMS or ASCII file and a report script, the Report Writer can easily generate a custom report. Detailed feedback is given by the Report Writer on any syntax errors in the report scripts. lets you define the placement and format of columns, headings, footer information, column titles, and pagination in your database reports—even create user-defined functions for manipulating report information. Report Writer is run with one of two utilities, irs (for integrated reports) and RPT (for data formatting only.) ACCELL/SQL The irs utility is not available with ACCELL/SQL for MS Windows. Input for your report can be derived from a number of sources. These sources include host language programs, ACCELL /SQL 4GL, SQL queries, and ASCII files.

(46)

(47)

(48) . . .

(49) . . . . Report Writer Basics. . . 13.

(50) Using the full functionality of , you can assemble SQL and report scripts in one integrated report script to generate report input data and format the data. Other features include parameter acceptance, validation, and substitution output direction multiple report formats in a single script. 14. Report Writer Basics.

(51) Report Writer Components Before you develop your own reports, you must understand the basic structure of a

(52)

(53) report and the terms that describe the parts of a report. To generate an application report using

(54)

(55) , two report components are necessary, report input and a report script..

(56)

(57).

(58)

(59).

(60)

(61) .

(62). Report Input. The report input is data that is supplied to

(63)

(64) in identically formatted lines. The data can be generated by SQL, a host language user program, ACCELL/SQL 4GL, a text editor, or other utility. You can pass the input data to

(65)

(66) as standard input from the generating utility. You can also build the SQL query into an integrated report script when you use the irs Report Writer processing utility. Each report input line must contain data columns delimited by a separator character. The input line must end with a newline character. The default separator character is the pipe symbol ( | ).. Example. The following example shows five lines from a report input file. 1000 2000 1100 2100. |Smith |Jones |Whittaker |Reilly. | | | |. 50|salesrep 10|clerk 20|salesrep 30|salesrep. data columns. Report Writer Basics. | | | |. 2100|1500.00| 1000.00 1300| 900.00| 0.00 2400|2500.00| 500.00 2400|2500.00| 1500.00. separator characters. newline (non-printing). 15.

(67) Report Script. Report Components. The report script contains the report language statements. The basics of the report script are its components and syntax. In this manual, descriptions of report scripts assume that you are processing your report with the irs utility. The irs utility is described in more detail on page 29.. A report script is comprised four types of script sections: input. optional. environment. optional. SQL. optional. report format. required. Only one report format section is required in a report script. When you include multiple report format sections in a report script, you provide your end users with a choice of different report formats while using the same input data. An illustration of report script components is supplied on page 17. A complete description of each script section starts on page 45 .. Report Syntax. The following is the only required language in a report script: the script section identifier RPT_SECTION (only required when using the irs utility for processing) the input format section statement an event section the end format section statement The input statement must be the first command in the report format section and the end statement must be last. The remaining statements and event sections in the report format section can be in any order. An illustration of the report script syntax is supplied on page 18.. 16. Report Writer Basics.

(68)

(69)

(70)

(71) . INPUT_SECTION PROMPT ’What is the bottom of the salary range?’ [NUMERIC] PROMPT ’What is the top of the salary range?’ [NUMERIC] ENVIRONMENT_SECTION PIPE=’tee current_mo | lpr ’. script section. report Script. SQL_SECTION ... SQL statements .... report format section. report format section. Report Writer Basics. RPT_SECTION ’employee report’ input emp_name [string 10], emp_job [string 10], emp_Salary [amount 4], emp_Commission [amount 4] before report print ’Employee Report: Salaries from $1 to $2’ detail print emp_name, emp_job, emp_Salary end RPT_SECTION ’Commission report’ input emp_name [string 10], emp_job [string 10], emp_Salary [amount 4], emp_Commission [amount 4] sort emp_job before report print ’Employee Commission Summary’ centered before emp_job skip 1 print emp_job detail print emp_name column 8, emp_Commission column 24 after report skip print ’Average Commission for all Employees:’ column 20, avg(emp_Commission) end. 17.

(72)

(73)

(74)

(75)

(76) . script section statements.

(77)

(78) . script section statements.

(79) SQL statements.

(80)

(81) . arguments

(82)

(83) event section identifier format section statement .

(84)

(85) . arguments

(86)

(87) event section identifier format section statements . 18. . Report Writer Basics.

(88) How Report Writer Processes a Report Script This section tells how processes the input data and the report format section statements. Processing control is determined by report events. When a report event occurs, the statements in the corresponding script event section are executed. performs the following tasks when formatting a report. The tasks are done by the two utilities, irs and RPT. The irs utility tasks: Process the environment script section Process the input section to get parameter values Replace all of the parameter placeholders in the script with the corresponding parameter value Use your RDBMS SQL to process the SQL script section query and pass the input data to the RPT utility The RPT utility tasks: Start processing the applicable report format section Make sure input data matches the format and data types, and give a name to the data columns Sorts the input data lines per the sort statement Process the remaining section statements The remainder of this section describes report events and event sections, and how the events sections are processed.. Report Writer Basics. 19.

(89) Report Events. A report event is the occurrence of a change in the input data or pagination. These are the events that Report Writer uses: The start of each report page The end of each report page The first data line is read in The input value of a sorted column changes An input line is read in The last data line is read in When a report event occurs, RPT process all the format statements that are in the corresponding event section. Using event sections, you can print column labels to introduce a group of data items, do totaling calculations, print report headings, and print a report footer with totals at the end of the report.. 20. Report Writer Basics.

(90) Examples of Report Events . SALES REPORT STATE: CA CITY: Los Angeles CUSTOMERS. . ABC Company, Inc..

(91) . Mom’s Auto Repair, Inc.. ORDER # ––––––––– 98475 423485. AMOUNT ––––––––––– $54,321.99 $238.67 ––––––––––– $54,560.66. 348661 78563222 123456789. $98,342.59 $23,586.89 $55,676.50 ––––––––––– $177,605.98. SALES: Los Angeles $232,166.64 ––––––––––––––––––––––––––––––––––––––––––––––––––––––– CITY: San Jose CUSTOMERS ORDER # AMOUNT ––––––––– ––––––––––– Fixit–Yourself Company 385576 $12,887.50 18496333 $239.00 ––––––––––– $13,126.50.

(92) . SALES: NY $178,385.43 –––––––––––––––––––––––––––––––––––––––––––––––––––––––––––– TOTAL SALES THIS REPORT: $1,003,347.73 AVERAGE CUSTOMER ORDER : $13,030.49 ============================================================. There are three primary event types: Report pagination Sorting Data input. Report Writer Basics. 21.

(93) Report Pagination Events and Event Sections Report pagination events occur at the start of each report page at the end of each report page The processing that is done when a report pagination event occurs is specified in these event sections: header footer header. The header event section can be used to print a running title, date, time, or page number at the top of each report page. The text processing specified by header prints on the first page if before report command is not used.. footer. The footer event can print information at the bottom of each report page.. Sorting Events and Event Sections Each item you sort by is called a sort key. A sort key can be a database column, or a complex expression composed of columns, constants, variables, arithmetic operators, and functions. Sorting serves two functions: To arrange items with different sort keys into ascending or descending sequence Put items into groups by the same sort key, for example, all customers in a specific city When a list of data lines are put into groups by their sort keys, an event occurs each time the sorted column value changes. The processing that is done when a sorting event occurs is specified in these event sections: before sort_key after sort_key 22. Report Writer Basics.

(94) In the Sales Report example, shown on page 21, the sales input is sorted by state, by city within state, and by customer within city. The expression named state is the highest level sort expression, because state is the first expression in the sort command: sort state, city, customer The expression named customer is the lowest level sort expression, because customer is the last expression name in the sort command. When expressions are sorted hierarchically, the sort expression that changes least is the highest level sort expression. More complex expressions composed of several columns, operators, and functions can be given a name in the sort command. Then the expression can be referred to by this name in the report script. Before and After sort_key Event Sections. A sort key event occurs when the specified sorted column value changes. One before sort_key and after sort_key event section can be associated with each sort key in the sort command. As a sort key value changes, the before sort_key and after sort_key sections for that sort key are processed, as well as the sections for sort expressions lower in the sort hierarchy. The after sort_key event section can be used to print a cumulative total of the sort key values in that sort group. A before sort_key event section has access to the data in the first input line of the new sort group about to be processed. Similarly, an after sort_key section has access to the information in the sort group just processed. At an event, before sort_key sections are processed according to the sort hierarchy, starting with the section associated with the highest level sort expression. The after sort_key sections are processed in reverse sequence.. Example. The data in this example illustrates sorted data and identifies changes in the sort key and sort groups. The input data lines are comprised of the columns state, city, company, o_num, o_amount. The data is sorted with this sort statement: sort state, city, company. Report Writer Basics. 23.

(95) Sort Key Value Changes. sort key value changes. CA|Los Angeles CA|Los Angeles CA|Los Angeles CA|Los Angeles CA|Los Angeles CA|San Jose CA|San Jose GA|Atlanta. NY|Albany NY|Albany NY|Albany. |ABC Company, Inc. |ABC Company, Inc. |Mom’s Auto Repair, Inc |Mom’s Auto Repair, Inc |Mom’s Auto Repair, Inc. |Fixit–Yourself Company |Fixit–Yourself Company |Top Flight Repair, Inc . . . |Alice’s Hardware |Do Right Supply Company |Do Right Supply Company. | 98475| | 423485| | 348661| | 78563222| |123456789| | 385576| | 18496333| | 3459234|. 54321.99 238.67 98342.59 23586.89 55676.50 12887.50 239.00 2390.19. | 3451250| | 34589011| |459384773|. 1298.23 3456.90 34100.29. | 98475| | 423485| | 348661| | 78563222| |123456789| | 385576| | 18496333| | 3459234|. 54321.99 238.67 98342.59 23586.89 55676.50 12887.50 239.00 2390.19. | 3451250| | 34589011| |459384773|. 1298.23 3456.90 34100.29. Sort Key Value Groups. state city. CA|Los Angeles CA|Los Angeles CA|Los Angeles CA|Los Angeles CA|Los Angeles CA|San Jose CA|San Jose GA|Atlanta. company NY|Albany NY|Albany NY|Albany. |ABC Company, Inc. |ABC Company, Inc. |Mom’s Auto Repair, Inc |Mom’s Auto Repair, Inc |Mom’s Auto Repair, Inc. |Fixit–Yourself Company |Fixit–Yourself Company |Top Flight Repair, Inc . . . |Alice’s Hardware |Do Right Supply Company |Do Right Supply Company. Input Data Events and Event Sections In addition to an event that occurs when a sort key value changes in the input data, events also occur as the first data line is read in as each line is read in after the last data line is read in The processing that is done when an input data event occurs is given in these event sections:. 24. before report. As the first data line is read in. detail. As each line is read in. after report. After the last data line is read in Report Writer Basics.

(96) The before report and after report event sections give commands to control processing at the start and the end of the report. These are the highest level of events. Before Report Event Section. The before report section is processed first before other control breaks are processed. Then, existing before sort_key sections are processed before detail information is considered for output. You can use the before report event section to print a descriptive heading identifying the report and initialize variables before the first input line is read.. detail Event Section. A detail control break occurs each time a new line is read from the input file—this is the lowest level break. In the Sales Report example, the detail command is used to print the order number and order amount.. After Report Event Sections. The after report event section is processed at the end of the report after all input data lines and after sort_key sections are processed. The after report event section can be used to print a report summary or grand total after the last input line is read.. Report Writer Basics. 25.

(97) Processing Event Sections. RPT processes the report event sections in this sequence:

(98) sort_key_n sort_key_1

(99)

(100) sort_key_1

(101) sort_key_n

(102)

(103). header and footer control breaks are not included, because when they occur depends on the page dimensions. Example. This example shows how the event sections in the Sales Report are processed as the input data is read in.. before report (or header) before state before city before customer after customer before customer after customer after city before city before customer after customer after city after state before state before city before customer after customer before customer after customer after city after state after report. 26. CA|Los Angeles. |ABC Company, Inc.. |. 98475|. CA|Los Angeles. |ABC Company, Inc.. |. 423485|. 238.67. CA|Los Angeles. |Mom’s Auto Repair, Inc. |. 348661|. 98342.59. CA|Los Angeles. |Mom’s Auto Repair, Inc. | 78563222|. 23586.89. CA|Los Angeles. |Mom’s Auto Repair, Inc. |123456789|. 55676.50. CA|San Jose. |Fixit–Yourself Company. |. 12887.50. CA|San Jose. |Fixit–Yourself Company. | 18496333|. GA|Atlanta. |Top Flight Repair, Inc. |. 3459234|. 2390.19. |. 3451250|. 1298.23. 385576|. 54321.99. 239.00. . . . NY|Albany. |Alice’s Hardware. NY|Albany. |Do Right Supply Company | 34589011|. 3456.90. NY|Albany. |Do Right Supply Company |459384773|. 34100.29. Report Writer Basics.

(104) Example. This second example shows a report and the report format script used to format the data. This example shows how to use event sections to format your report and control data processing:.

(105) . SALES REPORT STATE: CA CITY: Los Angeles CUSTOMERS. sort_key. ABC Company, Inc..

(106). Mom’s Auto Repair, Inc.. sort_key. ORDER # ––––––––– 98475 423485. AMOUNT ––––––––––– $54,321.99 $238.67 ––––––––––– $54,560.66. 348661 78563222 123456789. $98,342.59 $23,586.89 $55,676.50 ––––––––––– $177,605.98. SALES: Los Angeles $232,166.64 ––––––––––––––––––––––––––––––––––––––––––––––––––––––– CITY: San Jose CUSTOMERS ORDER # AMOUNT ––––––––– ––––––––––– Fixit–Yourself Company 385576 $12,887.50 18496333 $239.00 ––––––––––– $13,126.50 SALES: San Jose $13,126.50 ––––––––––––––––––––––––––––––––––––––––––––––––––––––– SALES: CA $245,293.14 ––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––. sort_key. . SALES: NY $178,385.43 –––––––––––––––––––––––––––––––––––––––––––––––––––––––––––– TOTAL SALES THIS REPORT: $1,003,347.73 AVERAGE CUSTOMER ORDER : $13,030.49 ============================================================. Report Writer Basics. 27.

(107) The Sales Report Format Section input state [string 2], city [string 11], customer [string 14], orderno [numeric], price [amount] sort state, city, customer before report print ’SALES REPORT’ centered before state print ’STATE:’, state before city print ’CITY:’ in column 6, city before customer need 4 print ’CUSTOMERS’ col 10, ’ORDER #’ col 38, ’AMOUNT’ col 54 print 9[–] col 36, 11[–] col 51 print customer in col 10 no newline detail print orderno col 36, price col 52 using ’$$$,$$&.&&’ after customer need 3 print 11[–] in column 51 print total(price) column 51 using ’$$$$,$$&.&&’ skip 1 after city need 3 print ’SALES:’ in column 6, city, total(price) column 49 using ’$$,$$$,$$&.&&’ print 56[–] column 6 skip 1 after state need 2 print ’SALES:’, state, total(price) column 49 using ’$$,$$$,$$&.&&’ print 61[–] skip 1 after report need 4 print ’TOTAL SALES THIS REPORT:’, total(price) col 49 using ’$$,$$$,$$&.&&’ print ’AVERAGE CUSTOMER ORDER :’, avg(price) col 52 using ’$$$,$$&.&&’ print 61[–] print 61[–] end. 28. Report Writer Basics.

(108) The Report Writer Utilities This section describes the two utilities that are used to process a script, the irs utility and the RPT utility. The most comprehensive method of running a report is with the irs utility. This utility’s name stands for integrated report script because the utility lets you integrate parameter prompting, data validation, SQL query, and report formatting in a single script. The RPT utility is used for data formatting only. RPT processes the report format section of a report script. All input data must be supplied by a different method and sent to RPT. It cannot prompt for parameters or define the report environment.. The irs Utility. The syntax for running the irs utility from the operating system command line is shown below: irs options report_script When you use the irs utility, a prompt displays to verify the processing of the report script. If you have multiple report format sections in the script, you are also prompted for the report format that you want to use. For a complete description of the irs utility, please see the irs reference page in Configuration Variable and Utility Reference.. irs Arguments. The irs utility uses these arguments: report_script. Report Writer Basics. The report script file that contains the report processor statements to do the report formatting.. 29.

(109) irs Options. You can use the irs options for three purposes: To override specifications in the environment section To supply values for the input section To automatically select the report format script section that will be used to format the report If parameter values are given in the command line, prompts and messages in the input section are skipped. ACCELL/SQL You can assemble and invoke options through an ACCELL/SQL application.. The following table is a brief summary of the generic command line options that the irs utility recognizes: Option. Description. –d database_name. Use database as the default database. –S database_object. Use database_object as the name of the database object that contains the database tables to use.. –OPROMPTER =prompter. Prompt for and validate parameter values using the executable prompter. –OSELECTOR=selector. Use the executable selector to get the report format section to use. –OSCREEN. Send report output to the standard output device. –OFILE=filename. Send report output to file filename. –OPRINTER. Send report output to printer. –OPIPE =command. Direct report output as specified by the string command. By default, the executables upager and utee are used. upager is like the UNIX more command and utee is equivalent to UNIX tee. table continues on next page. 30. Report Writer Basics.

(110) Option (continued). Description. –OBACKGROUND. Execute the SQL and report format processing in the background. Cannot be used with –OSCREEN.. –OERROR_FILE =filename. Send error messages to file filename. –ODEBUG_MODE. Process the report in debug mode. –On=parameter_value. Substitute the value parameter_value for all occurrences of the parameter placeholder $n in the report script. –OREPORT=rpt_sec_name. Use the script in the specified RPT_SECTION section to format the. report.. Example. The first irs example directs runtime error messages to the file /tmp/err.out, sends the report output to the user’s screen, and specifies two parameter values. The value 87/01/01 replaces all occurrences of the placeholder $1 in the report script and 87/12/31 replaces placeholder $2. irs –OERROR_FILE=/tmp/err.out –OSCREEN –O1=87/01/01 –O2=87/12/31. In the second example, the report output is directed to the UNIX tee command which sends the output to the file /tmp/out and sends it on to the line printer. The string “select * from history;” replaces all occurrences of $1 in the report script. The report script file format.irs will be used. irs –OPIPE=’tee /tmp/out|lpr’ –O1=’select * from history;’ format.irs. Report Writer Basics. 31.

(111) The RPT Utility. The report formatting utility is RPT. The RPT utility is called by irs to format the report script’s report format section. You can also run RPT independently when the report input, parameter values, and report output direction is managed by a different method. This is the best utility to use when you have one report format section and you are not using parameters. All RPT report messages and output are directed to the screen. The RPT syntax is shown below: RPT options report_script. input file . If no input file is specified either by name or using the dash, RPT examines the report script file and prints a Table Usage Report. See page 35 for information about the Table Usage Report. You can also provide input data to a report script by piping the results of an executable to RPT at runtime. Additional Help For complete syntax information about the RPT utility, see Configuration Variable and Utility Reference.. RPT Arguments. RPT uses these arguments:. report_script. The script file that contains the report processor statements to do the report formatting.. input file. The name of an ASCII file that contains the data to be formatted.. –. Input data is supplied from a pipe.. Dependencies MS Windows does not support pipes. To pass input data to a report script that is running on MS Windows, you must direct the input data to a file and then use that input file name in the RPT command line. See the example on page 34 for this type of command syntax.. 32. Report Writer Basics.

(112) Dependencies If you are piping SQL output to RPT, you may have to suppress your RDBMS’s SQL query result column heading. When using Unify DataServer, you must the SQL statement lines 0 to. suppress the column headings: lines 0; select Manufacturer_ID, Name, Address from manf where Manufacturer_ID > 100;. When using ORACLE SQL*Plus, you must use the Unify filter orcfltr to prepare the SQL output for RPT:

(113) usernamepassword sqlscript rptscript For more information about the orcfltr utility, see ACCELL/SQL: Configuration Variable and Utility Reference.. RPT Options. Report Writer Basics. RPT recognizes several command line options, including these:. –Oreport. Prints the Table Usage Report in addition to the report output.. –S db_object. Indicates the name of the default database object that owns or contain the application file to be used.. –r. Remove the script file when the report is complete.. 33.

(114) Example. This example shows a report script, SQL script, and the RPT command to process them when using the Unify DataServer RDBMS: The RPT report script rscript is shown below: input emp_name [string 10], emp_job [string 10], emp_Salary [amount 4], emp_Commission [amount 4] sort emp_job before report print ’Employee Commission Summary’ centered before emp_job skip 1 print emp_job detail print emp_name column 8, emp_Commission column 24 after report skip print ’Average Commission for all Employees:’ column 20, avg(emp_Commission) end. The SQL script sscript is shown below: ... select Manufacturer_ID, Name, Address from manf where Manufacturer_ID > 100 .... In UNIX, to process the SQL query sscript and direct the output to the RPT report script rscript for formatting, you use this command line: SQL sscript | RPT rscript –. To process the script separately, you can use these commands: SQL sscript > outputfile RPT rscript outputfile. 34. Report Writer Basics.

(115) Configuring a Report This section gives information about how you can configure a report for custom format, data display, and your computer system. Configuration is done by setting the configuration variables used by . You can display the current value of many configuration variable by generating a Table Usage Report.. The Table Usage Report. This section gives information about the Table Usage Report, how to run the table report, and how to change the size of a configuration table. There are 14 configuration variables used by to control the maximum number of occurrences of a report element in a report script. The variable’s setting determines the size of the table that stores the elements during the report processing. Each configuration table size can be resized for scripts that use more of some elements and less of others. On computer hardware with limited data address space, this feature lets you decrease the table sizes and make an allowance for larger database designs. The only constraint on the size of each table is the limitation of your swap area in memory.. Generating a Table Usage Report You can examine the configuration table’s maximum and current size and setting for a given report script by using this command: RPT script_name. Notice that there is no input file in the RPT syntax.. Report Writer Basics. 35.

(116) The Table Usage Report The following is an example of a Table Usage Report. In the Table Usage Report, the terms command and statement are used interchangeably. RPT – Report Processor Copyright Unify Corporation 1987–91. No syntax errors were found in the report script. RPT TABLE USAGE INFORMATION Table Name Expression Nodes Variables Constant Commands Print Statements Print Items Sort Items Input Items Command Groups Set Statements If Statements Aggregates Function Calls Arguments. Used. Maximum. n n n n n n n n n n n n n n. 400 150 250 256 125 256 15 100 25 100 50 50 50 100. Table Elements and Associated Configuration Variables The following data table identifies the report script elements that are controlled by a configuration table, the name of the configuration variable that is related to the table, and the default value for the table. A full description of the table configuration variables starts on page 41 of this manual. Report Elements and Configuration Variables. .

(117)

(118)

(119) .

(120)

(121) . . RPTNODECNT. .

(122)

(123) . RPTFLDCNT. .

(124) . RPTCONCNT. .

(125) . RPTNCOM. table continues on next page. 36. Report Writer Basics.

(126) . (continued).

(127) .

(128) . . RPTNPRINT. . . RPTNPITM. . . RPTNSITM. . . RPTNITM. . . RPTNCGRP. . . RPTNSETCL. . . RPTNIF. . . RPTNAG. . . RPTNCALL. . . RPTNARGS. .

(129) . RPTPBUFSIZ. . . RPTINBUFSIZ. . Changing a Table Size To reset a table size, set the configuration variable that corresponds to the table to be changed. After you set the necessary configuration variables, generate the Usage Table report again to examine the settings. Changes made to a configuration table display at the top of the Usage Report. RPT: RPTNCOM set to 300 RPT: RPTNITM set to 40 RPT – Report Processor Copyright Unify Corporation 1988. No syntax errors were found in the report script. RPT TABLE USAGE INFORMATION Table Name Expression Nodes Variable Constant Commands Print Statements Print Items Sort Items Input Items .... Report Writer Basics. Used. Maximum. n n n n n n n n. 400 150 250 300 125 256 15 40. 37.

(130) Configuration Variables. uses three classes of configuration variables: general display formatting element table Additional Help About. See. Setting configuration variables. Managing an Application or Managing a Database. A general or display formatting configuration variable. Configuration Variable and Utility Reference. General Configuration Variables Report Writer uses the following configuration variables to format and control the processing of a report: PAGELN PAGER. SEPARATOR. The page length of the reports, for example, 66 lines. The paging utility to be used when SCREEN is specified as the output destination in the irs ENVIRONMENT_SECTION. Column separator character for the report script input data. The default separator is the pipe symbol (|). You should avoid using any of the following reserved characters as the separator character: ^!#@*.() Warning Be careful if you do use a reserved character as the separator character. For example, if you set the separator character to an asterisk (*), you must set the ACCELL/SQL null character configuration variable, NULLCH, to a different character because the default null character is also the asterisk.. WIDTH. 38. Width of a report page in number of characters. The default value is 80 characters. Report Writer Basics.

(131) Display Format Configuration Variables ACCELL/SQL The display format described in this section are available when you are using ACCELL/SQL . For additional information about display formats, see “Display Formats” in ACCELL/SQL: Managing an Application. uses these configuration variables to format input data for the report. AMTFMT. Default format template to be used to display AMOUNT data.. A dollar sign ($) in the AMTFMT variable shows the position of the currency symbol that is given by CURRSYM . When using AMTFMT to position the currency symbol on the right, use only one dollar sign, regardless of the number of characters in the currency symbol. A comma (,) in the AMTFMT variable shows the position of the triad separator that is given by TRIADSEP. A period (.) in the AMTFMT variable shows the position of the radix separator that is given by RADIXSEP. For example, if AMTFMT is set to ”###,##&.&&$” , and CURRSYM is set to ”XXX”, then the amount 123456.78 displays as 123,456.78XXX . AMTPREC. Display precision (number of digits following the radix separator) for AMOUNT data. If AMTPREC is set to zero (”0”), neither the radix separator nor the digits following the radix separator are displayed.. CURRSYM. Currency symbol that is used when displaying AMOUNT data. This configuration variable is used only when a format template is used. A format template can be given in these ways: in a using clause of the print statement in the AMTFMT configuration variable, set either at the operating system command level, in a configuration file, or in the langcap file. DATEFMT Report Writer Basics. Default format in which to accept and display dates. 39.

(132) FLTFMT. Default format template to be used to display FLOAT data.. NULLCH. Default null display character for all data types. You can override this variable by specifying a default display character fore each data type.. NUMFMT RADIXSEP. Default format template to be used to display NUMERIC data. Radix separator character to be used when displaying AMOUNT data. This configuration variable is used only when a format template is used. A format template can be given in these ways: in a using clause of the print statement in the AMTFMT configuration variable, set either at the operating system command level, in a configuration file, or in the langcap file When the AMOUNT data is displayed, the radix separator given by RADIXSEP displays in the position indicated by the period (.) in the format template.. TIMEFMT. Default format in which to accept and display TIME values.. TRIADSEP. Triad separator character that is used when displaying AMOUNT data. The TRIADSEP configuration variable is used only when a format template is used. A format template can be specified in these ways: in a using clause of the print statement in the AMTFMT configuration variable, set either at the operating system command level, in a configuration file, or in the langcap file. 40. Report Writer Basics.

(133) Element Table Configuration Variables The following configuration variables are used to set the maximum value for the Report Writer element tables. RPTNODECNT. The number of expression nodes permitted in a report script. Each expression in a script uses one expression node. See the “Report Writer Expressions” chapter for information on expressions.. RPTFLDCNT. The number of different variables, including input variables that are permitted in a report script.. RPTCONCNT. The number of constants permitted in a report script. The constant table includes numeric, float, amount, date, ldate, time, string, and text constants.. RPTNCOM. RPTNPRINT RPTNPITM. The number of format section statements permitted in a report script. Format section statements include sort, after, detail, print, and if. The number of print statements permitted in a report script. The number of print items permitted in a report script. Each comma-divided expression in a print statement is one print item. For example, this statement has three print items: print emp_name, emp_job, emp_Salary. RPTNSITM. The number of sort items permitted in a report script. Each comma-divided expression in a sort statement is one sort item. The RPTNSITM configuration variable also shows the number of presorted input columns sent to the report script. This variable must be set when the sorted option is used with the input statement. For example, this statement has three sort items: sort v_number, po_number, Line_Number. RPTNITM. RPTNCGRP Report Writer Basics. The number of columns permitted in the input section of an script. The number of event sections permitted in a report script. 41.

(134) RPTNSETCL RPTNIF RPTNAG. 42. The number of set statements permitted in a report script. The number of if statements permitted in a report script. The number of aggregate functions permitted in a report script. Aggregate functions are min, max, avg, count, and total.. RPTNCALL. The number of local function calls permitted in a report script. A local function call in a report script can be one of the built-in functions or one of your own.. RPTNARGS. The total number of function arguments permitted in a report script. You can pass arguments, or parameters, to local functions.. RPTPBUFSIZ. The size in bytes of the sort key buffer. This buffer must be large enough to accommodate the total length of all the sort items’ column lengths.. RPTINBUFSZ. The size of the input buffer. This buffer must be large enough to accommodate the total length of all the input items’ column lengths.. Report Writer Basics.

(135) Developing a Report Script. 43.

(136) . . This chapter describes the capabilities and features of RPT Report Writer and how to write a report script. To develop a report script, you will learn about these topics: Report script sections Report format sections How to customize a report script Examples of report scripts are supplied in “Appendix B: Report Examples” that starts on page 187 of this manual. Dependencies The SQL script examples used in this chapter and other chapters in this manual show only the portable ANSI statements and syntax. The exact SQL statement that you use depends on your RDBMS. When using Report Writer with ACCELL/SQL , see ACCELL/SQL: RDBMS Integration for database dependencies.. 44. Developing a Report Script.

(137) Building the Report Script Sections A report script is made up of four report script sections: input environment SQL. report format The input, environment, and SQL sections are only used in an irs report script. The report script sections are shown in the following script: INPUT_SECTION PROMPT ’What is the bottom of the salary range?’ [NUMERIC] PROMPT ’What is the top of the salary range?’ [NUMERIC]. report script section. ENVIRONMENT_SECTION PIPE=’tee current_mo | lpr ’ SQL_SECTION ... select Name, Job, Salary, Commission from emp where Salary > $1 and Salary < $2 ... RPT_SECTION ’employee report’ input emp_name [string 10], emp_job [string 10], emp_Salary [amount 4], emp_Commission [amount 4] before report print ’Employee Report: Salaries from $1 to $2’ detail print emp_name, emp_job, emp_Salary end. Tip You can add text comments to your script to document the report. Comments embedded in the SQL section must conform to the RDBMS SQL utility’s syntax. Comments in the report format section must use the convention “/* comment */” and cannot be more than one line. Comments put in other sections of the script are indicated by “––” at the start of the line or delimited by “/* comment */”.. Developing a Report Script. 45.

(138) Input Section. The irs input script section contains the user prompts and validation specifications for parameter value retrieval. By default, Report Writer uses scrolling to display the user prompts on the screen sequentially. The input section must start with the INPUT_SECTION script section identifier. INPUT_SECTION PROMPT ’What is the bottom of the salary range?’ [NUMERIC] PROMPT ’What is the top of the salary range?’ [NUMERIC] SQL_SECTION

(139)

(140)

(141) ... select Name, Job, Salary, Commission from emp . where Salary > $1 and Salary < $2 ... RPT_SECTION ’employee report’ input

(142)

(143) emp_name [string 10], emp_job [string 10],

(144) emp_Salary [amount 4], emp_Commission [amount 4] before report print ’Employee Report: Salaries from $1 to $2’ detail print emp_name, emp_job, emp_Salary, emp_Commission end. Valid script section statements for the input section are: PROMPT MESSAGE. The PROMPT statement controls the display of the parameter prompt and parameter value validation. PROMPT text can be enclosed by single (’) or double (”) quotes. The MESSAGE statement defines a text string to display before its corresponding PROMPT statement. A MESSAGE statement is matched with the PROMPT statement that it precedes. MESSAGE statements after the last PROMPT statement are processed after all parameter values have be entered. Tip If you want to display parameter prompt text in a format that is different from the default and you want to control input validation, you must use your own custom executable.. 46. Developing a Report Script.

(145) Additional Help About. Example. See. The PROMPT and MESSAGE statements. The reference pages in the “Language and Function Reference” chapter that starts on page 115 of this manual. Using a custom parameter prompter. “Adding a Custom Parameter Prompter” that starts on page 66 of this manual. This sample report script prompts the user for a low and high salary value to use in data selection and the report heading. The output from the input section is shown after the script. Input INPUT_SECTION MESSAGE ”Please enter values for the following range delimiters:” PROMPT ”What is the bottom of the salary range?” [NUMERIC] PROMPT ”What is the top of the salary range?” no newline [NUMERIC] MESSAGE ”The report is now processing. Please wait.” SQL_SECTION ... select Name, Job, Salary, Commission from emp where Salary > $1 and Salary < $2 ... RPT_SECTION ’employee report’ input emp_name [string 10], emp_job [string 10], emp_Salary [amount 4], emp_Commission [amount 4] before report print ’Employee Report: Salaries from $1 to $2’ detail print emp_name, emp_job, emp_Salary, emp_Commission end. Output Please enter values for the following range delimiters: What is the bottom of the salary range? V What is the top of the salary range? V The report is now processing. Please wait.. Developing a Report Script. 47.

(146) Environment Section. The irs environment report script section defines the environment in which the report script operates. These environment features include the name of the database and schema, the destination of the report output, and a debugging mode. The environment section must start with the script section identifier ENVIRONMENT_SECTION . If you do not include an environment section, these default procedures occur: the report output is sent to the standard output device the default database and schema is used processing is done in the foreground Tip All environment section specifications can be overridden or set at runtime by using the irs options..

(147)

(148)

(149)

(150)

(151)

(152) . . . ! . database. database. . " .

(153) schema. schema.

(154) filename. ! filename.

(155) filename. filename. string. ! string. . . table continued on next page 48. Developing a Report Script.

(156)

(157)

(158) (continued).

(159) . prompter.

(160)

(161)

(162)

(163)

(164) prompter

(165)

(166)

(167)

(168) . . Default.

(169)

(170)

(171)

(172) . selector.

(173)

(174)

(175)

(176)

(177) selector

(178)

(179)

(180) . Additional Help About. Example. See. A script section statement that is valid in the environment section. The reference page for the statement in the “Language and Function Reference” chapter that starts on page 115 of this manual. Using a custom parameter prompter. “Adding a Custom Parameter Prompter” that starts on page 66 of this manual. Using a custom report format selector. “Adding a Custom Report Format Selector” that starts on page 68 of this manual. Overriding or setting environment section options at runtime. “The irs Utility” on page 29 and the irs reference page in Configuration Variable and Utility Reference. This environment script section specifies that the report is to be processed as a background process, directs the report output to a file and to the line printer, and uses a custom executable to prompt for the parameter values and validate the data types. ENVIRONMENT_SECTION PIPE=’tee current_mo | lpr ’ BACKGROUND PROMPTER=’/usr/bin/xprompter’. Developing a Report Script. 49.

No results found