Group criteria page of the SQL designer
The SQL designer Group Criteria page enables you to specify group selection criteria used in the HAVING clause that the SQL designer adds to the query. A HAVING clause selects and rejects row groups.
The group criteria can be a simple expression, an SQL expression, or a two summary expression. Right-click in the grid and select the appropriate criteria type from the context menu.
Adding group selection criteria
To change the type of selection criteria, right-click in the grid on the Group Criteria page and select one of the following from the grid’s context menu.
SQL Expression
Enter an SQL expression directly. For example,
SUM (Qty * Price) > 1000
Simple Having Summary Expression
A Simple Having Summary Expression summarizes the comparison of two fields for each row of data.
When defining a Simple Having Summary Expression the grid has four columns: Summary, Field, Operator, and Field.
A Field may be dragged from a table window in the table pane and dropped onto a Field column.
To enter a Simple Having Summary Expression,
Select the appropriate summary value from the Summary column.
Enter the first field you wish the summary to compare. This can be done by either dragging a field from a table window in the table pane and dropping it onto the Field column or selecting a field from the drop-down list box.
Select the appropriate operator from the Compare column drop-down list box.
Enter the second field you wish the summary to compare as described in step 2.
Two Summary Expression
A Two Summary Expression selects and rejects row groups based on the result of the comparison of two summaries.
When defining a Two Summary Expression, the grid has five columns: Summary, Field, Operator, Summary, and Field.
A Field may be dragged from a table window in the table pane and dropped onto a Field column.
To enter a Two Summary Expression grouping criteria:
Select the appropriate summary value from the Summary column for the first summary to compare.
Enter the first field you wish to summarize for the comparison. This can be done by either dragging a field from a table window in the table pane and dropping it onto the Field column or selecting a field from the drop-down list box.
Select the appropriate operator from the Operator column drop-down list box. This operator defines the type of comparison between the two summaries.
Select the appropriate summary value from the Summary column for the second summary to compare.
Enter the second field you wish to summarize for the comparison as described in step 2.
Combining group criteria
To specify how the selection criteria are combined to form more complex selection criteria, select from the drop-down list box above the criteria grid.
ALL Specifies that all selection criteria in the grid must be true for the combined criteria to be true.
ANY Specifies that at least one of the selection criteria in the grid must be true for the combined criteria to be true.
NONE Specifies that all of the selection criteria in the grid must be false for the combined criteria to be true.
NOT ALL Specifies that at least on of the selection criteria in the grid must be false for the combined criteria to be true.
Deleting a row
After selecting the row to delete, right-click and choose Delete Row from the context menu.