Generating and Publishing HTML from an SQR Program

This chapter provides an overview of SQR capabilities that are available with HTML and discusses how to:

Click to jump to parent topicUnderstanding SQR Capabilities That Are Available with HTML

The SQR language has a rich set of features, but some of these features are not available for HTML output due to the limitations of that format.

The SQR features that are supported for HTML include:

The SQR features that are not currently supported for HTML output include:

Note. You can generate professional quality HTML report files with SQR without having to be an HTML expert. However, if you want to adapt HTML output by using SQR’s HTML procedures, you may want to learn more about HTML.

Click to jump to parent topicGenerating HTML Output

This section provides an overview of HTML output and discusses how to:

Click to jump to top of pageClick to jump to parent topicUnderstanding HTML Output

When an SQR program generates HTML output, that output contains HTML tags. An HTML tag is a character sequence that defines how information appears in a web browser.

Typically, HTML output looks like this:

<HTML><HEAD><TITLE>myreport.lis</TITLE></HEAD><BODY>

This code is only a portion of the HTML output that SQR generates. The tags that it contains indicate the start and end points of HTML formatting.

For example, in the code example, the <HTML> tag identifies the output that follows as HTML output. The <TITLE> and </TITLE> tags enclose the report title, in this case, myreport.lis. The <BODY> tag indicates that the information following it makes up the body of the report.

Click to jump to top of pageClick to jump to parent topicProducing HTML Output

You can produce HTML output from an SQR program by using one of four methods, each of which provides a different level of HTML features:

The procedures that are used in the last two options are contained in a file called html.inc. To use HTML procedures, the SQR program must include this command:

#include 'html.inc'

The HTML.INC file is located in the SAMPLE (or SAMPLEW) directory. Use the -I command-line flag to specify its path.

Click to jump to top of pageClick to jump to parent topicUsing -PRINTER:EH

You can generate enhanced HTML output from an SQR program by using the -PRINTER:EH command-line flag. This produces output that contains HTML formatting tags. All output is displayed as fully formatted HTML 3.0 or 3.2 text. You can generate high-quality HTML from SQR programs by using -PRINTER:EH to issue a command like this:

sqrw myreport.sqr sammy/baker@rome -PRINTER:EH

You can control the version of HTML that is used by editing the FullHTML enhanced HTML parameter in the PSSQR.INI file. Set FullHTML to be equal to TRUE for HTML 3.2 or FALSE for HTML 3.0. Adjust this based on the level of HTML that your web browser supports. The -PRINTER:EH default output is HTML 3.0.

If you have existing .spf files for which you want to generate enhanced HTML output, you do not need to rerun your SQR program. You can invoke SQR Print (with SQRP or SQRWP, depending on your platform) to generate enhanced HTML from .spf files by using a command like this:

sqrwp myreport.spf -PRINTER:EH

From within the SQR Viewer, you can also generate this same, high-quality HTML by selecting File, Save as HTML. The HTML level output from the SQR Viewer is also determined by the PSSQR.INI file settings and has the same default value.

You can also generate enhanced HTML files with precompiled SQR program files (.sqt files). Run the .sqt file against SQR Execute with a command like this:

sqrwt myreport.sqt sammy/baker@rome -PRINTER:EH

As is true when running any .sqt file, you can run it against SQR (or sqrw, on Microsoft Windows platforms) by including the -RT flag. To generate enhanced HTML, use the -PRINTER:EH flag in the command:

sqrw myreport.sqr sammy/baker@rome -RT -PRINTER:EH

The sample program ex7a.sqr produces a simple master and detail report. By running it with -PRINTER:EH, you can produce HTML output. A left frame is produced with links to each page of the report. The right frame also features a navigation bar that appears at the top of every page in the report. The navigation bar enables you to move to the first or last page or to move one page forward or back from your relative page-viewing position.

With -PRINTER:EH, you can also use additional flags to modify the output, such as:

These flags work only with -PRINTER:EH.

Click to jump to top of pageClick to jump to parent topicSetting HTML Attributes Under -PRINTER:EH

In certain cases, you may want additional control over the enhanced HTML code that is generated with -PRINTER:EH. SQR supports extensions that enable you to control the generated HTML, specifying titles, background colors and images, links, text colors, and more.

