Create Tabular Reports
The Tabular report presentation can contain data fields, static fields, and special fields (date and time, page numbers). When you select the Tabular presentation type, Reporter creates a report that includes the following:
• Report header
• Detail page
• Report footer
For guidelines and restrictions that apply when working with report sections, see
Report Section Types.
After you have selected document properties and chosen page configuration options, the next step in designing a Tabular report is to specify the report data by defining tables and their relationships.
To begin creating a Tabular report
In the Reporter Document Properties dialog, click the Tabular presentation icon in the Page Configuration edit control, and then click Query Definition.
Alternatively, from the Reporter Edit menu, select Query, Edit Query.
How You Can Define a Query
The first step in defining a query is to access the Query Editor. In the Query Editor, you must select the tables you want to include in your query.
The Query Editor's Edit menu also provides two commands that let you qualify table names:
• Add Schema to Query Table
• Remove Schema from Query Table
A Where Clause Editor can be accessed from the Query Editor, which lets you add where clause restrictions.
After tables are selected to include in the query and their master-detail status designated, you access the Join Definition dialog to specify table joins.
If you want, you can use the Sort Columns dialog to specify how you want rows of data ordered.
Each of these steps and procedures is described in the sections that follow.
Define a Query
You can define a query in the Reporter Document Properties window (see
Set Report Document Properties). Then, you can specify a query definition for the report and view SQL statements.
To begin defining a query
1. Click Query Definition in the Reporter Document Properties window.
The Query Editor appears. Available tables are shown in the Tables List.
2. Click Edit, View Columns in the Query Editor if you want to view a list of table columns for the highlighted table in the Query Editor's Tables List.
The Table Columns dialog appears, which shows all columns associated with the selected table and lists the total number of columns retrieved for that table.
To specify a query definition for the report
1. Drag and drop the table icons for the tables of your choice to the Query Definition pane.
Alternatively, double-click the items in the Tables List that you want to include in the report query.
The table name is highlighted in the Table List, and an x appears in the toggle field to the right of the table name.
Table names you select appear as icons in the Query Definition pane.
When tables are placed in the Query Definition pane, they are designated as Primary or Secondary <aster (M, m) or Primary or Secondary Detail (D, d), as indicated by the icons—M,m, D,d—located to the right of the Alias field.
With the exception of Primary Master, you can change these designations by clicking the table icon then clicking the desired Secondary Master or Primary or Secondary Detail icon.
You may specify more than one table, but only one table can be a Master table and only one can be a Detail table. For more information, see
Query Editor Fields.
2. Click the appropriate join icon in the Query Definition pane to access the Reporter Join Query Editor. For more information, see
Create Table Join Definitions.
Important! Table names cannot be changed after document creation. Tables may be added or deleted; deleting a table removes associated fields from the report.
To view SQL statements
Select at least one table and then, in the Query Editor, click Edit, View SQL Code.
Note: You can also access the SQL Code Viewer by clicking SQL Code in the Reporter query toolbar.
The SQL Code Viewer appears.
The Query Editor supports correlation names for tables, permitting you to specify auto joins. By default, the correlation name is the table name; you can modify this in the Alias entry field.
After the relations are generated, you can define table joins or generate them automatically by checking the Automatic Joins Research command on the Query Editor Edit menu.
Query Editor Fields
The following are descriptions of the fields in the Query Editor:
Tables List
Displays the names of tables available for inclusion in the report document. The tables you select appear as icons in the Query Definition pane.
Table Name
Indicates the name of the selected table
Alias
Specifies an optional alternate name of the table selected
Primary Master (M)
Specifies the primary master table of the query. Only one M table is permitted. Clicking the M button creates a primary Master-Detail relationship between the tables you have selected.
Secondary Master (m)
Specifies the secondary master table of the query. Multiple m tables are permitted (1:1 relationship with M table). Clicking the m button creates a secondary Master-Detail relationship between the tables you have selected.
Primary Detail (D)
Specifies the primary detail table of the query. Only one D table is permitted (1:N relationship with M table). Clicking the D button creates a primary Detail-Master relationship between the tables you have selected.
Secondary Detail (d)
Specifies the secondary detail table of the query. Multiple d tables are permitted (1:1 relationship with D table). Clicking the d button creates a secondary Detail-Master relationship between the tables you have selected.
Query Definition Pane
Displays icons representing the tables you have selected; relationships between tables are shown as labeled connectors. A “1” on the connector between two tables indicates a one-to-one relationship; an “N” indicates a one-to-many relationship.
Pattern
Defines a pattern-matching string to restrict the names of tables to be listed; for example, s% would retrieve only table names beginning with “s”
Table
Specifies the name of selected table and its owner
Qualify Table Names
Table names can be qualified by a schema name. (A schema is a named collection of tables in a database.) Reporter can display multiple identical table names; therefore, it may be necessary to prefix the schema to the table name to clarify which table is being referenced.
You can choose whether you want to use schemas. Using schemas means that you can specify a particular table that will not be dependent on the user or DBA. You can also include in a query tables of the same name but with different owners.
To select whether a schema (owner) should be used to qualify the selected table name in the query
In the Query Editor, click Edit, Add Schema to Query Table or Edit, Remove Schema from Query Table.
How You Can Use Global Temporary Tables and Variable Table Names
Reporter supports the use of global temporary tables and variable table names, which may be passed as parameters to the report. This is achieved through the use of example tables. An example table must exist in the design environment while the report is being specified but need not be kept after report definition is complete. An example table is required to contain only runtime-compatible column definitions that are required by the report. To set an example table, a table must be selected in the Query Definition pane.
A table that takes part in a query may be one of three example types:
Self
Specifies a runtime table of the same name. This may include a schema.
Temporary
Specifies a global temporary table that may already exist in the session that calls the report (in which case the same database session must be used by the report), or may be created during the setup phase of the report.
Reporter will automatically refer to the table as session.tablename. These tables may be created at runtime by the caller in the same session that the report will be run or may be created as part of Report/Setup using the OpenROAD syntax for declare global temporary table.
To drop a temporary table explicitly, use drop 'session.tablename'.
Parameter
Specifies tablename as a report variable that must be initialized at runtime. The table name passed in may be that of a temporary table. It may have a default value.
When a table is specified as a parameter you will be prompted to create a variable through the Variables List dialog (see
Create a Variable Field). Variables that are associated with tables are always of type varchar and are report parameters.
• A temporary table may be a parameter. In this case define the example type as Parameter and pass the table name as session.tablename.
• Parameter tables may not have a schema attached within the Query Editor. If required, pass the schema as part of the tablename.
• In the graphical display area of the Query Editor, tables that are examples of temporary tables are prefixed with (T), and parameter examples with (P).
• Quotes ('') are required around the table name for the drop statement.
• When the example type is parameter, this option is available to view or edit the associated parameter variable.
How You Can Change the Query Table Owner
When you are defining or editing a query, multiple tables having the same name—but different owners—may appear. Reporter issues a warning when it detects a table‑owner pair that is not a part of the current database. Mismatches are flagged with an asterisk (*), and the table name appears in CC_RED.
To prevent any problems related to mismatched ownership of tables, we recommend that you reconcile tables in the query with the current database. Mismatches can happen if a report is imported from a different database or if tables existing at the time the report was first created have been dropped.
Auto Search for Table Owners
Reporter enables the Auto Search for Table Owners command when it detects a mismatch. (You can manually enable this option by clicking Edit, Auto Search for Table Owners in the Query Editor.)
Auto Search tries to match tables with appropriate owners—Current User, DBA, someone else—in that order. Table names of successfully matched query tables appear in CC_BLUE. These matches, however, are only Reporter's suggestions; they cannot be saved directly. Permanent reassignment of owners can be accomplished only by using the Change Query Table Owner option.
Change Query Table Owners
The Change Query Table Owner option lets you assign new owners to tables defined in a query.
To assign new owners to tables defined in a query
1. Select a table in the Query Definition pane of the Query Editor.
2. Select a table with the same name from the Tables List.
3. Click Edit, Change Query Table Owner in the Query Editor.
Important! Changing ownership of a query table may invalidate existing join definitions. It is the user's responsibility to verify that existing join definitions are still valid.
Add Restrictions to the Where Clause
The Where Clause Editor lets you define the selection criteria (that is, restrict the criteria) for the tables you want the database to access. You can create or delete where clause restrictions.
To create or edit a where clause restriction in the Query Editor
1. Click Edit, Where Clause.
The Where Clause Editor appears.
In the Where Clause Editor, toolbar buttons and corresponding menu commands are available for creating, editing, and deleting a where clause restriction.
Toolbar buttons and corresponding menu toggle commands are also available for viewing the where clause restriction text, for viewing the where clause restriction graphically, and for viewing SQL code for the where clause.
2. Click Edit, Graphic in the Where Clause Editor.
3. Double-click the graphic (rectangular box) in the graphic area of the Where Clause Editor.
The Reporter Query Columns dialog appears.
4. Select a table name and a column name.
5. Click Select.
The Reporter Query Restriction dialog appears.
The Reporter Query Restriction dialog shows the selected table/column name and lets you add or edit a query restriction.
6. If required, change the AND to OR by clicking the down arrow for the drop-down box on the upper left side of the dialog.
7. If required, change the operator by clicking the down arrow for the drop-down box on the left side of the dialog.
8. Type the text for the desired restriction in the entry field on the right.
9. Click OK.
The Where Clause Editor appears, showing both the text for the restriction you entered and its graphic representation.
10. Repeat steps 3 through 9 to add other restrictions to the where clause.
11. Click Save to save your work.
To delete a where clause restriction
1. Click the box associated with the restriction you want to delete in the graphic area.
2. Click Edit, Delete in the Where Clause Editor.
To close the Where Clause Editor
Click Close in the upper right corner of the window.
A confirmation pop-up appears.
Create Table Join Definitions
The Edit menu in the Reporter Join Query Editor lets you create, edit, or delete table join definitions.
To create a table join definition
1. Click a join icon in the Query Definition pane of the Query Editor.
The Reporter Join Query Editor appears—unless no joins are currently defined, in which case, OpenROAD automatically opens the Join Definition dialog.
2. Click Edit, Create in the Reporter Join Query Editor.
The Reporter Join Definition dialog appears.
To select columns for the table join definition
1. Click the folder icon to the right of the Column field associated with the Left Table.
The Select a Column dialog appears.
2. Select the name of the table in the Table Name list for which you want to specify a column.
3. Select the name of the desired column in the Column Name list.
4. Click Select (or press Enter) to add the selected column to the table join definition.
Repeat this procedure to add other columns to the table join definition.
In the Reporter Join Query Editor, the column selections are displayed under these headings: Left Table, Left Column, Right Table, and Right Column.
5. Click Close.
If all required joins between tables have not been defined, a confirmation pop-up dialog appears.
To edit joins
Click Edit, Edit Joins in the Query Editor.
You can modify the default joins, created when you added a table, to make them user-defined joins. User-defined joins are not recalculated automatically. Click Edit, Joins Research in the Query Editor to perform this operation. (The user-defined joins then become default joins.)
Specify Sort Columns
Sort columns may be table columns or calculated fields created in the document. Sort columns determine the ordering of data in a report. When more than one sort column is specified, the higher on the list an item appears, the more significant the item; for example, if you sort by student name, “course” generates output ordered by student name and, within each name, by course.
To specify table sort columns
1. Click Edit, Sort in the Query Editor.
Note: You can also access the Reporter Sort Columns dialog by clicking Edit, Query Edit Sort.
The Reporter Sort Columns dialog appears.
• In the Show field, All Columns in Selected Tables is displayed. You can click the down arrow to select Column/Expression in Select Clause.
• Selecting All Columns in Selected Tables lets you select table and column names. Selecting Column/Expression in Select Clause only lets you choose alias names.
2. Select a table name.
The columns associated with the selected table appear in the Column Name field.
If you selected Column/Expression in Select Clause, select an alias name and skip to Step 4.
3. Select the column name that you want to add to sort columns.
4. Click Add to Sort Columns.
The selected column (or alias) name appears in the Alias field in the lower section of the dialog.
5. Repeat steps 2 through 4 for each column you want to add to sort columns.
If you want to remove any columns from the list, select the column name and click Remove from Sort Columns.
If you attempt to remove a column that has been defined as a break column, a Message pop-up appears.
Click OK to close the Message pop-up.
6. Click the up or down arrows to change the order of the sort columns.
Clicking the up arrow moves the selected item up one line. Clicking the down arrow moves the selected item down one line.
7. Click the toggle field under Order Type to select ascending or descending sort order for each sort column.
The default is asc (ascending).
8. Click OK to return to the Reporter Join Query Editor.