Ticket messages per agent excluding notes

Example Reports

Requirement: a report showing the number of ticket replies to users per agent last month, excluding any agent notes.

The built-in report Number of ticket messages written PLACEHOLDER per agent per month does most of what is required, but includes notes.

Using the built-in report and clicking SHOW QUERY gives us:

SELECT DPQL_COUNT() AS 'Total Messages'
FROM tickets_messages
WHERE tickets_messages.person.is_agent = 1
        AND tickets_messages.date_created = ${date}
SPLIT BY tickets_messages.person
GROUP BY DPQL_CONCAT(DPQL_YEAR(tickets_messages.date_created),
     '-', LPAD(DPQL_MONTH_NAME(tickets_messages.date_created), 2, '0')) AS 'Period'
ORDER BY tickets_messages.date_created



Looking at the details of the Ticket Message (table: tickets_messages), there is a boolean is_agent_note field. That means that it can only take 1 or 0 as values, where 1 indicates true and 0 indicates false. Since we don’t want notes, we can add this to the WHERE clause:

AND tickets_messages.is_agent_note = 0

This outputs the required report.

Published: 22/05/2018

Last updated: 22/05/2018