Specifying HTML Titles

The HTML page title normally appears on the caption bar of the browser window and is also used when you are creating a bookmark for the page. It is placed between the <TITLE> and </TITLE> HTML tags. Specify the title of the HTML page by using the %%Title extension at the beginning of the SQR program by entering:

Print-Direct Printer=html '%%Title ​Monthly Sales'

Specifying Background Colors

Specify a background color for the pages that are generated with -PRINTER:EH by using the %%Body-BgColor extension. Enter code like this at the beginning of the program:

Print-Direct Printer=html '%%Body-BgColor ​#0000FF'

To set the background color for the navigation bar, enter code like this:

Print-Direct Printer=html '%%Nav-Body-BgColor ​#0000FF'

See “Specifying HTML Colors” subsequently.

Specifying Background Images

To use a background image for the report pages that the enhanced HTML generates, insert the %%Background extension at the beginning of the program:

Print-Direct Printer=html '%%Background tile.gif'

To set the background image for the navigation bar, enter code like this:

Print-Direct Printer=html '%%Nav-Background D:\jpegdir\house.jpg'

The background attribute can be any valid Uniform Resource Locator (URL). If you do not specify the %%Nav-Background extension while specifying the body background, the background image that you specify for the body is used both in the body and in the navigation bar. If you do not want an image to appear in the navigation bar, use code like this:

Print-Direct printer=html '%%Nav-Background EMPTY'

Specifying Links

The %%Href extension specifies a link in the report. This extension enables you to make a text, number, image, or chart object into a link. The object can be the item that you click to activate the link or it can be the location on the page where the link takes you. Specify the latter by using the %%Anchor extension. For example: 

Print-Direct Printer=html '%%Href #section2'
Print 'ABC' ()
...
Print-Direct Printer=html '%%Anchor section2'
Print 'XYZ' ()

In this example, clicking the ABC text on the page jumps to the XYZ text. When using frames or multiple browser windows, you can control which frame displays the target of the link by using the target option of the %%Href extension. For example, specify on one line: 

Print-Direct Printer=html '%%Href target="_top" http://www.peoplesoft.com'

Specifying Text Colors

Use the %%Color and %%ResetColor extensions to change the color of text. The following code example demonstrates this capability:

If &Salary > 100000
Print-Direct Printer=html '%%Color #FF0000'
End-If
Print &Salary ()
If &Salary > 100000
Print-Direct Printer=html '%%ResetColor'
End-If

In this example, when the value of the column is over 100000, it prints in red. The %%Color extension affects all text (and number) printing from this point on. This is similar to the behavior of the ALTER-PRINTER command. A subsequent invocation of %%Color with a different color value sets the current color to the new color. To restore the color back to the default (normally, black) use the %%ResetColor extension.

Specifying HTML Colors

Specifying color as a red-green-blue (RGB) hexadecimal value is the only way to designate color in SQR. Your browser documentation should contain a listing of supported colors and their hexadecimal values. To specify color as an RGB hexadecimal value, enter a # character followed by six hexadecimal digits. The first two digits specify the intensity of the red, the next two specify the green, and the last two specify the blue. For example, green is #00FF00.

Including Your Own HTML Tags

Enhanced HTML extensions enable you to include your own HTML tags in the output. These tags are passed through to the output without change. Use this feature to include advanced HTML capabilities such as JavaScript and <APPLET> tags .

SQR PRINT with CODE-PRINTER=HT enables you to inject any text into the HTML output. SQR does not check the text that you are printing. This text can contain anything that your browser understands. Do not use this method for formatting, because your formatting may conflict with -PRINTER:EH enhanced HTML formatting. -PRINTER:EH enhanced HTML uses HTML tables extensively. To fully control the formatting, use the HTML procedures that are defined in html.inc and that are documented in this section. By invoking the html_on procedure, you instruct the enhanced HTML to perform no formatting. Specify all formatting by using the HTML procedures in html.inc or by using SQR PRINT with CODE-PRINTER=HT to insert HTML codes. When you use SQR PRINT with CODE-PRINTER=HT, the enhanced HTML does not translate special symbols that are used in HTML tags, such as <, >, and &.

See Also

Enterprise PeopleTools 8.51 PeopleBook: SQR Language Reference for PeopleSoft

