If it’s on your form, it can be used in an aggregation
That’s a fancy way of saying that if you have a field on a form that is capturing some values, those values can be grouped and displayed in a key performance indicator (KPI) block on your XForms dashboard. Listboxes, checkboxes, numeric fields, and even text fields can be used in aggregations.
Aggregation types
You can display the following types of aggregations in your KPI dashboard:
- Count (used for grouping “like” values)
- SUM (used to total a numeric value)
- MIN (displays the minimum numeric value)
- MAX (displays the maximum numeric value)
- AVG (averages the numeric values)
The “Count” aggregation is the most common type. It’s sort of like a grouping metric, where like values are grouped, and then dsiplayed in the KPI block. This aggregation type is mostly used for listboxes and checkbox fields (to group checked vs unchecked), but can also be used with text and numeric fields. A word of precaution when using “Count” for text fields: when users fill out plain text fields, its easy to enter something differently for the same thing. For example, one field user might enter “Acme, Inc.” while another might enter “Acme Inc”. The former has a comma and a period, the latter does not. So the system will treat these as two separate values, not the same value.
In the screenshot below, take a look at the 2nd KPI block from the left on the top row. This is an aggregation for Projects. That is, it will display the number of different projects selected from the dropdown menu on the Well Sampling form for the date range selected in the “Period” selector in the top block of the page. Notice a small arrow (–>). This tiny white arrow denotes that this is a COUNT aggregation. When you click on the block, the pie chart to the right will reload and display the different values for that block. In the example below, it displays the 12 different projects for the time period selected. Click on any pie chart slice or project name below the pie chart, and you will be taken to the Forms screen filtered for that specific project.
Here’s what this looks like live:
SUM, MIN, MAX, and AVG are used solely for numeric fields. SUM displays the sum total of the values in the field for all the submitted forms in the range of forms selected. MIN displays the minimum numeric value in the field for all the submitted forms in the range of forms selected. MAX displays the maximum value in the field for all the submitted forms in the range of forms selected.
What about table grids?
Since table grids have multiple rows that often are dynamic, displaying specific cells in the dashboard KPIs is currently not possible. Doing that would mean having to define each cell in the table grid with a meaningful cell_id, and then knowing which cell_id to display in the dashboard KPI block. Yes, it can be done, but no one is beating down our door asking us to build this sort of thing. So….
Rather than tackling the above complexity, we’ve implemented something simpler: you can create an aggregation in a table footer column by using the same types of aggregations (Count, SUM, MIN, MAX, and AVG) for a specific column or columns. And you can even filter the COUNT to count the number of times a specific value was entered into a column.
Here’s a quick look at that in the form template designer tool. In the example below, we’ve created a Water Levels table that is used to collect depth to water in a monitoring well, the presence of free product in that well, and the depth to product if there’s product in that well. The footer is enabled, and if you look closely, the FP? column is a checkbox field that the field tech can check as TRUE if they encounter free product in the well. That column has an id of “fp” and in the footer field, there is a small formula like this: “=count(Yes)”. This means that the footer aggregation for that column will display the number of times that that field has been checked, in essence giving us the ability to determine how many wells had free product in them during the well tagging event for that project.
Here’s what that water level form looks like in runtime in the reporting dashboard and KPI blocks. Notice in the KPI blocks that it counts the number of wells that had free product in them (in the example below, 7 water level forms have been submitted, with 53 total wells tagged, and 8 of them exhibiting free product). This is pretty good information that’s available right at your fingertips with just a few clicks.
Notice also that when you click on the PDF icon for a specific form in the Forms screen, you can also see the count value counting the number of times free product was encountered in wells for that specific water level gaugng event.
So there you have it. While it takes a little bit of effort to set up, you can pretty much track anything you capture in XForms, in a simple and easy to understand drill-down dashboard.
Want to learn more?
Click/tap the button below.