Recent searches


No recent searches

Using SQL to filter reports



image avatar

Erin O'Callaghan

Zendesk Documentation Team

Edited Jun 21, 2024


0

4

4 comments

Could you please share examples on how to use it?

0


image avatar

David

Zendesk Customer Care

Hey MAD Monica!  

The SQL options panel in Explore allows for more advanced data manipulation and fine-tuning of your reports beyond what's achievable through the standard interface. This feature is especially useful for creating more specific, tailored reports that fit precise business needs.

Scenario: 
You want to create a report to analyze the performance of your support agents, specifically focusing on the average resolution time for tickets, but you only want to include agents who have closed more than 50 tickets. Additionally, you wish to order the results by the average resolution time in ascending order and limit the report to the top 10 performers.

Step-by-Step Example:
1. Create a New Report:
    - In Zendesk Explore, start by creating a new report.
    - Choose the "Support: Tickets" dataset.
2. Select Metrics and Attributes:
    - Metrics: Add the metric "AVG Time to resolution (hrs)" to measure the average resolution time.
    - Attributes: Add the attribute "Assignee" to group the results by each agent.
3. Access SQL Options:
    - In the Result manipulation menu (icon might look like a wrench or a gear), select "SQL options".
4. Construct the SQL Query:
    - Having Clause: Here, you specify the condition for an agent to be included in the results. Since you want agents who have closed more than 50 tickets, you would use something like `COUNT(Ticket ID) > 50`. This part of the SQL ensures only agents meeting this criterion are considered.
    - Order By Clause: You want to sort the agents by their average resolution time in ascending order, indicating the most efficient agents first. You would use `AVG(Time to resolution) ASC`.
    - Limit Clause: Since you're interested in the top 10 performers, you would set this to `10`.
5. Optional - Replace NULL by Field**: If there are agents with NULL values in their average resolution time (maybe because they haven't closed any tickets), you could leave this as is or replace NULL with a value for readability.

Example SQL Options Input:
- Having: `COUNT(Ticket ID) > 50`
- Order By: `AVG(Time to resolution) ASC`
- Limit: `10`

This setup will generate a report showcasing the top 10 agents with more than 50 tickets closed, ranked by their average time to resolution from lowest to highest, giving you a clear view of your most efficient agents in terms of resolution time.

When to Use This Feature:
- Custom Filtering: When you need to apply specific conditions that aren't directly available through the graphical interface.
- Sorting Requirements: For complex sorting that the standard interface cannot accommodate.
- Limiting Results: To focus on the top or bottom performers by limiting the number of results returned in a report.

This advanced feature allows for greater flexibility and precision in reporting, enabling you to derive insights that are most relevant to your operational efficiency and customer service goals.

0


Is the ‘Having’ clause not available any more? If so, are there any other ways to filter that would be more reliable than the metric filter?

1


image avatar

James G

Zendesk Customer Care

Hello Milton,
 
You are correct. The "Having" clause has been removed from the Result manipulations SQL options menu in the Report builder because it was deemed impractical and had very low usage. You can find more information in our release notes here - Release notes through 2024-04-19
 
To filter reports, you may check the available options we have here - Filtering a report

0


Please sign in to leave a comment.