SQL Report Writer


This section will provide a basic understanding of SQL*ReportWriter. This document will briefly cover each main menu choice available to users of SQL*ReportWriter. Most of the information contained in this section is DIRECTLY extracted from ``Introduction to SQL*ReportWriter'', ``Building Reports with SQL*ReportWriter'', and ``SQL*ReportWriter Reference Manual'' and all credit should be given to ORACLE. If you require more detailed information than provided in this section, consult the above stated manuals.

SQL*ReportWriter is a general purpose tool for developing and executing reports, specially designed for application developers who know the SQL language. Information is entered into SQL*ReportWriter through its fill-in-the-form interface. This interface allows you to navigate quickly and easily throughout the report definiton screens.

The best way to learn SQL*ReportWriter is through hands on experience.


Elements of a Report

A SQL*ReportWriter report is made up of the following objects:

Every report contains at least one query, one group, and one field. Each object in a report has a set of attributes, or ``settings'', which provides information about the object. For example, fields have settings for Width and Display Format, among others.

Most objects are initially created by default, and, in many cases, the default settings are sufficient. Simple forms are provided to edit the defaults and to create new objects, such as subtotals and grandtotals.

Every SQL*ReportWriter object (except Report) is owned by another object. For example, a group owns all of the fields within it. An object is not shared with another object; it has one, and only one, owner.



Report Building Process

There are a few simple steps involved in building a report:

SQL*ReportWriter separates the process of data access from report formatting, providing you with an additional degree of freedom.


Creating a Blank Report:

In order to build reports successfully with SQL*ReportWriter, you must follow a few simple steps. First, select the Action choice from the main menu and then select New from the pull-down menu, and then enter a name for your report.

After you have created a blank report, it is helpful to enter a comment describing the purpose of the report, and at the same time define your page size and margins. Select the Report choice on the main menu to access these settings.


Specifying the Data:

The next step is to define one or more queries. Queries enable you to specify the data you plan to use. You can access data from one or more tables residing in one or more databases. You can use multiple queries in a report, and you can create relationships between them.


Specifying the Report Format:

Once queries are defined, you can use groups settings to specify where groups of data from your queries should be placed in your report and/or specify control breaks (or master/detail relationships). You can think of groups as a tool to perform ``coarse'' or overall placement of data in your report. ``Fine tuning'' can be done using the field and text objects described below.

The next step in building reports is to modify the fields. For each column in the database that you selected, a field is created. By default, each field is made up of two parts: a label and the data of the field. You can change the default field label to any name, using any capitalization you wish; you can change the default data format to any format you desire.

The final step that is used to build a report is to modify the text. Using the text object you can change the positioning or appearance of the fields. You can even highlight parts of field labels. You can also move or highlight one, or any number of fields. In the text object, you can add your own text to appear in a report. The text that you create is called ``boilerplate text''. You can reposition your boilerplate text, and any part(s) of it.


Starting SQL*ReportWriter

SQL*ReportWriter can be invoked from many different windowing systems and each operates differently.

The SQLREP command invokes SQL*ReportWriter for defining and maintaining reports. To interactively view your command line options, type sqlrep ? or sqlrep ``?''.

If you are using ``openwin'' then you can simply invoke SQL*ReportWriter as follows:

          sqlrep /
            OR
          sqlrep / TERM=srw_sun

If you are using ``xterm'' then you MUST invoke another xterm with the sf options (to use the sun function key map) as follows:

          xterm -sf &

Now you can start sqlrep as in openwin.

Current key mappings for SQL*ReportWriter functions can be obtained at any time by pressing [Esc]k. You can them find out how to navigate your way through SQL*ReportWriter. For example, in the sun environment, [Accept] in SQL*ReportWriter is obtained by pressing F7, [Undo] is obtained by pressing F9 F9.

Screen Layout

The Main Menu appears when SQL*ReportWriter is invoked, when [Accept] is pressed from any setting screen or menu, or when [Menu] is pressed.

The status line displays the following information:

Display the List of values for a field by placing the cursor in that field and pressing [List]. Scroll through the list with [Next Choice] and [Previous Choice] or [Scroll Up] and [Scroll Down], and pick one of the values by placing the cursor on the desired value and pressing [Select].

