Reports on billing and time log changes

Example Reports

As well as the basic ticket billing reports available at Reports > Billing, you can create custom reports that include charges created with the Billing and Time Log feature.

Information about charges is stored in the Tickets table.

If your DQPL query uses FROM tickets, you can cross-reference this table as ticket.charges.

Example query:

SELECT tickets.subject, tickets.id, TIME_FORMAT(SEC_TO_TIME(tickets.charges.charge_time), '%Hh %im') AS 'Charge Time', tickets.status
FROM tickets
WHERE tickets.charges.charge_time <> NULL AND tickets.date_created = %LAST_MONTH% AND tickets.status IN ('resolved', 'archived')

This makes a report showing the amount of time charged on each resolved ticket that had a charge in the previous month.

Note that charge_time is stored in seconds, so to make it more readable, in the example query we use SQL date conversion functions.

If you want to sum all charges for a ticket and display a total charge, use the SUM () function and GROUP BY tickets.id, like this:

SELECT tickets.date_created, tickets.subject, TIME_FORMAT(SEC_TO_TIME(sum(tickets.charges.charge_time)), '%Hh %im') AS 'Charge Time', tickets.status
FROM tickets
WHERE tickets.charges.charge_time <> NULL
GROUP BY tickets.id
ORDER BY tickets.id

Published: 22/05/2018

Last updated: 22/05/2018