Queries displaying ID numbers

When data is stored in the Deskpro database as a number we may want to report on it, but return a value that is more readable.

For example satisfaction data is held as below:

If we wanted a count of each time an agent recieved a rating grouped by value, we could run the report below:

SELECT DPQL_COUNT()
FROM ticket_feedback
WHERE ticket_feedback.date_created = %LAST_MONTH% AND ticket_feedback.ticket_message.person.is_agent = 1
GROUP BY DPQL_MATRIX(ticket_feedback.rating, ticket_feedback.ticket_message.person)
 copy

This would output:

We can see that the system is processing 0 as 'None' and then 1 and -1 are showing the numerical value, none of which is particularly helpful.

REPLACE

We can use the REPLACE function to display different information for the query values.

In this example we could use the below:

SELECT DPQL_COUNT()
FROM ticket_feedback
WHERE ticket_feedback.date_created = %LAST_MONTH% AND ticket_feedback.ticket_message.person.is_agent = 1
GROUP BY DPQL_MATRIX(REPLACE(REPLACE(REPLACE(ticket_feedback.rating, '-1', 'Negative'), '0', 'Neutral'), '1', 'Positive'), ticket_feedback.ticket_message.person)
 copy

Which will output:

Custom IDs

As well as built in values such as Satisfaction, we can use REPLACE to help us when we are referencing custom values.

An important note on using REPLACE on a large number of IDs

Be careful if you have a large number of IDs. If we were referencing CRM ids for example and we used the below in our query:

REPLACE(REPLACE(REPLACE(tickets.person.id, '1', 'Matt'), '2', 'JAMES'), '12', 'Bill')
 copy

This would actually render as below:

So 12 would actually be replaced as '1' and '2' rather than '12'.

Because of the logic of the query we can actually mitigate this though by using the IDs in a descending order.

REPLACE(REPLACE(REPLACE(tickets.person.id, '12', 'Bill'), '2', 'JAMES'), '1', 'Matt')
 copy

Which outputs:

Example

Sub Statuses

It isn't possible to reference sub-status names through the reporting system but you can reference IDs.

We can use the below example to amend these to our substatus names (where the substatus ids are 5,6 and 7):

SELECT DPQL_COUNT() AS 'Tickets by sub-status'
FROM tickets
WHERE tickets.ticket_status_id <> NULL
GROUP BY DPQL_ALIAS(REPLACE(REPLACE(REPLACE(tickets.ticket_status_id, '7', 'sub-status 1 '), '6', 'sub-status 2'), '5', 'sub-status 3'), 'Tickets grouped by sub-status')
 copy

Which will output: