Publishing a Report

This section discusses how to:

  • Publish reports.

  • Support older browsers.

  • View published reports.

  • Publish by using an automated process.

  • Publish by using a Common Gateway Interface (CGI) script.

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 about 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.

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.

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

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:

  • Sets the necessary environment variables.

  • Runs the /usr2/reports/myreport.sqr program and generates the /usr2/reports/myreport.htm and /usr2/reports/myreport.h00 output files.

  • Specifies /dev/null as the source of standard input to prevent the program from stopping if it requires input.

  • Redirects the standard output to /usr2/reports/myreport.out to capture any status messages.

    You can view the output file at a later time to diagnose any problems.

  • Copies the generated report files to the /usr2/web/docs directory to publish it on the web server.

    (Use the directory name that is appropriate for your server.)

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.

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:

  • The form

  • The CGI script

  • The SQR program

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 clicked. 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. Do not 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 was 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