1.7 Data Model Objects
The topics in this section discuss basic concepts of data model objects; for more advanced concepts, see Section 2.3, "Data Model Objects".
1.7.1 About queries
Queries provide the data for your report. You create a query using the Report Wizard, Data Wizard, or manually using the query tools in the Data Model tool palette. Queries can select data from any data source (Oracle, XML, JDBC, Text, or your own data source that you can access through the pluggable data source (PDS) API). You can also use a REF CURSOR to create queries (see Section 2.6.10, "About REF CURSOR queries").
Reports built using one query are the simplest reports. The most popular formats for single-query reports are tabular, mailing label, form letter, and break (either group above or group left). In one report, you can display one query's data any number of times, even in different formats. You can query data without including it in the report output. This is useful for establishing relationships between multiple queries, performing calculations, and so on.
The figure below shows the data of one query formatted various ways.
Figure 1-10 Formatting the same data in different ways
Description of "Figure 1-10 Formatting the same data in different ways"
Notice that the last report style (group left break) seems to use an entirely different query for its data than the first three. However, the data it displays was hidden in the other reports, and vice versa. With Oracle Reports Builder, you can query data without including it in the report output. This is useful for establishing relationships between multiple queries, performing calculations, and so on.
A report may contain any number of queries. Multiquery reports are useful when you want to:
-
produce multipart unrelated query reports.
-
produce multipart related query reports.
-
make your queries easier to maintain (often a report with a complex query containing embedded
SELECTstatements and joins can also be created with multiple simple queries that are linked; the latter are often easier for others to understand and maintain). -
display the same data twice in a report with different sorting criteria.
If you create a report with multiple queries, you can either leave the queries unrelated, or establish a relationship between them using a data link.
Multipart unrelated query reports
If you do not link the queries, you will produce a multipart unrelated query report (commonly called a master/master report). These types of reports display lists of unrelated data. For example, in the report below, one query selects products and another selects customers. Notice that there is no relationship between the products and customers.
Multipart related query reports
In many reports, the data fetched for one part of the report is determined by the data fetched for another part. This is termed a "master/detail," or "parent/child," relationship, and is defined with a data link between two queries. When you run a master/detail report, each row of the master (or parent) query will cause the detail (or child) query to be executed, retrieving only matching rows.
-
External queries are no longer supported.
-
Reduce the number of queries in your report as much as possible. In general, the fewer queries you have, the faster your report will run. While multiquery data models are often easier to understand, single-query data models tend to execute more quickly.
-
Queries are processed on the server.
-
To restrict the number of pages that a certain user can produce, insert a record into the PRODUCT_PROFILE table for that user.
-
The only times you should use multiquery data models are:
-
when you're fetching many large columns from the parent and only a few small columns from the child.
-
when you're trying to do things that the query type, such as a SQL query, does not support directly (for example, multiway outer join).
-
when you have complex views (for example, distributed queries or GROUP BY queries).
-
when you need but do not have or want to use a view.
-
For a single-query report, Oracle Reports Builder opens only one cursor to fetch all of the master and detail records. For a two-query report, Oracle Reports Builder opens two cursors--one for each query--after appending the detail query's link to the WHERE clause of the detail query. Therefore, for each master record fetched in the master query, Oracle Reports Builder must rebind, execute, and fetch data from the detail query.
1.7.2 About groups
Groups are created to organize the columns in your report. Groups can do two things: separate a query's data into sets, and filter a query's data.
When you create a query, Oracle Reports Builder automatically creates a group that contains the columns selected by the query. You create additional groups to produce break levels in the report, either manually in the Data Model view or by using the Report Wizard to create a group above or group left report.
Create groups when you want to treat some columns differently than others. For example, you create groups to:
-
produce subtotals (that is, totals at a more granular level).
-
create breaks or cross products in your report output.
With the exception of cross-product groups, all user-created groups are called break groups.
You create break groups to produce subtotals, print columns in a different direction, create breaks, and so on. A break group suppresses duplicate values in sequential records. For example, Oracle Reports Builder can select the department number for each record; however, the duplicate department numbers are not printed.
You create cross-product groups to perform mathematical cross products, which are generally used to create matrix reports.
Filters enable you to conditionally remove records selected by your queries. Groups can have two types of filters:
-
Oracle Reports Builder packaged filters:
-
First, to display only the first n records for the group (for example, the first 5 records)
-
Last, to display only the last n records for the group
-
-
User-created filters, using PL/SQL.
Section 2.3.6, "About links versus groups"
Section 2.6.9, "About group filters"
Section 1.3.2, "About group above reports"
Section 1.3.3, "About group left reports"
Section 2.1.8, "About matrix with group reports"
Section 4.5.1, "Creating a report"
Section 4.5.2, "Creating a multiquery group above report"
1.7.3 About database columns
A database column represents a column that is selected by the query, containing the data values for a report. For each column that you select in your query, Oracle Reports Builder automatically creates a column in the data model of your report. If you want to perform summaries and computations on database column values, you can create new columns manually in the Data Model view (for summary and formula columns) or by using the Report Wizard (for summary columns). You can also reassign one or more columns to a group or groups you have created.
In addition to the traditional column types (for example, date, number, character), Oracle Reports Builder also supports graphics or images columns, which are columns whose values can be:
-
graphics or images stored directly in the database. Such a column is usually of data type LONG or LONG RAW and contains a graphics or images. In this case, specify the graphic's format in the column's properties.
-
file names. The values of such a column are pointers to files stored in the operating system. In this case, specify in the column's properties that its values are file contents (Read from File) and the format of the files. Many graphics or images formats are supported, including BMP, CALS, CGM, GIF, JFIF, PCD, PCX, PICT, RAS, TIFF, and so on.
Section 4.8.1, "Creating a query"
Section 4.8.5, "Selecting an image from the database"
Section 2.3.4, "About referencing columns and parameters"
Section 2.3.1, "About summary columns"
1.7.4 About data links
A data link (or parent-child relationship) relates the results of multiple queries. A data link can establish these relationships:
-
between one query's column and another query's column.
-
between one query's group and another query's group (this is useful when you want the child query to know about its parent's data).
A data link causes the child query to be executed once for each instance of its parent group. The child query is executed with the values of the primary key used by the parent.
When a report with a data link is executed, the data link is converted into a SQL clause (as specified in the link's Property Inspector) and appended to the child query if Oracle Reports Builder is able to parse the query.
Important note: If the query cannot be parsed, the query is not modified (for example, Oracle Reports Builder has no way to modify how the data is fetched when the query is defined for a pluggable data source).
Although links are commonly equijoins (for example, WHERE DEPTNO=DEPTNO), you can create links with various SQL clauses (that is, WHERE, HAVING, or START WITH) and conditions. If your database has database constraints, you can create a data link that derives its SQL clause and condition from the constraints. You can view the SELECT statements for the individual parent and child queries in Oracle Reports Builder, but cannot view the SELECT statement that includes the clause created by the data link you define.
For the report below, the following link was defined:
The default group of the master query (containing the columns "Order ID" and "Customer") is the parent group; the detail query (the query to which "Item", "Product", and "Amount" belong) is the child query.
Note:
Oracle Reports Builder does not support data links between queries that contain column objects. If you attempt to create such a link, a message dialog box displays, which enables you to choose whether to create a group-to-group query instead (using the parent groups), or to cancel the operation. If you want to create a link between any type of column and a column object, you can manually modify the SQL statement in the child query, adding in the appropriate WHERE clause based on columns in the master query.
Section 2.3.6, "About links versus groups"
1.7.5 About Query Builder
Query Builder is an easy-to-use data access tool designed for analysts, managers, and other business professionals. It provides a logical and intuitive means to access information from your organization's databases for analysis and reporting.
Query Builder is designed for professionals who do not have a computer programming or database background. Because of its powerful query features and its support of Structured Query Language (SQL) statements, experienced database users and programmers will find that Query Builder serves many of their needs as well.
In Query Builder's graphical Query window, you can specify a request for data from your organization's database(s). The request for data is called a query.
You can use Query Builder to define almost any query that you would build using a SQL SELECT statement. Query Builder automatically generates the appropriate SELECT FROM [table.column] clause based on columns displayed in the Query Builder workspace.