Click to jump to top of pageClick to jump to parent topicUsing -PRINTER:HT

Another method for generating HTML output from an SQR program is running a program with the command-line flag -PRINTER:HT. Alternatively, you can make some simple modifications to the program. Add either DECLARE-PRINTER with the TYPE=HT argument or USE-PRINTER-TYPE HT.

With these methods, HTML output is generated in the following way:

The sample program ex7a.sqr produces a simple master and detail report. By running it with -PRINTER:HT, you can produce HTML output. A left frame is produced with links to each page of the report. The right frame also features a navigation bar that appears at the top of every page in the report. The navigation bar enables you to move to the first or last page or to move one page forward or back from your relative page viewing position.

See Creating Master and Detail Reports.

Click to jump to top of pageClick to jump to parent topicBursting Reports

With SQR, you can generate HTML format reports by using -PRINTER:EH or -PRINTER:HT command-line flags. If you want HTML files to be smaller in size for faster in load time or to be divided on the basis of report page ranges, or if you want to preview a report’s table of contents in your web browser without generating an entire report, use -BURST:{xx} with -PRINTER:EH or -PRINTER:HT.

By using -BURST:P (or BURST:P1) with -PRINTER:EH, or by using -BURST:P1 with -PRINTER:HT, you can generate HTML output files that are burst by report page numbers, one report page per .htm file. (This is frequently referred to as demand paging.) So a 25-page report would be divided into 25 separate .htm output files. By using -PRINTER:HT, you can also specify the report page ranges that you want within an HTML file. For example, -BURST:P0,1,3-5 generates an HTML file containing only report page numbers 1, 3, 4, and 5. You can then focus on information that is truly of interest.

Similarly, if you specify -PRINTER:HT with -BURST:T, only the table of contents file is generated. And if you specify -PRINTER:HT with -BURST:S, report output is generated according to symbolic table of contents entries. By using -BURST:S, you can specify the numeric level to burst on (for example, -BURST:S2 bursts on level 2). If you have used DECLARE-TOC and TOC-ENTRY commands in the SQR program, the table of contents provides more detailed information than just page number links, as illustrated in the following code example.

To use DECLARE-TOC and TOC-ENTRY to improve the information that is available in generated HTML output, this example adds the following code example to the beginning of the sample program ex7a.sqr:

begin-setup
declare-toc common
       for-reports=(all)
       dot-leader=yes
       indentation=2
end-declare
end-setup

The code example also adds this code to the body of the program, in the main procedure immediately following the begin-select and Print ‘Customer Information’ (,1):

toc-entry text = &name

Click to jump to top of pageClick to jump to parent topicSetting Attributes with HTML Procedures

Use the SQR HTML procedures html_set_head_tags and html_set_body_attributes to define a title and background image for a report. To use these procedures, the SQR program must include the html.inc file. You must also run the program by using the -PRINTER:HT command-line flag.

These procedures must be called at the start of the program. For example:

do html_set_head_tags('<TITLE>Monthly Report</TITLE>')
do html_set_body_attributes('BACKGROUND="/images/mylogo.gif"')

The first line of this code example displays the Monthly Report title. Specifically, the entire '<TITLE>Monthly Report</TITLE>' sequence is passed as an argument to the html_set_head_tags procedure. The argument is enclosed in single quotes.

The second line displays the mylogo.gif background image for the web page. Again, an argument is passed to the procedure. The entire argument is enclosed in single quotes, and the file name and path are enclosed in double quotes.

Together, these two lines of code generate the following HTML output:

<HTML><HEAD><TITLE>Monthly Report</TITLE></HEAD>
<BODY BACKGROUND="/images/mylogo.gif">

Click to jump to top of pageClick to jump to parent topicUsing Additional HTML Procedures

Using additional HTML procedures in the SQR program provides enhanced capabilities, including:

Click to jump to top of pageClick to jump to parent topicSetting Output File Types

An SQR report named myreport.sqr creates a FRAME file (myreport.htm) and report output files. The OUTPUT-FILE-MODE entry in the Default-Setting section of the PSSQR.INI file controls the report output file extensions. When this entry is set to SHORT, the report output files use the form myreport.hzz, and when set to LONG, the files use the form myreport_zz.htm. The value of zz ranges from 00 to 99 and reflects the report number.