To get help for any part of SQL*ReportWriter, place the cursor on any field or menu choice and press [Help]. A screen of information is displayed appropriate to the current setting or choice. Occasionally, the information is displayed on more than one screen: press [Scroll Up] or [Scroll Down] to move from one screen to another within a topic. The help system is organized by chapters, and is reflected in the on-line Table of Contents.


Action Choice:

The Action Menu contains a list of options which operate on report definitions, including: New, Open, Copy, Rename, Drop, Execute, Generate and Quit.

Each option, except Quit, has an associated dialog box for specifying the report definition to work on.

NEW: Creates a new report definition. The default margins are set on the Report Screen, default Report and Page text objects are created (blank) on the Text Screen, and system parameters for DESTYPE, DESNAME, DESFORMAT, COPIES, CURRENCY, THOUSANDS, and DECIMAL are automatically created on the parameter screen.

You must enter the name for the report. You can check the List of values for a list of your own reports. In this context this is actually the list of invalid values, i.e., report names you cannot use for new reports.

OPEN: Opens an existing report definition for editing. You must enter the name of the report to open by either typing it in or choosing it from List of values, which displays the nemes of your own reports.

COPY: Creates a new report by copying an existing report definition.

RENAME: Changes the name of an existing report definition.

DROP: Deletes the specified report definition.

EXECUTE: Generates and runs an existing report.

GENERATE: Creates a runfile for an existing report.

QUIT: Exits to the operating system.


Query Choice:

Queries define the data retrieved from the database. Each query consists of:

To insert the first query, enter a name for the query in the Query Name entry area, press [Next Field], and then enter the query in the scrollable, multi-line entry area labelled SELECT Statement.

To insert an additional query, press [Insert Record Below] or [Insert Record Above] and repeat the step to insert the first query. To the right of the query name a display shows the number of the current query out of the total number of queries entered in the report. Inserting a query between two existing queries will not cause the new query's fields to appear between the fields that belong to the existing queries (on the Field Screens). You must move the new query's group between the existing queries' groups to place the new fields between the existing fields.

To enable you to run a report after specifying only one query, SQL*ReportWriter generates several default objects for each query:

Enter a SELECT statement; all features of the SELECT Statement are supported, except the FOR UPDATE clause.

Parent-Child Relationships allow you to relate the results of multiple queries. When you specify a parent query and pairs of matching columns, SQL*ReprtWriter retrieves only the rows in the child query that match the rows in the parent.


Group Screens:

Groups, like other report objects, can be inserted, deleted, moved, and renamed (use [Show Keys]). A group must be created on the Group Screens before it can be referenced elsewhere in the report definition. SQL*ReportWriter automatically creates a group for each query you enter. These groups are named by attaching a ``G_'' to the guery name. The only exception is when query names start with a ``Q_''. In that case, SQL*ReportWriter removes the ``Q_'' and replaces it with ``G_'' when naming the associated group.

You can arrange groups in any order on the Group Screens. When you exit this screen, SQL*ReportWriter re-sorts them according to the following rules, if necessary:


Group Screen One is used to specify:

The Group Name. Create new groups by inserting records, entering new group names, and assigning fields to them. A prefix of G_ is recommended, but not required.

Query specifies the query with which the group is associated. You can choose a query from the List of values, which shows the list of valid query names. The query you enter must already exist.

Print Direction specifies the direction in which successive records of the group appear. The default is Down.

Matrix Group specifies that the indicated group is part of a matrix report. X indicates that the group is a matrix group. For more information on matrix reports see ``Building Reports with SQL*ReportWriter Version 1.1''.

Page Break allows pagination control for each group.


Group Screen Two is used to specify:

Relative Position specifies the position of the group in relation to the previous group.

Lines Before specifies the number of blank lines before a group relative to the prior group or the top margin. Blank implies 2 for the second and subsequent Across and Across/Down groups, and 0 for all other groups. Any number between 0 and the number of lines remaining before the bottom margin is acceptable.

Spaces Before specifies the number of blank spaces before a group relative to the prior group or the left margin. Blank implies 2 for the second and subsequent Down and Down/Across groups, and 0 for all other groups. Any number between 0 and the number of spaces remaining before the right margin is acceptable.

Record Spacing specifies the spacing between each record in the group. Blank implies 0 lines for Down and Down/Across groups and 2 spaces for Across and Across/Down groups.

