Creating a Free-Form Report

You can create a free-form report from a TABLE request that omits the display commands that control columnar and matrix formatting (PRINT, LIST, SUM, and COUNT). Instead, the request includes the following report features:

Heading

Contains the body of the report. It displays the text characters, graphic characters, and data fields that make up the report.

Footing

Contains the footing of the report. This is the text that appears at the bottom of each page of the report. The footing may display the same characters and data fields as the heading.

Prefix operators

Indicates field calculations and manipulation.

Temporary fields

Derives new values from existing fields in a data source.

BY phrases

Specifies the report sort order, and determines how many records are included on each page.

WHERE criteria

Selects records for the report.

When creating a free-form report, you can:


Top of page

Example: Creating a Free-Form Report

Suppose that you are a Personnel Manager and it is your responsibility to administer your company education policies. This education policy states that the number of hours of outside education that an employee may take at the company expense is determined by the number of hours of in-house education completed by the employee.

To do your job efficiently, you want a report that shows the in-house education history of each employee. Each employee information should display on a separate page so that it can be placed in the employee personnel file and referenced when an employee requests approval to take outside courses.

To meet this requirement, you create the EMPLOYEE EDUCATION HOURS REPORT, which displays a separate page for each employee. Notice that pages 1 and 2 of the report provide information about employees in the MIS department, while page 6 provides information for an employee in the Production department.

The following diagram simulates the output you would see if you ran the procedure in Request for EMPLOYEE EDUCATION HOURS REPORT.


Top of page

Example: Request for EMPLOYEE EDUCATION HOURS REPORT

The following request produces the EMPLOYEE EDUCATION HOURS REPORT, which you can see in Creating a Free-Form Report. Numbers to the left of the request correspond to numbers in the following annotations:

 
1. DEFINE FILE EMPLOYEE
      CR_EARNED/I2 = IF ED_HRS GE 50 THEN 9
         ELSE IF ED_HRS GE 30 THEN 6
         ELSE 3;
      END 
2. TABLE FILE EMPLOYEE 
3. HEADING
   "PAGE <TABPAGENO"
   " "
   "<13>EMPLOYEE EDUCATION HOURS REPORT" 
4. "<14>FOR THE <DEPARTMENT DEPARTMENT" 
5. "</2"
   "EMPLOYEE NAME:        <FIRST_NAME> <LAST_NAME>"
   "EMPLOYEE ADDRESS:     <ADDRESS_LN1>"
   "<23><ADDRESS_LN2>"
   "<23><ADDRESS_LN3>"
   "</1"
   "JOB CODE: <JOBCODE>"
   "JOB DESCRIPTION: <JOB_DESC>"
   "</1" 
6. "MOST RECENT COURSE TAKEN ON: <MAX.DATE_ATTEND>"
   "TOTAL NUMBER OF EDUCATION HOURS: <ED_HRS>"
   "</1" 
7. "<10>|-----------------------------|" 
8. "<10>| EDUCATION CREDITS EARNED <CR_EARNED>|"
   "<10>|-----------------------------|" 
9. FOOTING
   "<15>PRIVATE AND CONFIDENTIAL"
   BY DEPARTMENT NOPRINT 
10.BY EMP_ID NOPRINT PAGE-BREAK 
11.WHERE ED_HRS GT 0
   END

The list that follows explains the role of each line of the request in producing the sample report:

  1. The DEFINE command creates a virtual field for the report. The calculation reflects the company's policy for earning outside education credits. The result is stored in CR_EARNED and appears later in the report.
  2. A free-form report begins with a standard TABLE FILE command. The sample report uses the EMPLOYEE data source for its data.
  3. The heading section, initiated by the HEADING command, defines the body of the report. Most of the text and data fields that appear in the report are specified in the heading section. In this request, the heading section continues until the FOOTING command is encountered.
  4. This line illustrates the versatility of a heading. It shows the following:
  5. Line-skipping commands enhance the readability of a report. The command </2, when coded on a line by itself, generates three blank lines, as seen between the report heading and employee name.
  6. This line illustrates how to perform a field calculation in a free-form report using a prefix operator. The request here is for the date on which the most recent course was taken, which is the maximum value for the DATE_ATTEND field.
  7. The next three lines illustrate the use of special characters to create a graphic in the report. The box around EDUCATION CREDITS EARNED may need adjustment for output displayed in a proportional font.
  8. The value of the field created by the DEFINE command displays in the box, highlighting the number of education credits an employee has earned. This line demonstrates that you can display a virtual field in the body of your report. This is the field that was created at the start of the request.
  9. The FOOTING command signifies the beginning of the footing section, ending the heading section as well. Since this is a personnel report, the words PRIVATE AND CONFIDENTIAL must appear at the end of each page of the report. The footing can accomplish this.
  10. This line illustrates sorting in a free-form report. The report specifications require that information for only one employee appears per page; that requirement is met through the BY and PAGE-BREAK commands.
  11. You can specify record selection in a free-form report. As a result of the WHERE criterion, the report includes only employees who have accumulated in-house education credits.

Information Builders