Regular Subqueries

A regular subquery executes the inner and outer queries once and returns the values from the inner query to the outer query. For example, you might need to find out who sold more than the average of all sales representatives in April. You first use a subquery to define what was the average sales amount in April. This information is supplied to the parent query, which determines which representatives exceeded the average of all sales in April.

  To build a regular subquery:

  1. Select or build a parent query.

  2. Select an item on which to set a filter by:

    • Double-clicking the topic item.

    • Dragging the item from the Content pane to the filter line.

    • Right-clicking an item and selecting filter on the shortcut menu.

      The Filter dialog box is displayed.

  3. Click Advanced.

    The Advanced button toggles the Create Subquery button.

  4. Click Create Subquery.

    A subquery indicator shows that you are working with a subquery and not the parent query, even though the data models of the parent query are displayed in the Content pane (which now has a gray background).

    The Subquery section is considered a child of the parent query and is subordinate to the parent query. It is dependent on the parent query and does not have its own default Query or Results sections. Subqueries can be nested within other subqueries, in which case the first subquery becomes the parent query to the subquery nested inside it.

  5. Build the query.

    Only one item can be on the Request line, but you can add server and local filters, set a filter inside a subquery as a variable, and define data functions and computations as needed.

  6. In the Section pane, click the parent query for the subquery.

    The parent query section is redisplayed.

  7. Click Process to process the parent query and subquery.