- 06 Aug 2024
- 2 minutes to read
Report aggregation and grouping examples
- Updated on 06 Aug 2024
- 2 minutes to read
It is possible to aggregate certain data within a report, this can be useful when you want to see multiple pieces of information together (see the examples below for more details). It is also possible to group data, to give you more of an overview of information. again examples can be found below.
It is not possible to have multiple count aggregations in a report that use the same field for data. This is because once the field has been grouped for the first count it is not ungrouped again, meaning any subsequent counts will simply reflect the number from the initial count. Therefore it is advised where possible you use columns that are already aggregated.
For example, if you wanted to aggregate course completion, using both the Number in progress and the Number completed columns from the Course Completion by Organisation report source will work, as these columns already contain an aggregated number.
However, using both the Is in progress? and the Is complete? columns from the Course completion report source with the count aggregation set on them will not work, as the aggregation would be correctly calculated for the first column but then repeated in the second as they use the same field for aggregation.
Aggregation examples
Assume that the following is the output of my report with no aggregation or grouping turned on:
USER NAME | COURSE |
---|---|
Sample User | Course A |
Sample User | Course B |
Sample User | Course A |
Comma-separated values: Lists the values from the column in one row.
If the Comma-separated values option is turned on for column two (the courses) it produces:
USER NAME | COURSE |
---|---|
Sample User | Course A, Course B, Course A |
CSV without duplicate: Same as CSV, but if Course A is listed twice. This would just show once for the user.
USER NAME | COURSE |
---|---|
Sample User | Course A, Course B |
Count: Instead of listing course names, it would have a count for the column.
USER NAME | COURSE |
---|---|
Sample User | 3 |
Count unique: Eliminates duplicates from count.
USER NAME | COURSE |
---|---|
Sample User | 2 |
Grouping examples
Dates: Date groupings will group by the date/time format chosen.
Assume that the following is the output of my report with no aggregation or grouping turned on:
USER NAME | LAST SYSTEM ACCESS DATE |
---|---|
Sample User | January 1, 2010 |
Sample Two | February 1, 2016 |
Sample Three | January 1, 2016 |
To produce the numbers, not the names, of the users accessing the system, set the User Name or ID to a count field and the date field use whatever grouping format required.
User Name (Count) | Last System Access Date (YYYY)
USER NAME | LAST SYSTEM ACCESS DATE |
---|---|
1 | 2010 |
2 | 2016 |
User Name (Count) | Date YYYY-MM
USER NAME | LAST SYSTEM ACCESS DATE |
---|---|
1 | January 2010 |
1 | February 2016 |
1 | January 2016 |
User Name (Count) | Month of Year
USER NAME | LAST SYSTEM ACCESS DATE |
---|---|
2 | January (even if these aren’t the same year, both accesses took place in a January) |
1 | February |
Get involved in the conversation about Reporting on the Totara Community.
The Totara Academy has a whole course dedicated to using Reports in Totara. Here you can learn more on how to use reports, see best practice, and give it a go yourself.
© Copyright 2024 Totara Learning Solutions. All rights reserved.