The FRAME file displays a list (links) of report pages in one frame and the report text in another frame. Each report output file contains a list of pages (links) at the end of the file. If myreport.sqr created multiple reports, then the FRAME file contains a link to each report output file. In addition, each report output file contains links to the other report output files that were created during the program run.

Click to jump to top of pageClick to jump to parent topicTesting HTML Output

When an SQR program produces HTML output, you can preview it on a local system. This is a good way to test the output before you publish it on a website.

To test the output of the program, open the file in the web browser. If your web browser supports the HTML FRAME construct, open the FRAME file (myreport_frm.htm); otherwise, open the report output file (myreport.h00, myreport_00.htm).

Click to jump to parent topicUsing HTML Procedures in an SQR Program

This section provides an overview of HTML procedures and discusses how to:

See Also

Enterprise PeopleTools 8.51 PeopleBook: SQR Language Reference for PeopleSoft

Click to jump to top of pageClick to jump to parent topicUnderstanding HTML Procedures

To enhance the appearance of the HTML output, use HTML procedures in an SQR program.

An SQR program with these procedures generates output as described previously in “Using PRINTER:HT,” with these exceptions:

Click to jump to top of pageClick to jump to parent topicUsing HTML Procedures

When using the HTML procedures, include the html.inc file. As before, you must run the SQR program with the -PRINTER:HT command-line flag.

The SQR program must also call the html_on procedure at the start of the program. The command that calls this procedure is:

do html_on

Additionally, the program must specify a large page length to prevent page breaks. SQR automatically inserts the page navigation links and an <HR> HTML tag at a page break. If a page break occurs in the middle of an HTML construct, such as a table, the output can appear incorrectly. Use the DECLARE-LAYOUT command with a large MAX-LINES setting to prevent page breaks from occurring.

Click to jump to top of pageClick to jump to parent topicPositioning Objects

When HTML procedures are activated:

Thus, the HTML procedures must be used to format the report.

The following code example does not use the HTML procedures to format the output:

print 'Report summary:' (1,1)
print 'Amount billed:' (3,1)
print #amount_amount (3,20)
print 'Total billed:' (4,1)
print #total_amount (4,20)

In this case, all of the text appears on the same line with no spaces between the data.

With the HTML procedures for line breaks and a table, the output can be formatted properly.

The following code example uses the html_br procedure to separate the first two lines of text. The html_table, html_tr, html_td, and html_table_end procedures display the totals in a tabular format. An empty string is passed to each procedure as it is called. This empty string is required if no other argument is passed. 

print 'Report summary:' (1,1)
do html_br(2,'')
do html_table('')
do html_tr('')
do html_td('WIDTH=300')
print 'Amount billed:' (3,1)
do html_td('')
print #amount_amount (3,20)
do html_tr('')
do html_td('WIDTH=300')
print 'Total billed:' (4,1)
do html_td('')
print #total_amount (4,20)
do html_table_end

Click to jump to top of pageClick to jump to parent topicDisplaying Records in Tables

When the HTML procedures are activated, all positioning values in the SQR program are ignored. Thus, the position values cannot be used to display records in a tabular format. To display records in a tabular format, use the following procedures:

Description

Beginning Procedure

End Procedure

Create a table

html_table

html_table_end

Create a caption. The end is typically implied and html_caption_end is not required, but you can use it for completeness.

html_caption

html_caption_end

Create rows. The end is typically implied and html_tr_end is not required, but you can use it for completeness.

html_tr

html_tr_end

Create column headings. The end is typically implied and html_th_end is not required, but you can use it for completeness.

html_th

html_th_end

Create columns. The end is typically implied and html_td_end is not required, but you can use it for completeness.

html_td

html_td_end

The following sample program uses these table procedures to display information in a tabular format:

Program ex28a.sqr
#include 'html.inc'
begin-program
   do main
end-program
! set a large page length to prevent page breaks
begin-setup
  declare-layout default
    max-lines=750
  end-declare
end-setup
begin-procedure main
! turn on HTML procedures
 do html_on