Field Spacing specifies the spacing between each field in the group. Blank implies 2 spaces for Down and Down/Across groups and 0 lines for Across and Across/Down groups. Any number greater than 0 is acceptable.

Fields Across specifes the maximum number of fields of the group that will print on a single line of a panel for Down and Down/Across groups, or within a single column area of a panel for Across and Across/Down groups. This setting applies to the fields from one record within a single group, not to fields from multiple groups on the same page or to multiple records from the same group.


Group Screen Three is used to specify:

Multi-Panel specifies whether or not to keep all fields in a group on the same panel.

Label Position specifies whether to place the field labels in the Column Heading or Body. This setting works in conjuction with the Print Direction flag. Default behavior is dependent on the Print Direction:

Highlight is used to define consistent highlighting for all fields and labels in a group. Field and Label settings are used to achieve this. The Underline highlighting attribute means that SQL*ReportWriter will attempt to use an ``underline'' attribute, not a row of hyphens beneath the field.

Field specifes the highlighting style for the fields in the group. This setting affects all fields in a group. Specify individual highlighting sytles or any combination of styles: Normal, Underline, Reverse, Bold, Underline/Reverse, Underline/Bold, Reverse/Bold, and Underline/Reverse/Bold.

Label specifies the highlighting style for the field labels in the group. This setting affects all labels in the group. Blank specifies normal typeface. The List of values are the same as the Field.


Field Screens:

Fields, like groups and summaries, can be inserted, deleted, moved, and renamed (use [Show Keys] function). By default, each column or expression from each query in a report appears once on these screens. You can replace the value from the query with a computed value by supplying a function and reset group on Field Screen Three.


Field Screen One is used to specify:

The Field Name. SQL*ReportWriter uses the column name, alias, or text of the source column or expression as the name for default fields. If the resulting name is not unique, appends a ``2'' for the second occurrence of the name and so on.

Source specifies where the field's data comes from:

For default fields, the column name, alias, or SQL expression from the query is used. SQL*ReportWriter prefixes non-unique column names and aliases with the name of the query. For more information on system variables and user exits, see ``SQL*ReportWriter Reference Manual''.

Group specifies the name of the group to which the field belongs. The default is the group for the associated query from which the field is being retrieved. You can assign a field to a group by selecting a group from the List of values.

Field Label specifies the text of the field label. The default field label for a field that is automatically created from a query is the text of the column specified in the SELECT statement, modified following the same rules used for generating field names. You can change the label by typing over the default or remove it by deleting all the characters.


Field Screen Two is used to specify:

Data Type Lists, the datatype for the field. By default, SQL*ReportWriter defines the datatype for the field based on the datatype of the SELECT statement expression for the field.

Field Width specifies the width of each field. The width of the Source column is derived from the query. Valid numbers are 0 through 999.

Display Format specifies a mask for altering the display of date and number values. If the actual value is longer than the specified display format, the value will appear as a string of asterisks in the generated report. The date formats offered in SQL*ReportWriter are those supported by the TO_CHAR function in SQL.

Relative Position specifies the position of the field, depending upon the group. If the previous field is in the same group, the position is relative to the previous field. If the field is the first in the group, the position is relative to the left margin of the group. By default, Blank implies Right if the Print Direction is Down or Down/Across or Below if the Print Direction is Across or Across/Down.

Lines Before specifies the number of lines before a field. Blank implies no lines before the field. Any number between zero and the number of lines left on the page, minus the number of lines needed by the field for Across and Across/Down groups is valid.

Spaces Before specifies the number of spaces before a field. Blank implies the number of spaces set for field spacing within the group. Any number between zero and the number of spaces left on the page, minus the number of spaces needed by the field for Down and Down/Across groups is valid.


Field Screen Three is used to specify:

Align specifies the justification of the field. Each datatype has its own default value, Left for character and date values and Right for number values.

Skip specifies that SQL*ReportWriter should not print the indicated field. You skip fields when they are needed for use in computed fields and/or summaries, but you do not want them to appear in the report. By default, Blank implies that the field will be displayed. X skips the field in the report output.

Repeat causes the field to appear on all the panels of the page as a label column. This is useful for simulating a spreadsheet style of output. By default, Blank implies that the field will be displayed on the first panel only. X prints the field on all panels.

Computed Value fields show the results of calculations on values after they are used to perform calculations on query results. These calculations augment the kinds of calculations that can be done directly with a SELECT statement.

