Reports Guide

REPLACE


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:

image.png

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)

Which will output:

image.png

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')

This would actually render as below:

image.png

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')

Which outputs:

image.png

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')

Which will output:

image.png

Comments (0)

Add a comment

Add a comment

You need to log in before you can submit a comment.

Need a password reminder?