! start the table and display the column headings
do html_table('border')
do html_caption('')
print 'Customer Records' (1,1)
do html_tr('')
do html_th('')
print 'Cust No' (+1,1)
do html_th('')
print 'Name'    (,10)
! display each record
begin-select
   do html_tr('')
   do html_td('')
cust_num         (1,1,6) edit 099999
   do html_td('')
name         (1,10,25)
   next-listing skiplines=1 need=1
from customers
end-select
! end the table
do html_table_end
end-procedure

Click to jump to top of pageClick to jump to parent topicCreating Headings

The heading procedures display text by using heading levels like those in a book. The available heading levels range from 1 to 6; a first-level heading is the highest. To use the heading procedures, call the appropriate heading procedure before the text is generated. After the text is generated, call the corresponding end procedure.

The following code example displays text as a second-level heading:

do html_h2('')
print 'A Level 2 Heading' (1,1)
do html_h2_end

Click to jump to top of pageClick to jump to parent topicHighlighting Text

The highlighting procedures enable you to display text in the various HTML highlighting styles. Highlighting is also called logical markup.

To use the highlighting procedures, call the appropriate highlighting procedure before the text is generated. After the text is generated, call the corresponding end procedure.

The following highlighting procedures are available:

Type of Highlighting

Beginning Procedure

End Procedure

Blink

html_blink

html_blink_end

Citation

html_cite

html_cite_end

Code

html_code

html_code_end

Keyboard

html_kbd

html_kbd_end

Sample

html_sample

html_sample_end

Strike

html_strike

html_strike_end

Subscript

html_sub

html_sub_end

Superscript

html_sup

html_sup_end

The following code example displays text in the subscript style:

print 'Here is ' (1,1)
do html_sub('')
print 'subscript' ()
do html_sub_end
print ' text' ()

Click to jump to top of pageClick to jump to parent topicCreating Links

The link procedures enable you to create links and link anchors. When the user clicks the link, the web browser switches to the top of the specified HTML document, to a point within the specified document, or to a link anchor within the same document. A link can point to the home page of a website, for example.

To insert a link, use the html_a procedure to format the information that is to become the link, and use the html_a_end procedure to mark the end of the link. Two useful attributes for the html_a procedure are the HREF and NAME attributes:

These attributes are passed as arguments to the html_a procedure.

The following code example creates an anchor and two links. The anchor is positioned at the top of the document. The first link points to the HTML home.html document. The second link points to the anchor named TOP in the current document. Note the # sign in the argument, which indicates that the named anchor is a point within a document. The third link points to an anchor named POINT1 in the mydoc.html document.

do html_a('HREF=home.html')
print 'Goto home page' ()
do html_a_end

do html_a('NAME=TOP')
do html_a_end

print 'At the top of document' ()
do html_br(40, '')
print 'At the bottom of document' ()
do html_p('')

do html_a('HREF=#TOP')
print 'Goto top of document' ()
do html_a_end

do html_a ('HREF=mydoc.html#POINT1')
print 'Goto point1 in mydoc.html' ()
do html_a_end

Click to jump to top of pageClick to jump to parent topicIncluding Images

You can include an image in an HTML output with the PRINT-IMAGE command or the html_img procedure. Both of these produce the <IMG> HTML tag.

The PRINT-IMAGE command displays images for all printer types but enables you to specify only the image type and source. The html_img procedure displays images only for the HTML printer type, but it enables you to specify any of the attributes that are available for an <IMG> HTML tag.

For HTML output, you can use only Graphics Interchange Format (GIF) or JPEG files. With PRINT-IMAGE, use the TYPE=GIF-FILE or TYPE=JPEG-FILE argument, respectively.

Click to jump to top of pageClick to jump to parent topicDisplaying Text in Lists

The list procedures display lists. To use these procedures, call the appropriate procedure before the list is generated. After the list is generated, call the corresponding end procedure.

The following list procedures are available:

List Type

Beginning Procedure

End Procedure

Definition (terms and their definitions)

html_dl

html_dl_end

Directory

html_dir

html_dir_end

Menus

html_menu

html_menu_end

Ordered (numbered or lettered)

html_ol

html_ol_end

Unordered (bulleted)

html_ul

html_ul_end

To display a list, except for the definition list, call the appropriate list procedure before starting the output. Call html_li to identify each item in the list; you can also call html_li_end for completeness. After specifying the output, call the corresponding end procedure.

