Understanding the Sample Program for Simple Tabular Reports
This chapter provides an overview of the sample program for simple tabular reports and discusses how to:
Add graphics.
Share images among reports.
Print bar codes.
Understanding the Sample Program for Simple Tabular Reports
The following sample program produces a simple tabular report, similar to the one in the chapter “Selecting Data from the Database”:
Program ex12a.sqr begin-setup declare-layout default end-declare end-setup begin-program do main end-program begin-procedure main begin-select name (,1,30) city (,+1,16) state (,+1,5) tot (,+1,11) edit 99999999.99 next-listing no-advance need=1 let #grand_total = #grand_total + &tot from customers end-select print '-' (,55,11) fill print 'Grand Total' (+1,40) print #grand_total (,55,11) edit 99999999.99 end-procedure ! main begin-heading 5 print $current-date (1,1) Edit 'DD-MON-YYYY' page-number (1,60) 'Page ' print 'Name' (3,1) print 'City' (,32) print 'State' (,49) print 'Total' (,61) print '-' (4,1,65) fill end-heading
The SETUP section contains a DECLARE-LAYOUT command that specifies the default layout without defining any options. The purpose of specifying the default layout is to use its margin settings, which are defined as 1/2 inch. Without DECLARE-LAYOUT, the report would have no margins.
Note the PRINT command with the FILL option. This command produces dashed lines, which is a simple way to draw lines for a report that is printed on a line printer. On a graphical printer, however, you can draw solid lines.
The following is the output for program ex12a.sqr.
06-JUN-04 Page 1 Name City State Total --------------------------------------------------------------- Gregory Stonehaven Everretsville OH 39.00 John Conway New York NY 42.00 Eliot Richards Queens NY 30.00 Isaiah J Schwartz and Company Zanesville OH 33.00 Harold Alexander Fink Davenport IN 36.00 Harriet Bailey Mamaroneck NY 21.00 Clair Butterfield Teaneck NJ 24.00 Quentin Fields Cleveland OH 27.00 Jerry's Junkyard Specialties Frogline NH 12.00 Kate's Out of Date Dress Shop New York NY 15.00 Sam Johnson Bell Harbor MI 18.00 Joe Smith and Company Big Falls NM 3.00 Corks and Bottles, Inc. New York NY 6.00 Harry's Landmark Diner Miningville IN 9.00 --------- Grand Total 315.00
See Selecting Data from the Database, Adding Graphics.
Adding Graphics
The following sample program includes graphical features: a logo, solid lines, and a change of font in the heading:
Program ex12b.sqr begin-setup declare-layout default end-declare end-setup begin-program do main end-program begin-procedure main begin-select name (,1,30) city (,+1,16) state (,+1,5) tot (,+1,11) edit 99999999.99 next-listing no-advance need=1 let #grand_total = #grand_total + &tot from customers end-select graphic (,55,12) horz-line 20 print 'Grand Total' (+2,40) print #grand_total (,55,11) Edit 99999999.99 end-procedure ! main begin-heading 11 print $current-date (1,1) page-number (1,60) 'Page ' alter-printer point-size=14 font=4 ! switch font print 'Name' (9,1) bold print 'City' (,32) bold print 'State' (,49) bold print 'Total' (,61) bold alter-printer point-size=12 font=3 ! restore font graphic (9,1,66) horz-line 20 print-image (1,23) type=bmp-file image-size=(21,5) source='acmelogo.bmp' end-heading
The GRAPHIC command draws solid lines with the HORZ-LINE argument. The line is positioned by using a normal SQR position specifier. Note that the third number in the position specifier is the length of the line, which is given in characters. (The actual width of a character cell is determined by the CHAR-WIDTH or MAX-COLUMNS arguments of DECLARE-LAYOUT.)
The HORZ-LINE argument of the GRAPHIC HORZ-LINE command is the thickness of the line, specified in decipoints (an inch has 720 decipoints). For example, the graphic (10,1,66) horz-line 20 command specifies a horizontal line following line 10 in the report, starting with position 1 (the left side of the report) and stretching for 66 character positions (at 10 characters per inch, this is 6.6 inches). The thickness of the line is 20 decipoints, which is 1/36 of an inch (about 0.7 mm).
You can also use the GRAPHIC command to draw vertical lines, boxes, and shaded boxes. See the sqrlaser.sqr program in the SAMPLE (or SAMPLEW) subdirectory for an example.
The ALTER-PRINTER command in ex12b.sqr changes the font for the heading. When used a second time, it restores the normal font for the rest of the report. The FONT option selects a font (typeface) that is supported by the printer. The font is specified by number, but the number is printer-specific. On a PostScript printer, for example, font 3 is Courier, font 4 is Helvetica, and font 5 is Times Roman.
The POINT-SIZE option specifies type size in points. You can use a whole number or even a fraction (for example, POINT-SIZE=10.5). The following command changes the font to 14-point Helvetica:
alter-printer point-size=14 font=4 ! switch font
The PRINT-IMAGE command inserts the logo. PRINT-IMAGE is followed by a print position corresponding to the upper-left corner of the image (line 1, column 19 in the sample program). The TYPE option specifies the image file type. In the example, the image is stored in Microsoft Windows bitmap format (bmp-file). The size of the image is specified in terms of columns (width) and lines (height). In the example, the image is 30 characters wide (3 inches) and 7 lines high (1-1/6 inches).
In SQR, images are always stored in external files. The format of the image must match that of the printer that you are using. These formats are:
The SOURCE option specifies the file name of the image file. In the example, the file is Acmelogo.bmp. The file is assumed to reside in the current directory or in the directory in which SQR is installed (you can place the logo file in either of these places). The file can reside in any directory, however, as long as you specify a full path name for the image file.
The output file now contains graphic language commands. SQR can produce output that is suitable for HP LaserJet printers in a file format that uses the HP PCL language or output that is suitable for PostScript printers in a file format that uses the PostScript language. SQR can also produce printer-independent output files in a special format called SQR Portable Format (SPF).
SQR can create a printer-specific output file (an .lis file) or create the output in portable format (SPF). When you create an .spf file, the name of the image file is copied into it, and the image is processed at print time, when printer-specific output is generated. When you use .spf files, a change in the contents of the image file is reflected in the report the next time that you print it or view it. You can create printer-specific output by using SQR or SQR Execute to directly generate an .lis file or by using SQR Print to generate an .lis file from an .spf file.
See Adding Declarations Using the SETUP Section, Printing with SQR.
See Also
Enterprise PeopleTools 8.50 PeopleBook: SQR Language Reference for PeopleSoft
Sharing Images Among Reports
You can place logos and other images in a report by using only the PRINT-IMAGE command. However, the DECLARE-IMAGE command is useful if you want several programs to share the definition of an image.
The ex12c.sqr program prints a simple form letter. It shows how to print a logo by using the DECLARE-IMAGE and PRINT-IMAGE commands and how to print a signature by using only PRINT-IMAGE.
Because the image is shared among several reports, the DECLARE-IMAGE command is contained in the acme.inc file:
File acme.inc declare-image acme_logo type=bmp-file image-size=(30,7) source='acmelogo.bmp' end-declare
This file declares an image with acme-logo as the name. It specifies the logo that is used in the previous sample program. The declaration includes the type and source file for the image. When the image is printed, you do not need to respecify these attributes.
Multiple programs can share the declaration and include the acme.inc file. If you later need to change an attribute, such as the source, you need to change it in only one place. The image size is specified and provides the default.
To change the size of an image in a particular report, use the IMAGE-SIZE argument of the PRINT-IMAGE command. It overrides the image size that is specified in DECLARE-IMAGE.
Program ex12c.sqr begin-setup #include 'acme.inc' end-setup begin-program do main end-program begin-procedure main begin-select name addr1 addr2 city state zip phone do write_letter from customers order by name end-select end-procedure ! main begin-procedure write_letter move &city to $csz concat ', ' with $csz concat &state with $csz concat ' ' with $csz move &zip to $zip xxxxx-xxxx concat $zip with $csz move &phone to $phone_no (xxx)bxxx-xxxx ! Edit phone number. begin-document (1,1,0) &name @logo &addr1 &addr2 $csz .b .b .b $current-date Dear &name .b Thank you for your inquiry regarding Encore, Maestro!!, our revolutionary teaching system for piano and organ. If you've always wanted to play an instrument but felt you could never master one, Encore, Maestro!! is made for you. .b Now anyone who can hum a tune can play one too. Encore, Maestro!! begins with a step-by-step approach to some of America's favorite songs. You'll learn the correct keyboarding while hearing the sounds you make through the headphones provided with the Encore, Maestro!! system. From there, you'll advance to intricate compositions with dazzling melodic runs. Encore, Maestro!! can even teach you to improvise your own solos. .b Whether you like classical, jazz, pop, or blues, Encore, Maestro!! is the music teacher for you. .b A local representative will be calling you at $phone_no to set up an in-house demonstration, so get ready to play your favorite tunes!! .b Sincerely, @signature .b .b Clark Axelotle end-document position () @logo print-image acme-logo () image-size=(16,4) position () @signature print-image () type=bmp-file image-size=(12,3) source='clark.bmp' new-page end-procedure ! write_letter
The #INCLUDE command, which is performed at compile time, gets text from another file. In this program, the #INCLUDE 'acme.inc' command includes the code from the acme.inc file.
The document paragraph begins with a BEGIN-DOCUMENT command and ends with an END-DOCUMENT command. It uses variables and document markers to print inside the letter. The program uses variables for the name and address, the date, and the phone number. It uses document markers for the logo and signature.
Document markers are placeholders in the letter. The program uses the @logo and @signature document markers in a POSITION command before printing each image. The document markers make unnecessary specifying the position of these items in the PRINT-IMAGE command. Instead, you print to the current position.
The date is prepared with the $current-date reserved variable. It is printed directly in the document paragraph without issuing a PRINT command.
The program uses the CONCAT command to put together the city, state, and zip code. In the document paragraph, variables retain their predefined sizes. A column variable, for example, remains the width of the column as defined in the database. You can print the date and phone number directly, however, because they occur at the end of a line, without any following text.
Printing Bar CodesSQR supports a wide variety of bar code types, which you can include in an SQR report.
To create a bar code, use the PRINT-BAR-CODE command. Specify the position of the bar code in an ordinary position qualifier. In separate arguments, specify the bar code type, height, text to be encoded, caption, and optional check sum. For example:
print-bar-code (1,1) type=1 height=0.5 text='01234567890' caption='0 12345 67890'
Arguments to PRINT-BAR-CODE can be variables or literals.
See Also
Enterprise PeopleTools 8.50 PeopleBook: SQR Language Reference for PeopleSoft