For those who want to export the data and massage data outside of Totara, you might want to give Tableau a try. Hope this helps...

Hi Amir,

Yip these are the results I got using that report source, the fundamental operation of this functionality is dictated by the SQL "Group by" clause, in 2.7.7 an error message is displayed if you are trying to aggregate data from source which already contains aggregated data.

cheers,

George,

I’ve done some analysis of this issue and have found the following:

We have identified some reports which do not aggregate well. Our solution was to add a warning when you try to aggregate those report sources, but there seems to be a bug in that code - for me some report sources that do not have the problem (like certification completion) are showing the warning when they shouldn’t. I have opened a bug about that (TL-7675) and we’ll get it fixed.

I was able to reproduce the issue with ‘half’ pie charts you showed. It occurred because of the choices you made on the ‘Graph’ tab when constructing the chart. For pie charts, the ‘category’ represents the field which will make up the segments of the graph (and are also shown on the legend). You chose ‘Is certified’ which only contains two values “yes” and “no”. For pie charts these values are expected to be unique, but in your case there are multiple “no” values, so only the first one is used. The data series represents the count of the items matching the category you selected. I think we should either support multiple identical values in the category for pie charts, or if that is not possible print an error explaining why we can't. I have opened TL-7676 to cover that.

To get the graph you want you need to think about what you would like the data to look like to represent the values you are after. In this case it would be more like this:

To achieve this the status column must shown the actual status (not the count of status), and you need a column with the count of the number of records with that status. If you are expecting each user to only complete each certification once the easiest way to do that would be "Unique Count of User ID”. So the final setup I used is:

• certification completion report source
• columns tab:
  • Status
  • Count unique of User ID
• graph tab:
  • type: pie
  • Orientation: data series in columns
  • Category: Status
  • Data sources: Count unique values of User ID

Which gave this result:

The other issue here is that you have left a lot of unrelated columns in the table which is being used to generate the graph. It is best to remove columns that are not directly related to the graph as they can have an unintended impact on aggregation. This is explained in more detail with examples here: 

http://help.totaralms.com/index.htm#Report_Aggregation.htm

In your case including the user’s name and the certification name is not what you want if you want to aggregate by status, because having those unique values will prevent aggregation across multiple users and certifications.

I do acknowledge that the current system makes it much harder than it should be to quickly generate the graphs that you need. I think there are a number of changes needed to resolve that including:

• Fixing the issues I found today
• Providing much better documentation on how the system works
• Providing a set of common graphs out of the box, which allow you to quickly produce graphs for common scenarios, but also give some examples that you can view and modify easily.
• Separating graph generation from the tabular data view, so you can product standalone graphs that are separate from the data view.
• In some cases, add additional columns to make common graphs easier to generate
• Improvements to the usability of the graph generation as a whole

Simon