The following code example displays an ordered list:

do html_ol('')
do html_li('')
print 'First item in list' (1,1)
do html_li_end 
do html_li('')
print 'Second item in list' (+1,1)
do html_li_end 
do html_li('')
print 'Last item in list' (+1,1)
do html_li_end 
do html_ol_end

To display a definition list, call html_dl before starting the output. Call html_dt to identify a term and html_dd to identify a definition. After specifying the output, call html_dl_end. You can also call html_dd_end and html_dt_end for completeness.

The following code example displays a definition list:

do html_dl('')
do html_dt('')
print 'A daisy' (1,1)
do html_dt_end 
do html_dd('')
print 'A sweet and innocent flower' (+1,1)
do html_dd_end 
do html_dt('')
print 'A rose' (+1,1)
do html_dt_end 
do html_dd('')
print 'A very passionate flower' (+1,1)
do html_dd_end 
do html_ol_end

Click to jump to top of pageClick to jump to parent topicFormatting Paragraphs

The HTML procedures provide various paragraph-formatting capabilities. To use these procedures, call the appropriate paragraph procedure before the list is created.

The following procedures are available:

Formatting Type

Beginning Procedure

End Procedure

Paragraph breaks

html_p

html_p_end

Many HTML constructs imply an end of paragraph; thus, the html_th_end procedure is not needed, but you can use it for completeness.

Line breaks

html_br

NA

Horizontal dividers (usually a sculpted line)

html_hr

NA

Prevent text wrapping

html_nobr

html_nobr_end

The following code example uses the paragraph-formatting procedures to format text into paragraphs:

print 'Here is some normal text' (1,1)
do html_p('ALIGN=RIGHT')
print 'Here is right aligned text' (+1,1)
do html_br(1,'')
print 'and a line break' (+1,1)
do html_p_end
do html_hr('')
do html_nobr('')
print 'A very long line of text that cannot be wrapped' (+1,1)
do html_nobr_end

Click to jump to top of pageClick to jump to parent topicIncorporating Your Own HTML Tags

You can incorporate your own HTML tags into the HTML output. To do so, use the PRINT command with the CODE-PRINTER=HT argument.

Text that is printed with this argument is placed only in the HTML output that is generated when the HTML printer type is specified. With all other printer types, the text is not placed in the output. In addition, the specified text is placed directly in the HTML output without any modifications, such as the mapping of reserved characters.

The following code example uses the <B> HTML tag to print bold text:

print '<B>' () code-printer=ht
print 'Bold text' ()
print '</B>' () code-printer=ht

Click to jump to parent topicModifying an Existing SQR Program for HTML

In this section, an existing sample program, ex12a.sqr, is modified to use HTML procedures. The modified program is named program ex28b.sqr. First, examine the output from ex12a.sqr when this program is run without modifications by using the -PRINTER:HT command-line flag. Three HTML files are generated: ex12a.htm, ex12a_frm.htm, and ex12a_toc.htm.

Program ex28b.sqr 
#include 'html.inc'
begin-setup
  declare-layout default
    max-lines=10000
  end-declare
end-setup
begin-program
 do main
end-program
begin-procedure main
do html_on
print $current-date (1,1) edit 'DD-MON-YYYY'
do html_p('')
do html_table('BORDER')
do html_tr('')
do html_th('WIDTH=250')
print 'Name'   (3,1)
do html_th('WIDTH=120')
print 'City'   (,32)
do html_th('WIDTH=60')
print 'State'  (,49)
do html_th('WIDTH=90')
print 'Total'  (,61)
begin-select
  do html_tr('')
  do html_td('')
name  (,1,30)
  do html_td('')
city  (,+1,16)
  do html_td('')
state (,+1,5)
  do html_td('ALIGN=RIGHT')
tot   (,+1,11) edit 99999999.99
  next-listing no-advance need=1
  let #grand_total = #grand_total + &tot
from customers
end-select  
  do html_tr('')
  do html_tr('')
  do html_td('COLSPAN=3 ALIGN=RIGHT')
print 'Grand Total' (+1,40)
  do html_td('ALIGN=RIGHT')
print #grand_total (,55,11) edit 99999999.99
do html_table_end
end-procedure ! main

