Creating a Subquery Filter Using a HOLD File

How to:

You can create a subquery using a HOLD file. A subquery is a nested query that is added to the Where clause of an SQL statement. A subquery is valuable because it is highly reusable.

Note: When creating a chart or report in InfoAssist, on the Home tab, in the Design group, you can click Query to create your content in Query mode. This hides the canvas and uses only the Query and Filter panes for content creation. In Query mode, if a subquery is present and there is no ACROSS field in the request, the data is refreshed only at run time. It is not refreshed at design time as you edit your content, improving performance. Since the canvas is hidden at design time, data does not need to be refreshed at that point.

Procedure: How to Create a Subquery Filter Using a HOLD File

This procedure describes how to create a subquery filter using a HOLD file created in the previous procedure.

  1. Build a report.
  2. On the Data tab, in the Filter group, click Filter.

    The Filter dialog box opens.

  3. In the Create a filtering condition dialog box, from the Type drop-down menu, select Subquery as the filter type for the left-most part of the expression.
  4. From the Subquery drop-down menu, select In list as the comparison operator.
  5. From the list of subqueries, select the subquery that was created (in this example, File1) for the right-most part of the expression.

  6. Click OK.

    The report is filtered by the subquery that you created.

    Note: To view the SQL statements generated by the request, go to the Quick Access Toolbar, open the Run drop-down menu, and select SQL Trace.