Developing Your Report Request

In this section:

The only requirement for reporting is identifying a data source. Beyond that, the structure of a report request is very flexible and you only need to include the report elements you want. For example, you only need to include sorting instructions if you want your report to be sorted, or selection criteria if you want to report on a subset of your data.

A report request begins with the TABLE FILE command and ends with the END command. The commands and phrases between the beginning and end of a request define the contents and format of a report. These parts of the request are optional; you only need to include the commands and phrases that produce the report functions you want.

The following are the most frequently used options for structuring a report request.

You can run the request as an ad hoc query or save it as a procedure. Saving a report request as a procedure enables you to run or edit it at any time.

Starting a Report Request

How to:

A report request begins with the designation of a data source. You can then specify the details of your report request. A data source can be specified in the following ways:

Syntax: How to Begin a Report Request

To begin a report request, use the command

TABLE FILE filename

where:

filename

Is the data source for the report.

Completing a Report Request

To complete a report request, use the END or RUN command. These commands must be typed on a line by themselves. To discontinue a report request without executing it, enter the QUIT command.

If you plan to issue consecutive report requests against the same data source during one session, you have the option of using the RUN command. RUN keeps the TABLE facility and the data source active for the duration of the TABLE session. After viewing one report you do not need to repeat the TABLE command to produce another report. You terminate the TABLE session by issuing the END command after the last request.

Creating a Report Example

The example in this topic is a simple report request that illustrates some of the basic functions of WebFOCUS. However, there are many more functions not shown here that you can find information on throughout this documentation.

Example: Creating a Simple Report

The following annotated example illustrates some of the basic functions of WebFOCUS. The numbered explanation in this example corresponds with the code in this request. This request can be generated by typing the commands into a text editor.

1.  JOIN PIN IN EMPDATA TO ALL PIN IN TRAINING AS J1 
2.  DEFINE FILE EMPDATA
    YEAR/YY=COURSESTART; 
3.  END
  
4.  TABLE FILE EMPDATA 
5.  HEADING CENTER
    "Education Cost vs. Salary" 
6.  SUM EXPENSES AS 'Education,Cost' SALARY AS 'Current,Salary' 
7.  AND COMPUTE PERCENT/D8.2=EXPENSES/SALARY * 100; AS 'Percent' 
8.  BY DIV
    BY DEPT 
9.  WHERE YEAR EQ 1991 
10. ON TABLE SUMMARIZE 
11. ON TABLE SET STYLE *
    TYPE=HEADING, STYLE=BOLD, COLOR=BLUE,$
    TYPE=REPORT, FONT=TIMES, SIZE=8,$
    TYPE=REPORT, GRID=OFF,$
    ENDSTYLE 
12. END

The output is:

Creating a Simple Report Output example

The request processes in the following way:

  1. The JOIN command joins the EMPDATA and TRAINING data sources, allowing the request to access information from both data sources as if it were a single structure.
  2. The DEFINE command creates a virtual field which extracts the year from the COURSESTART field in the TRAINING data source.
  3. The END command ends the DEFINE command.
  4. The TABLE command begins the report request.
  5. The HEADING command adds the heading, Education Cost vs. Salary to the report output.
  6. The SUM command adds the values within both the EXPENSES field and the SALARY field. The AS phrase changes the name of the column headings.
  7. The COMPUTE command creates a calculated value using the values that have been aggregated in the SUM command and sorted with the BY command.
  8. The BY phrase sorts the data in the report by the DIV field, and then by the DEPT field.
  9. The WHERE command includes only the data that falls in the year 1991.
  10. The ON TABLE SUMMARIZE command adds all values in both the EXPENSES and SALARY columns, and recalculates the Percent column.
  11. The StyleSheet information formats the report heading and content.
  12. The END command ends the report request.