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,
Oracle Express, Oracle OLAP, 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.
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 querys 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.
Notice that the last report style (group left break) seems to use an entirely different query for its date than the first three. However, the data it displays was hidden in the other reports, and vice versa. With 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 SELECT statements 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.
If you do not link the queries, youll 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.
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, Reports Builder opens only one cursor to fetch all of the master and detail records. For a two-query report, 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, Reports Builder must rebind, execute, and fetch data from the detail query.
Copyright © 1984, 2005, Oracle. All rights reserved.