Creating a Single-Row Detail Query

A single-row detail query is a special form of a mutli-row detail query. The model is still the same—the detail query executes for every row in the master query. However, a single-row detail query differs from a multi-row detail query in the following ways:

A single-row detail query does not have its own layout. Instead, fields from the query are placed directly on the layout for the master query.
A single-row detail query can return only one row. The row returned is a logical extension of the row produced by the master query, so the relationship must be one-to-one.
A single-row detail query cannot be nested to multiple levels – it cannot have any detail queries of its own.

The process for creating a single-row detail query is the same as the process for creating a multi-row-detail query, except that you do not check the box next to Assign a layout to this query in the Query Builder.

To create a single-row detail query:

Display the layout for the master query in the Layout window.
Click on the Object Toolbar, select Insert, then Field, or right-click Fields in the Object Explorer and select Insert.
The Report Fields dialog box is displayed with the master query and any detail queries listed under the Database tab.
Click New and choose Detail Query.
On the first Query Builder page, enter a name for the detail query.
For example, you could name the detail query for customer sales forecasts Forecast.
Clear the box next to Assign a layout to this query.
This is different than multi-row detail queries that have their own layout and return more than one row.
On the subsequent Query Builder pages, select the data for the detail query.
After you create the single-row detail query, it appears in the Database tab in the Report Fields dialog box.
Drag the desired fields into the layout for the master query.
Since single-row detail queries do not have their own layout, the name of the query and the fields in the query do not appear in the Queries or Fields section of the Layout window. As a result, you must drag the fields into the layout for the master query from the Report Fields dialog box.
Format the objects as desired.
Note:
For an example of a single-row detail query that does not have a layout of its own, review the correlate_subquery.srm in the Samples folder.

The default path for this srm file is: C:\Hyperion\products\biplus\bin\SQR\Studio\Samples\correlate_subquery.srm. If you placed SQR Production Reporting Studio in a destination other than the default destination, then look for the Samples folder and report there.

In the sample file, the detail query is used to return the average salary for the region. This value has been placed in the Group Header.