11 Building a Two-Query Group Report
In this chapter, you will learn about multiquery group reports. By following the steps in this chapter, you can generate the report output shown in Figure 11-1.
Figure 11-1 Group above report output, two queries
Description of "Figure 11-1 Group above report output, two queries"
As you can see above, a two-query group report appears much the same as a single-query group report. Performance is the key issue when contrasting single-query and multiple-query group reports. In most cases, single-query reports will run faster than multiple-query reports. The multiple-query reports are, however, sometimes easier to understand conceptually and easier to maintain. For example, if you are in a situation where only a few users run the report and the report returns a relatively small number of records, you might want to use multiple queries to simplify maintenance and make the data model easier to understand. If you have many users and the report is quite large, then you should try to use a single-query report.
Note:
For more information on streamlining your report's performance, refer to the chapter "Tuning Oracle Reports" in the Oracle Fusion Middleware Publishing Reports to the Web with Oracle Reports Services manual.-
The single and multiple-query group reports look the same but have different data models. A single-query report has multiple groups underneath one query, while a multiple-query report uses one group underneath each of several queries. In the single-query case, the relationship is established by the hierarchy of groups underneath the query. In the multiple-query case, the relationship is established by data links between the groups of different queries.
-
A data link is a data model object that enables you to relate multiple queries. For a simple group report, you relate the two queries using the primary and foreign keys of the tables from which you are selecting data.
-
A primary key is a column for which each value uniquely identifies the record in which it is found. A foreign key is a column which contains the same values as the primary key for another table, and is used to reference records in that table.
-
Linking two tables through primary and foreign keys is similar to specifying a join condition. In fact, the data link causes Reports Builder to create a SQL clause defining a join, based on information specified when creating the link. This clause is added to the child query's
SELECTstatement at run time. -
The join defined by a default data link is an outer join—in addition to returning all rows that satisfy the link's condition, an outer join returns all rows from one table that do not match a row from the second table.
-
This report uses a default group above layout style in which master records display across the page with the labels to the left of their fields and the detail records appear below the master records in standard tabular format.
-
You will deselect one default column to keep it from appearing in the report, change some field widths to ensure the fields fit across the page, and format one of the fields.
-
You will also specify a maximum of one master record per page to ensure that only one master record and its associated detail records are displayed per page of report output.
In this example, you will create a group above report that lists employees with their jobs and salaries by department.
As you build this example report, you will:
-
Create a New Report Manually to create a new, empty report.
-
Create a Data Model with a Data Link between two queries.
-
Format a Field to format monetary values.
To see a sample group above report with two queries, open the examples folder named masterdetail, then open the Oracle Reports example named grp_above.rdf. For details on how to access it, see "Accessing the Example Reports" in the Preface.