In this code example, a DECLARE-LAYOUT command with a large page length setting that is specified in the MAX-LINES argument is issued to prevent page breaks.

The html_on procedure activates the HTML procedures.

The html_table, html_tr, html_td, and html_th procedures position the information in a tabular format. Note the arguments that are passed to the HTML procedures:

Instead of using a HEADING section, use the html_tr and html_th procedures to display column headings.

See Displaying Records in Tables.

Click to jump to parent topicPublishing a Report

This section discusses how to:

Click to jump to top of pageClick to jump to parent topicPublishing a Report

You can publish an SQR report on a website, and then anyone with a web browser can view the report over the internet or an intranet by specifying its URL.

To publish a report:

  1. Run the SQR program.

  2. Determine where the report output will be stored on the web server.

    The directory must be one that is referenced by a URL on the server. See your webmaster for more details on creating a URL.

  3. Copy the generated HTML output files to the selected directory on the web server.

    If the output is generated on a client workstation, use a utility such as FTP to transfer the HTML output files to the web server.

    Note. If you select the zip file option, a zip file is created for the generated HTML output in addition to the files being placed in the file system.

  4. Create links on a home page or other website that point to the report files so that users browsing the network can navigate to the report and view it.

Click to jump to top of pageClick to jump to parent topicSupporting Older Browsers

To support older web browsers that do not support the HTML FRAME construct, create two separate links: one pointing to the FRAME file (.htm) and labeled to indicate the frame version, and another pointing to the report output file and labeled to indicate the nonframe version. If the report was created with HTML procedures, however, it should contain only a single page. In that case, a listing of report pages that are contained in the FRAME file is not needed. Only the report output file is required for publication on a website.

Click to jump to top of pageClick to jump to parent topicViewing a Published Report

Use a web browser to view a report that is published on a website. To do this, specify a URL in your web browser, for example: http://www.myserver.com/myreport.htm.

Click to jump to top of pageClick to jump to parent topicPublishing by Using an Automated Process

The webmaster can create a program that automates the publishing process. The program should run the SQR program and copy the output to the appropriate location. You can start the program by using a scheduling utility to automatically run the program and publish it on the website at specified times.

The sample Bourne shell program:

Here is the code example:

#! /bin/sh
# set the appropriate environment values
ORACLE_SID=oracle7; export ORACLE_SID
ORACLE_HOME=/usr2/oracle7; export ORACLE_HOME
SQRDIR=/usr2/sqr/bin; export SQRDIR
# invoke the SQR program
sqr /usr2/reports/myreport.sqr orauser/orapasswd \
    -PRINTER:ht -I$SQRDIR \
    > /usr2/reports/myreport.out 2>&1 < /dev/null
# copy over the output
cp /usr2/reports/myreport.htm /usr2/web/docs
cp /usr2/reports/myreport.h00 /usr2/web/docs

Note. You must adjust the environment variables and the file names to fit your particular environment. See the documentation of your scheduling software for more details.

Click to jump to top of pageClick to jump to parent topicPublishing by Using a CGI Script

If you use the CGI script method, any user with a web browser can run an SQR and view the output. You can enable the user to run an SQR by providing a form to fill out.

When a user runs an SQR report through a website:

  1. The user navigates to a form.

  2. The user enters information on the form and clicks a button to invoke the CGI script.

  3. The CGI script runs the SQR program.

  4. The CGI script copies the report output file to the standard output.

  5. The user views the report.

This process requires:

Creating the Form

Create an HTML form to enable the user to enter some values and start the request.

The following HTML code example defines a form with three radio buttons and a submit button. The radio buttons enable the user to specify the sorting criteria. The Submit button invokes the CGI script.

Here is the HTML code:

<HTML>
<TITLE>View Customer Information</TITLE>
<FORM METHOD=POST ACTION="/cgi-bin/myreport.sh">
<B>Select the Field to Sort By</B><P><DIR>
<INPUT TYPE="radio" NAME="rb1" VALUE="cust_num" CHECKED> Number<BR>
<INPUT TYPE="radio" NAME="rb1" VALUE="name"> Name<BR>
<INPUT TYPE="radio" NAME="rb1" VALUE="city"> City<BR>
<P><INPUT TYPE="submit" NAME="run" VALUE="Run Report"></DIR>
</FORM>
</HTML>

