The Aggregation table widget gives you the ability to choose how you want to generate rows. You can add as many columns as you need and filter each of them independently. Then, aggregate the data in each of your columns on a row-by-row basis.
The example here uses a GPS event form to report session information for each position in the dataset. We’ve added columns for each metric we’re interested in and averaged them by position (note that it’s easy to use other aggregations instead of a mean). Each column can use separate filters, aggregation types and conditional formatting rulesets.
Use the sidebar to configure the Aggregation table widget
Select the Aggregation table widget to display the settings in the sidebar. These settings include:
- Column summary
- Advanced properties
- Data preview
Use the General properties of the sidebar to add descriptive information for your Aggregation table widget. You can also:
- Choose whether the title of the widget should be displayed. If you enable this, then you can provide a Title and Title icon for the widget and specify the font size, font color and text alignment.
- Choose whether a subtitle for the widget should be displayed. If you enable this, then you can add a Subtitle for the widget and specify the font size, font color and text alignment.
- Specify the Width of the widget within the section where it appears.
- Choose the Background color and Border color for the widget.
Here you can add static informational content which will appear below the title of the widget. This can include text, images, tables, links and other common content tools.
To provide your Aggregation table with a data source, use the Categories properties of the widget to choose a data source and select which field should be used to create the rows in the first column. To customize your categories further, you can:
- Show the first column which contains the category data.
- Choose to Freeze categories when scrolling horizontally within the widget.
- Choose the text alignment and width of the first column of the table, which contains the category data.
- Set Data filters for the data you use to create the categories.
- Choose the category Data type (Number, String or Date).
- Choose whether to show null categories in the Aggregation table.
- Specify whether the category values are derived from a Multiple selection field type.
- Choose how the categories are ordered in the Aggregation table. You can then set the Order direction to be Ascending or Descending.
- Add a Category column title.
- Provide Prefix text or Suffix text for the category values.
- Choose which fields from your data source should appear in the record card if someone selects a category value in the Aggregation table.
Use the Columns properties to select the information you want to aggregate. For each column you add to the Aggregation table, you can:
- Provide a Column title.
- Choose the Data source for the column.
- Set Data Filters for the data that will populate the column.
- Choose the Value field from your data source which will be displayed in the column.
- Choose which field from the category data source should be used to aggregate the column data. Most of the time you will use the same field that is shown in the first column of the Aggregation table.
- Choose the Value data type (Number, String or Date).
- Decide which Aggregation is the most appropriate for the column data.
- Use the Advanced settings for each column to adjust:
- The number of Decimal places shown (for numeric data).
- The Text alignment of the column. This applies to both the header and the data in that column.
- Which fields should appear in the record card.
- Whether to Enable conditional formatting rulesets you have set up in the dashboard. Conditional formatting can be applied on a column by column basis and affects each table cell background and icon. Columns can be conditionally formatted based on any field within the data source by changing the Conditional field setting in the sidebar. If your ruleset includes a range of different colors, you may wish to enable legible foreground color. With this enabled, the dashboard will automatically choose a legible text and icon color, depending on the color of each individual cell in that column.
- The Column width (measured in pixels). If no width is selected, the table will automatically keep each column at an even width. Keep in mind that if you set the width of some columns but not others, the size of the screen the dashboard is being viewed on may impact the way the table will look. If the width of all columns is greater than the screen width when viewing your dashboard, you can scroll sideways to view the entire table.
- Whether the column should be hidden if it does not contain data. Specifically, if no records from the data source match the filter requirements of the column, it will be hidden. However, if one or more records match the filter requirements then the column will remain visible, even if the referenced field is blank.
Use the Column summary properties to aggregate each column in your table. For each column summary you add to the Aggregation table, you can:
- Provide a Row title.
- Choose the Data type for the column.
- Choose the Aggregation.
- Choose whether the column summary is an aggregate of the raw values, or an aggregate of the data displayed in the table.
- Choose whether to Inherit conditional formatting from the table column or disable conditional formatting in the summary cell.
You can Enable avatars for the Aggregation table using the Avatars properties. When you do this, an avatar will be shown in the first column (containing the category data) for each person whose data gets included in any of the aggregated columns. Depending on the number of people you expect to be included in the Aggregation table results, you might want to change the Avatar size and set a Maximum number of avatars.
You can format the appearance of Aggregation table contents using the Style properties. These allow you to adjust:
- The Table font size (px).
- The Cell padding (px). This is the space between the contents of a cell in the table and the border of the cell.
- The Cell border color.
- The Header background color.
- The Header text color.
- The background color for odd-numbered rows in the table. Note that the background color may be overridden if you are using conditional formatting within the table.
- The background color for even-numbered rows in the table. Note that the background color may be overridden if you are using conditional formatting within the table.
- The text color for the contents of the table. It’s important to choose the text color carefully to ensure it’s easy to read against the background colors in the table as well as any colors that are used in the ruleset you’ve created if you apply conditional formatting.
If you want to disable record cards and click-through for the entire Aggregation table, you can use the Interaction properties to do this. Otherwise, you would use the Categories properties to format the record cards for the data in the first column of the table or the Columns properties to format the record cards for the data in each aggregated column of the table.
To learn more about widget interactions, visit the article on customizing widget interactions.
Use the Advanced properties for customizing your table further. Advanced properties for the Aggregation table widget allow you to:
- Freeze the column header row if vertical scrolling is required so that the headers are always visible.
- Specify the Max height (px) of the widget.
- Hide the widget if no data is available (based on data source and filter settings).
The Aggregation table widget has properties that allow you to choose how you want to preview your table. When using the dashboard builder, you’re not able to view real data entered by athletes, coaches or other users of your Smartabase site. So the dashboard builder shows you simulated data in order for you to see how your table might appear.
Use the Trellis properties to conditionally display the Aggregation table multiple times based on a field within a data source. To customize your trellis, you can:
- Set Filters for the data you use to create the trellis.
- Set a Limit of how many multiples of the trellis should be displayed.
- Choose whether to order the trellis by Values, Date of first occurrence, Date of last occurrence or a Custom order. You can then set the order Direction to be Ascending or Descending.
- Choose whether to display the title of each multiple (this is based on the trellis Field selected). If you enable this, then you can specify the font size, font color and text alignment.
To learn more about widget trellising, check out this article.