Function specifies the function to use when computing a value of the field. By default, Blank implies no function is to be computed. Press the [Help Key] or [List of Values] to see the different functions that are available for use.

Reset Group specifies the group at which to reset the computed field. Report may be specified to request that the values be calculated for the report as a whole (i.e., for grand totals). Page may be specified to request that the values be calculated for each page of the report: to achieve page totals. You can specify any valid group name, Report or Page.


Summary Screens:

A summary field contains data derived by SQL*ReportWriter using one of the summary functions. Summary fields are not created by default, and if desired, must be inserted and deleted manually. Multiple summaries can be computed for each field.

Summary Screen One is used to specify:

The Summary Name. Summaries are named so they can be referenced in other parts of the report definition. Summary names must be distinct from the report name, query names, group names, field names, and other summary names.

Field specifies the name of the field being summarized. Summaries can be based on any field that appears on the Field Setting Screen (Choose a field from the List of values, which shows a list of all existing fields).

Function specifies the function used to compute the summary. Press the [Help Key] or [List of Values] to see the different functions that are available for use.

Data Type specifies the data type for the summary field. By default, it is the same as the datatype of the field being summarized. The values are maintained by SQL*ReportWriter.

Width specifies the number of characters the summary will occupy. By default, this is the same as the field being summarized. If the value to be displayed is greater than the width of the summary, SQL*ReportWriter truncates characters and dates or replaces the value with asterisks if the datatype is NUM.

Display Format specifies a mask for the display of date and number values. Press [Help Key] to see valid Date and Number Display Formats.


Summary Screen Two is used to specify:

Print Group determines how often the summary will appear in the report. The summary will print once for each record in the print group. By default, the parent of the group containing the field being summarized, or Report. For running summaries of fields within the top group, it is the top group. A list of values is provided showing the names of all the valid groups, as well as Report. Report prints the summary once at the end of the report.
Note: The Print Group must be above the group containing the field being summarized in the same path, or Report.

Reset Group specifies the name of the group at which the summary resets to zero. Specify the keyword Report to request grand total values for the report as a whole. Specify the keyword Page to request page totals. By default, field is in the higher group, Report. For other fields, the next higher group in the same path. A List of values is provide showing the names of all valid groups in addition to Report and Page.


Text Screen:

The Text Screen allows you to customize your report by editing default text objects and creating your own text.

Text Objects consists of the following:

SQL*ReportWriter creates text for many objects by default, including Column Headings (if the default Label Position is used) and a Body for every group, and Group Footers for summaries. Text for other objects must be created manually.

Some reports are wider than a single page. SQL*ReportWriter creates one panel for each of the overflow pages for default objects. For other objects, panels must be created manually.

Locating Text Objects can be done by pressing [Next Record] or [Previous Record] repeatedly until the object appears. Alternatively, press [Query] to clear the Object and Type fields and to place SQL*ReportWriter into query mode. Enter the name and/or the Type of the object your are trying to locate, and press [Fetch].

Suppressing Printing of a Default Panel can be achieved by deleting all of the characters in all of the panels associated with the object without deleting the panels themselves. You can identify such blank panels because the panel will be numbered and the Status will be ``Edited'', even though no text is shown.

To customize a text object, use [Next Record] or [Previous Record] to locate the panel to be changed and then revise the text. Use the Text editing keys ([Delete Line], etc.) to revise text. Pressing [Delete Record] will cause the entire text object to be restored to its default (i.e., un-edited) state when the report is executed, or when the screen is re-entered.

You can Highlight Text and Fields from the Text Screens by selecting a highlight from the Highlight Text List of values. Embedding Printer Control Codes can also be done on Text Screen. For more information on these topics see ``SQL*ReportWriter Reference Manual''.


Text Screen is used to specify:

The Text Screen is divided into two parts: the top section of the screen describes the different text objects that make up a report, and the bottom section of the screen contains each of the panels that make up the text. Each panel is defined by a panel number and its associated text.

Object specifies the name of the object that owns the text. This field can be entered only when in query mode.

Text Type specifies the type of text associated with report, page, or group objects. This field can only be entered when in query mode.

Status indicates whether the default text has been edited. This will be indicated by Default or Edited and cannot be modified.

Relative Position specifies the location of the text object relative to the object that occurs before it. By default, Blank implies Below for all non-group objects.

