Several content types in Deskpro, including tickets and articles, support custom fields. You can access these fields in custom reports using the following syntax.
Each custom field has an ID number. To see them in the admin interface, click the small gear icon at the top right, and select Show IDs. Make note of the ID as it will be needed in your query.
Once you have the ID, you can reference the value of a particular field via
custom_data[#]. For example, to refer to a custom ticket field with ID number 12, you would use
If you are trying to retrieve a large number of fields, (over about 55), limitations in the underlying MySQL database will cause the query to fail with a “There is something wrong with your query” message. This is only likely to be an issue if you have a lot of custom fields. You can work around this limitation by retrieving data with the API Basics
This query will give a count of all tickets created this month per value for the custom field with ID 12:
SELECT DPQL_COUNT() AS 'Total Tickets' FROM tickets WHERE tickets.date_created = %THIS_MONTH% GROUP BY tickets.custom_data
This will list the tickets created this month, split by the values of the custom field with ID 12:
SELECT tickets.id, tickets.subject, tickets.person FROM tickets WHERE tickets.date_created = %THIS_MONTH% SPLIT BY tickets.custom_data
This will list a count of the tickets which have a particular custom field option set in the ticket (in this example, it will look for tickets with 'Desired Size' set to Medium or Large):
SELECT DPQL_COUNT() FROM tickets WHERE tickets.custom_data.field_id = '10' OR tickets.custom_data.field_id = '9'