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.

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

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.


Top of page

x
Lowercase Directory Names and File Names in WebFOCUS From UNIX

When working with WebFOCUS GUI tools that access directories and files from a UNIX system, the WebFOCUS Reporting Server returns lowercase directory names and files, by default. The WebFOCUS GUI tools also create directories and files in lowercase, regardless of the text case specified (for example, lowercase, uppercase, or mixed-case). If the user creates directories or files at the UNIX command level, they must create them in lowercase.


Top of page

x
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 report request can use a Master File that is stored in the application directory or in the WebFOCUS Reporting Server APP PATH or baseapp directory.



x
Procedure: How to Start a Report Request
  1. To create a new procedure:
    • With the Procedures folder highlighted, select New from the File menu.

      or

    • Right-click the Procedures folder and select New from the pop-up menu, then select Procedure.

    The following image shows the Add Procedure dialog box that opens.

  2. Enter a name for the new procedure in the File name field.
  3. Select the tool you want to use from the Create with drop-down list. The options are:

    Procedure Viewer opens the Component Connector toolbox.

    Report Painter enables you to create complex styled reports.

    Composer enables you to design reports, and to coordinate and distribute layouts made up of multiple reports and graphs in a single output file.

    SQL Report Wizard assists you with SQL passthru which allows you to execute SQL code that retrieves data from an RDBMS. You can use the resulting extract file in Report Painter or InfoAssist.

    InfoAssist enables you to create a chart using an easy to use graphical tool.

    Text Editor enables you to create a procedure with code.

    Define Function enables you to create virtual fields.

  4. Click Open.

    If you select Report Painter, Composer, or Text Editor, the selected tool opens.

    If you select Procedure Viewer, the Component Connector toolbox opens. Do the following:

    1. Click a component connector (yellow diamond) at the point where you want to include the report in the procedure, then click the Report button on the component connector toolbar.

      The Open dialog box opens.

    2. Select the Master File you want to report against.
    3. Click Open.

      The tool you selected opens.


Top of page

x
Creating a Report Example

The example in this topic is a simple report request that illustrates some of the basic functions of Developer Studio. 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 Developer Studio. 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:

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.

Top of page

x
Saving a Report

How to:

When you exit a reporting tool, you are prompted to save the report you have created. For details, see Report Painter Basics in the Creating Reports With Report Painter manual for additional save options available in that tool.

When you save a procedure created in Report Painter or the Component Connector toolbox, the entire procedure is saved, not only the report component.



x
Procedure: How to Save a Report

Close the reporting tool and choose Yes to save the changes.

or

Select Save from the File menu. Your report is saved as a component in the procedure in which it was created.

If you add other components to a procedure or edit the report component, you will be prompted to update the procedure (FOCEXEC). Click Yes to save your changes.


WebFOCUS