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:
|Default satisfaction survey value||Satisfaction value stored in the database|
|It was great||1|
|It was OK||0|
|It was not good||-1|
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)
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.
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)
Which will output:
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')
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')
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')
Which will output: