SuiteCRM Report Not Working When Grouping and Sorting By Date Field

SuiteCRM is a really nice open source CRM application. It has a powerful reporting function but at a time you may face an issue. I wanted to generate a report in which I want to group by Formatted Date and also sort the report by formatted date.

When I tried to use the built in functionality, the report worked only for grouping. As soon as I added sorting option, report did not show anything. This means that something is broken.

I set the logging level to debug so that I can inspect the generated query and error in the suitecrm.log file. Upon inspection, I found the error:

MySQL error 1055: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'some_date_column' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

This really means that the query that SuiteCRM generated is wrong. I started digging the code and finally found the issue.

In file /modules/aor_reports/aor_report.php around line 1337 following code is generating query:

if ($field->sort_by != '') {
	// If the field is a date, sort by the natural date and not the user-formatted date
	if ($data['type'] == 'date' || $data['type'] == 'datetime') {
		$query['sort_by'][] = $select_field_db . " " . $field->sort_by;
	} else {
		$query['sort_by'][] = $select_field . " " . $field->sort_by;
	}
}

The above code generates correct query in the following situation.

• Show date value in a specific date format
• Sort the date as a date and not as a string

But the above code falls short of handling grouping. Change the above code with following code and it will generate correct query.

Warning: This change is not upgrade safe. Upgrading your SuiteCRM instance will remove this change.

if ($field->sort_by != '') {
	// If the field is a date, sort by the natural date and not the user-formatted date
	if ($data['type'] == 'date' || $data['type'] == 'datetime') {
		if ($field->group_by == 1) {
			$query['sort_by'][] = $select_field . " " . $field->sort_by;
		} else {
			$query['sort_by'][] = $select_field_db . " " . $field->sort_by;
		}
	} else {
		$query['sort_by'][] = $select_field . " " . $field->sort_by;
	}
}

The above code generates correct query in the following situation

• Show date value in a specific date format
• When we want to group by formatted date, sort the date as a formatted string and not as a date
• When we don’t want to group, sort the date as a date and not as a string