Adding Grouped Results

A grouping query allows you to identify the fields that group records into sets so that the aggregate functions (SUM, MIN, MAX, etc.) added to columns can present total results for the group. Grouping records into sets can be very useful when you want to create "summary only" queries. In a grouped query you can count records, calculate totals or averages, or find the highest or lowest values within each record set.

Also, you can enable drilldown in grouping queries that can be used as the datasource for interactive charts and graphs in Dashboards.

The process below is the same for new queries, and for modifying existing queries (once opened in the editor).

Steps

1. Once in the Query Editor (see Creating a New Query for information) press Group By button .

   The Group By panel opens on the right.

   

2. Click Choose Column to use the drop-down list to select a column(s) for grouping.

   

   NOTE:

   Depending on the number of columns in the SELECT, you may see a warning about any fields you did not select for grouping. Any columns not added to the GROUP BY, or part of a grouping expression (SUM, AVG, MIN, and so on) will be removed from the SELECT.

   

   1. (optional) Click the Enabled switch in the Drilldownn section to enable Drilldown.

      

   2. Press Lookup to display a list of columns from the selected query files.

      Place a check next to any of the columns you want to make available for drilldown in a dashboard using this query as a datasource.

      

   3. Press OK to see a list of the selected drilldown columns. You can press Lookup to add more, or X to remove.

      

3. Press OK when finished to close the Group By panel.

   You may see a warning about extra columns on the SELECT clause like so:

   

   Press Cancel to add more columns to the GROUP BY.

   Press Do not Add Columns to save the current grouping settings and leave the SELECT with extra columns (which will have to be removed manually).

   Press Add Columns to add any remaining SELECT columns to the GROUP BY.

   TIP:

   This feature is designed to show the detail records for a given summary record. Only fields from the query’s files can be displayed in the drilldown results. For more advanced drilldown capabilities, see Advanced Drilldown.

   The editor displays the selected grouping column(s) in the Preview section.

   

Grouped results can be filtered by creating a Having expression. See the topic Conditions for Grouped Results for more on this.

 

Related Topics