Lines Before specifies the number of blank lines before the text. Any number between 1 and thh number of lines remaining on the page, minus the number of lines in the text is acceptable.

Spaces Before specifies the number of spaces before the text. Any number between 1 and the number of spaces remaining on the page, minus the number of characters in the text.

Width specifies the width of the object. Any number between 1 and the width of the report as specified in the Report Settings Screen is valid. This field is optional; if nothing is entered, SQL*ReportWriter will use its own algorithms to compute the width of the object.

Repeat on Page Overflow specifies whether the text should be repeated on the next page if the data overflows. By default an X appears for column heading and body; a blank appears for all other text types.

Justification specifies horizontal alignment for the text. By default Left appears. You can choose one of Left, Center or Right.

Frequency specifies how often the text will be printed. This option is provided to allow column headers to print once per instance of a group, or less frequently. This attribute is only available for column headers.

Panel Number specifies the panel on which to print the text. By default panels are numbered by SQL*ReportWriter.

Text specifies the text that is to appear in the report. You can reference any of the following system variables:


Report Screen:

The Report Screen is used to set parameters that affects the look of the entire report such as the page height and width, margins etc.


Report Screen is used to specify:

Page Height specifies the height of the physical page in lines. The default is 66 lines. You can enter any value between 1 and 999 lines.

Page Width sets the width of the physical page in character spaces. The default is 80 characters. You can enter any value between 1 and 999 characters.

Top Margin and Bottom Margin specifies the number of lines that SQL*ReportWriter skips from the top and bottom of every page. The default is 2 lines. You can enter any value between 1 and 999 lines. The sum of the top and bottom must be less than the height of the page.

Left Margin and Right Margin specifies the number of spaces that SQL*ReportWriter leaves blank at the left and right of every page. The default is 0 spaces. You can enter any value between 1 and 999 spaces. The sum of the left and right must be less than the width of the page.

Parameter Form Title specifies the title of the Run-time Parameter Form. The title appears at the top of the From in reverse video. By default ``Parameter Values'' appears. You can enter any title that is at most 80 characters long (including spaces).

Parameter Form Hint specifies the hint line of the Run-time Parameter Form. The hint line is displayed on the second to last line of your screen. By default ``Enter the desired value for each parameter.'' appears. You can enter any text that is at most 80 characters long (including spaces).

Parameter Form Status specifies the status line information of the Run-time Parameter Form. The status line appears directly below the hint line, on the last line of your screen. By defalut ``Report Name: '' appears. You can enter any text that is at most 50 characters long (including spaces). The Report Name will not appear in the status line unless you enter it there yourself. For example, you could enter ``Report is printed in room 219B. (Report: deptinfo)''.

Comments is used to enter text to describe the report in this area. This text is strictly to assist you, and other users, to remember the purpose of your report. The comments do not appear in the report output and must be less than 32K characters.

Access is used to allow other ORACLE users, when using system-owned SQL*ReportWriter tables, to have access to your report. You can enter each ORACLE username followed by spaces, enter public to give access to all users, j% (% wildcard character) to give access to all users with ORACLE username starting with j.

History displays all information related to the creation of the report such as the Created and Modified dates, version numbers etc.


Parameter Screen:

The Parameter Screen allows you to alter default values, and to create and define new parameters.


Parameter Screen is used to specify:

Parameter Name specifies the name of the parameter to be used in either the SELECT statement or the text object.

Data Type specifies the datatype for the parameter field. By default CHAR is selected. You can choose from CHAR, NUM or DATE.

Width specifies that maximum number of characters for the parameter value that will be used as input for the Run-time Parameter Form and report output. By default 40 characters appears. You can enter any value between 1 and 999 characters.

Default Value specifies the initial value of the parameter. This value is used unless it is overridden on the command line or in the run-time parameter form. By default this is BLANK, implying no default value.

Label specifies the text that appears as a prompt on the run-time parameter form. The maximum width of a Label must be less than or equal to 27 characters.

System Parameters that are found on the parameter screen cannot be deleted, but you can re-order them, relabel them, suppress their display on the Run-time Parameter Form (using SKIP), and change their default values. The System Parametes that appear as default are DESTYPE, DESNAME, DESFORMAT, COPIES, CURRENCY, DECIMAL and THOUSANDS.