- Reports Overview
- Making a report
- Anatomy of a DPQL query
- General expression format
- Aliasing expressions with AS
- Archived status
- Date and time operations
- List of functions
- Accessing custom fields
- Pull-down menus
- Cross-referencing table fields
- Chat Widget Feedback
- Exclude tickets created by agents
- Exporting User Data
- List tickets created on the weekend
- Links to all the tickets each agent resolved last month
- Reports on billing and time log changes
- Reports using ticket logs
- Ticket backlog with a custom timeframe
- Ticket messages per agent excluding notes
- DPQL Field Reference Tables
You can use the DPQL_MATRIX function in the GROUP BY clause to create Matrix tables in DPQL.
A particularly common example might be where you're using DPQL_COUNT in your SELECT statement and you'd like to divide the count up by two fields.
Using GROUP BY
You may want to view the number of tickets solved broken down by category.
We could do this with the query below:
SELECT DPQL_COUNT() AS 'Tickets by category' FROM tickets WHERE tickets.status IN('resolved', 'archived') GROUP BY tickets.category
This will give us a count of solved tickets that were assigned to that category.
Using a Matrix in GROUP BY
We might want to take that a step further though and see how many tickets are being resolved by each agent under that category.
This is where we would use the DPQL_MATRIX function
SELECT DPQL_COUNT() AS 'Tickets by category' FROM tickets WHERE tickets.status IN('resolved', 'archived') GROUP BY DPQL_MATRIX(tickets.category, tickets.agent)
This will create a Matrix table such as the example below: