Developing Your Report Request

The only requirement for reporting is identifying a data source. Beyond that, the structure of a report request is very flexible; that is, 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.

• Specifying fields and columns. Each column in your report represents a field. You can specify which fields you want to display, which fields you want to use to sort the report, which fields you want to use to select records, and which data source fields you want to use in creating temporary fields. Therefore, specifying the fields you want in a report is fundamentally tied to how you want to use those fields in your report.
• Displaying data. You can display data in your report by listing all the records for a field (detailed presentation), or by totaling the records for a field (summary presentation). You can also perform calculations and other operations on fields, such as finding the highest value of a field or calculating the average sum of squares of all the values of a field, and present the results of the operation in your report.
• Sorting a report column. Sorting a report enables you to organize a column's information. FOCUS displays the sort field (the field that controls the sorting order) at the left of the report if you are sorting vertically or at the top if you are sorting horizontally. Sort fields are displayed when their values change. You can also choose not to display sort fields.

  You can sort information vertically, down a column, horizontally, across a row; you can also combine vertical sorting and horizontal sorting to create a simple matrix.

• Selecting records. When you generate a report, you may not want to include every record. Selecting records enables you to define a subset of the data source based on your criteria and then report on that subset. Your selection criteria can be as simple or complex as you wish.
• Showing subtotals and totals. You can display column and row totals, grand totals, and section subtotals in your report.
• Customizing the presentation. There are two aspects of a successful report: the information you present, and how it is presented. A report that identifies related groups of information and draws attention to important facts is more effective than one that simply shows columns of data. For example, you can:
  • Give column titles more meaningful names.
  • Control the display of columns in your report.
  • Create headings and footings for different levels of the report, including each sort group, each page, and the entire report, and dynamically control the display of headings and footings based on conditions you set.
  • Add fonts, colors, grids, and images in a styled report.
  • Highlight a group of related information and separate it from other groups by inserting blank lines, underlines, and page breaks.
• Creating temporary fields. When you create a report, you are not limited to the fields that already exist in the data source. You can create temporary fields, deriving their values from real data source fields, and include them in your report. For details, see Creating Temporary Fields.
• Joining data sources. You can join two or more data sources to create a larger integrated data structure, from which you can report in a single request. For details, see Joining Data Sources.
• Storing and reusing the results. You can store your report data as a data source against which you can make additional queries. This is especially helpful for creating a subset of your data source and for generating two-step reports. You can also format the new data source for use by other data processing tools such as spreadsheets and word processors. For details, see Saving and Reusing Your Report Output.

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.

TABLE FILE EMPLOYEE
HEADING CENTER 
"Departmental Salary Report </1" 
PRINT CURR_JOBCODE AS 'Job Code'
BY DEPARTMENT AS 'Department'
BY LAST_NAME AS 'Last Name'
BY CURR_SAL AS 'Current,Salary'
ON CURR_SAL SUBFOOT
"<13 *** WARNING: <LAST_NAME 's salary exceeds recommended guidelines."
WHEN CURR_SAL GT 27000;
ON DEPARTMENT SUBFOOT
"<13 Total salary expense for the <DEP dept is: <ST.CURR_SAL"
ON DEPARTMENT SKIP-LINE
END

PAGE     1                                                                
                                                                    
                  Departmental Salary Report                                   
                                                                    
                                          Current                              
     Department  Last Name                Salary   Job Code                    
     ----------  ---------                -------  --------                    
                                                                               
     MIS         BLACKWOOD             $21,780.00  B04                         
                 CROSS                 $27,062.00  A17                         
                 *** WARNING: CROSS 's salary exceeds recommended guidelines.  
                 GREENSPAN              $9,000.00  A07                         
                 JONES                 $18,480.00  B03                         
                 MCCOY                 $18,480.00  B02                         
                 SMITH                 $13,200.00  B14                         
                 Total salary expense for the MIS dept is:     $108,002.00     
                                                                    
     PRODUCTION  BANNING               $29,700.00  A17                         
                 *** WARNING: BANNING 's salary exceeds recommended guidelines.
                 IRVING                $26,862.00  A15                         
                 MCKNIGHT              $16,100.00  B02                         
                 ROMANS                $21,120.00  B04                         
                 SMITH                  $9,500.00  A01                         
                 STEVENS               $11,000.00  A07
                 Total salary expense for the PRODUCTION dept is:     $114,282.00