The FORM METHOD tag specifies that the /cgi-bin/myreport.sh CGI script is invoked when the Submit button is pressed. Adjust the URL of the CGI script to fit your particular environment.

In the INPUT tags, the TYPE=“radio” attribute defines a radio button. The VALUE attribute of the selected radio button is passed by the CGI script to the SQR program.

Creating the CGI Script

The CGI script is started when a user makes a request from a form. A CGI script can be any executable program. Don't call SQR directly as a CGI script—a PERL script, a shell script, or a C program all provide simpler routines for processing as a CGI script.

The CGI script:

  1. Reads the contents of the standard input stream and parses them to obtain the values that were entered on the form.

    If the form has no input fields, this step is not required.

  2. Identifies the output as being in HTML format by sending the Content-type: text/html string and an extra empty line to the standard output stream.

  3. Invokes the SQR program.

    Values that the user entered on the form are passed to the SQR program by the CGI script and the command line.

  4. Sends the generated .lis file to the standard output stream.

    The .htm file is not used because it points to the .lis file with a relative URL.

    The relative URL does not specify to the web browser where to find the .lis file. You should make provisions within your SQR program to send an error message.

The following Bourne shell is an example of a CGI script:

#! /bin/sh
# set the appropriate environment values
ORACLE_SID=oracle7; export ORACLE_SID
ORACLE_HOME=/usr2/oracle7; export ORACLE_HOME
SQRDIR=/usr2/sqr/bin; export SQRDIR
# identify the output as being HTML format
echo "Content-type: text/html"
echo ""
# get values from fill-out form using the POST method
read TEMPSTR
SORTBY=`echo $TEMPSTR | sed "s;.*rb1=;;
s;&.*;;"`
# invoke the SQR program
sqr7 /usr2/reports/myreport.sqr orauser/orapasswd \
    -PRINTER:ht -f/tmp/myreport$$.lis -I$SQRDIR "$SORTBY" \
     > /tmp/myreport$$.out 2>&1 < /dev/null
if [ $? -eq 0 ]; then
   # display the output
   cat /tmp/myreport$$.lis
else
   # error occurred, display the error
   echo "<HTML><BODY><PRE>"
   echo "FAILED TO RUN SQR PROGRAM"
   cat /tmp/myreport$$.out
   echo "</PRE></BODY></HTML>"
fi# remove temp files
rm /tmp/myreport$$.*

The script performs the following tasks:

  1. Sets the necessary environment variables. Then it sends the Content-type: text/html string and an extra empty line to the standard output stream to identify the text as being HTML format.

  2. Retrieves the value of the selected radio button into the SORTBY variable. The script passes the value to the SQR program on the command line.

  3. Runs the SQR program. The script uses the /usr2/reports/myreport.sqr report file and generates the /tmp/myreport$$.lis file. In addition, the script redirects the standard input from /dev/null to prevent the program from stopping if the program requires any input. It also redirects the standard output to /tmp/myreport$$.out to capture any status messages. The $$ is the process ID of the program and is used as a unique identifier to prevent any multiuser problems.

  4. Copies the generated report file to the standard output stream. If an error occurs, the script generates the status message file instead to enable the user to view the status messages. It then deletes any temporary files.

Passing Arguments to the SQR Program

You must modify the SQR program to accept values that the user enters on the form.

The following code example is the main procedure from sample program ex28b.sqr. It has been modified to use the SORT BY value that is passed from the CGI script. The $sortby variable is obtained from the command line with an INPUT command and is used as dynamic variables in the ORDER BY clause. The modified lines are shown like this: 

begin-procedure main
​input $sortby 'Sort by' type=char
​do html_on
do html_table('')
do html_tr('')
do html_th('')
print 'Name'   (3,1)
do html_th('')
print 'City'   (,32)
do html_th('')
print 'State'  (,49)
begin-select
  do html_tr('')
  do html_td('')
name  (,1,30)
  do html_td('')
city  (,+1,16)
  do html_td('')
state (,+1,5)
next-listing no-advance need=1
 let #grand_total = #grand_total + &tot
from customers
​order by [$sortby]
​end-select