GROUP Usage Code

Reports:

The GROUP usage code displays only one line of summary data for each set of values in the column. The summary line can display only values that are the same for each member of the group, such as the value in a control column, or the results of columns whose usage is one of the aggregations.

When you want a report to show a summary line for each group of values in a column, use a query that includes the GROUP BY and ORDER BY SQL clauses. GROUP BY accumulates the results of the query by group; ORDER BY orders the groups. The report then shows exactly as many summary lines as there are different values in the column. Without ORDER BY in the query, the report could show as many summary lines as there are lines in the report.

Using GROUP BY and ORDER BY can also improve the performance of a query.

Rules for using GROUP:

• The query that selects the data must use ORDER BY in SQL. Without ORDER BY, the report can produce unexpected results.
• More than one column can have usage GROUP. If so, a change in value in any column starts a new group. With two usage codes of GROUP, the report could have many more lines of grouped values.
• The report runs but omits all columns with blank usages if all the following are true:
  • One or more columns in a report has usage GROUP
  • Any other column has an aggregation usage
  • Any remaining columns have blank usages
• If any column has usage GROUP and all other columns have blank usage codes, the report omits the column containing the GROUP usage.

Charts:

The effect of GROUP as it is used to format a report is similar to its effect on a chart.