This article describes how to group and aggregate results from your Advanced Search query. Grouping allows you to organize results by one or more columns, while aggregation summarizes grouped data with calculations such as sum or average.
Click HERE to watch a video tutorial for customizing Advanced Search results.
NOTE: You must have at least Enabled permissions to General > Advanced Search to access.
Grouping Date Columns
The three-dot menu for a date column allows additional options for grouping. Grouped date columns can be Ungrouped from this menu or by clicking the X above the grid.
Group by this column – Groups by the full selected date column, similar to dragging a column above the grid. NOTE: This option does not allow further grouping by Year, Month, or Day.
Group by year – Groups the selected date column by Year. Click the column headings to sort ascending or descending.
Group by month - Groups the selected date column by Month. Click the column headings to sort ascending or descending.
Group by day - Groups the selected date column by Date. Click the column headings to sort ascending or descending.
Multiple date groupings can also be created. For example, a Day grouping can be combined with Month or Year, a Month grouping with Day or Year, and a Year grouping with Day or Month.
If a column is grouped by Month and Year, clicking the three-dot menu for one header will ungroup only that column.
Click the caret to see all dates within a grouped month.
Leveled Grouping
Up to three levels of grouping can be added for all Advanced Search reports. This feature is perfect for administrators who run monthly reports and executive summaries where data needs to be aggregated and presented to management.
Columns can be dragged and dropped for a faster grouping experience. To group by a column, click and drag the column title to the space above the grid.
The grid will refresh and group your results by the selected column. To group additional columns, expand the first-level grouping and follow the same drag and drop process.
To remove a grouped column, click the X next to the column title. The order in which columns are added to the Group by section is the order that they will be grouped in the grid. Grouped columns can be rearranged by dragging and positioning in the desired order. To keep your groupings intact, save your Advanced Search report.
NOTE: Grouping and Ungrouping can still be performed from the three-dot menu in the grid.
For example, a report for Value items can be grouped by Case Name and then by Value Code. After grouping by a particular column, click the greater than arrow icon
to drill down to the grouped items for further grouping.
Another example where leveled grouping can be used is when running a query for Cases and grouping by Primary Staff and Class Code.
To remove grouping, navigate to the column that is currently grouped. Click the kebab menu and select Ungroup by this column.
Aggregation
Results can also be aggregated to show different grouped information.
NOTE: At least one column must be grouped before the Aggregation options can be available.
For example, if you perform an Advanced Search for Value, the results grid can show every Value entry in your firm's database. Click the kebab icon
next to a column name to group by a column, such as Case Number. Then, to show the total amount for each Case, click the right arrow
to expand the first group of Value entries. Select the kebab icon for the Total Amount column and click the Aggregation option. Choose how the amount should be calculated, e.g., Sum.
To go one step further, click the kebab icon for the Due column and click the Aggregation option. Choose how the amount should be calculated, e.g., Sum. You can now view the total Value for each case and the amount that is due.
The values of Aggregated columns can be sorted by clicking the column heading.
Another example of using Aggregation sorting can be seen when searching for average Insurance settlements by Insurance Type. In the screen below, we are searching for Insurance items where the Actual Settlement field is not equal to zero AND where the Insurance Type is equal to Liability.
NOTE: If the "Actual Settlement" column does not exist in the grid, it can be added by clicking the Columns button and searching for the column name. Click the checkbox and Save to insert it into the grid.
To remove an aggregation, click the kebab icon for the column you wish to change. Select Aggregation > None.
