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 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. (Except where otherwise noted, see Report Painter Basics in the Creating
Reports With Report Painter manual for details about these and
many other reporting features.)
-
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 column information. WebFOCUS displays the sort field, which is 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 appear when their values change. You can also choose not
to display sort fields.
You can sort information vertically, down
a column, horizontally, or 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 presented
and how it is presented. A report that identifies related groups
of information and draws attention to important facts will be 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.
- 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 from 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.
xStarting a Report Request
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:
- The TABLE FILE filename command
sets the data source for a single request.
- The FILE SET
parameter sets a data source for all requests within a procedure.
For details on the FILE SET parameter, see the Developing Reporting Applications manual.
x
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.
xCompleting 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.
Note: In Developer Studio, the QUIT
and RUN commands are only supported by entering these commands in
the Command Console.
xCreating 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 using Developer
Studio graphical tools, or 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:
The
request processes in the following way:
- 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.
- The DEFINE
command creates a virtual field which extracts the year from the COURSESTART
field in the TRAINING data source.
- The END command
ends the DEFINE command.
- The TABLE command
begins the report request.
- The HEADING
command adds the heading "Education Cost vs. Salary" to the report output.
- 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.
- The COMPUTE
command creates a calculated value using the values that have been
aggregated in the SUM command and sorted with the BY command.
- The BY phrase
sorts the data in the report by the DIV field, and then by the DEPT field.
- The WHERE command
includes only the data that falls in the year 1991.
- The ON TABLE
SUMMARIZE command adds all values in both the EXPENSES and SALARY
columns, and recalculates the Percent column.
- The StyleSheet
information formats the report heading and content.
- The END command
ends the report request.