This Explore recipe shows you how to create a report showing organizations that do not have any tickets associated with them. For more help with reporting on users and organizations, see Reporting on user and organization data.
What you'll need
Skill level: Easy
Time Required: 5 minutes
- Zendesk Explore Professional or Enterprise
- Editor or Admin permissions (see Giving agents access to Explore)
- Ticket, user, and organization data in Zendesk Support
How to create the report in Explore
- In Explore, click the reports (
) icon. - In the Reports library, click New report.
- On the Select a dataset page, click Support > Support - Tickets, then click Start report. The report builder opens.
- Next, add your metrics, the things you want to measure; in this case, the number of tickets created. In the Metrics panel, click Add.
- From the list of metrics, choose Tickets > Tickets, then click Apply. Explore displays the total number of tickets in your account.
- Next, you'll create a standard calculated metric for organizations. Although Explore already includes this metric you need to modify it to ensure that its calculation doesn't interfere with the ticket metric you just added by enabling the "compute separately" function. In the calculations menu (
), under Metrics, click Standard calculated metric.
-
In the Standard calculated metric panel, give your new metric a name like "Organizations (computed separately)" and then paste or type the following formula:
IF ([Requester organization status]="Active")
THEN [Requester organization ID]
ENDIF - Ensure that Compute separately is enabled. For more information about this option, see Creating standard calculated metrics and attributes.
- When you are finished, click Save.
- In the Metrics panel of the report builder, click Add.
- From the list of metrics, choose Calculated metrics > Organizations (computed separately), then click Apply.
- Click the metric you just added and ensure that the aggregator for it is COUNT. Explore displays a table showing all tickets and organizations in your account.
- In the Rows panel, click Add.
- From the list of attributes, choose Requester organization > Requester organization name, then click Apply. Explore displays a table showing organization names and the number of tickets in each. You can sort the table by clicking the Tickets column heading to easily see which organizations have no tickets.
Tip: You might see some deleted organization names in your table (with a value of 0 in the Organizations column). You can remove these from the report by configuring a metric filter on the Organizations (computed separately) metric that removes zero values.
15 Comments
Thanks for putting this guide together - super helpful and just what I needed!
That said....I'm running into an error, which I'm hoping someone can help me tackle.
"The set of calculations isnt valid. {0} Adjust the calculation and try again. (Error code: 67ccdea4)"
I've gone over this a few times to make sure I've set this up according to the guide:
Could someone please help identify what I'm missing?
Looks like there's a syntax error in the metric formula; the formula editor does not seem to recognize the attribute [Requester organization ID].
I noticed too that [Requester organisation status] has 'organisation' spelled with an 's' instead of 'z', and from your screenshot, it appears that the formula editor is able to validate the attribute name. It's possible that the locale/translation set for your account uses 'organisation' in the metric and attribute names.
Can you try editing the formula, and then select the attribute from the list of autocomplete/suggested values that will be displayed?
@...
Ahh that old chestnut - that was 100% it! Thanks so much - it now works exactly as I was looking for. 🙏
I'm trying to build off of this to capture Organizations who have submitted no Urgent tickets, within a certain timeframe. I was hoping I could just use filters, but when I add any Date or Ticket Priority filters, it no longer shows correctly. Is there a way to capture this information, and preferably still allow it to be dynamically filtered? (i.e. different date range, or different priority)
You can try building another custom metric to count the number of tickets with priority set to Urgent –
– and then add this metric to the table with the organization count metric. You'll see under the metric column which organizations have no Urgent tickets. If you want to only display the orgs with zero Urgent tickets submitted, then you can use a metric filter to hide rows with 1 or more results/ticket count.
You can also filter this report by date attributes like Ticket created - Date.
Hope this helps. Thanks Jamie!
Thanks, but I'm still struggling to get it to work with any date filters. The below is accurate, but has no date filters.
But as soon as I try and add a date filter, it's incorrect.
Does the date filter also need to be custom?
You should also be able to use native date attributes to slice or filter the data in the report. Can you try using D_COUNT as aggregator for the OrganizationsCount metric?
By the way, just in case we need to look into this further, I have created a ticket for you. Thanks Jamie!
Jamie Farmer, did this every get figured out? I would like to do the same thing. Is there a ticket you can share?
Yes, they assigned a ticket 10918177 , and I was able to get it to work by using these Metrics:
COUNT(Tickets)
COUNT(Urgent_Count)
D_COUNT(OrganizationsCount)
Urgent_Count is IF [Ticket priority]="Urgent" THEN [Ticket ID] ENDIF
OrganizationsCount is IF ([Ticket organization status]="Active")
THEN [Ticket organization ID]
ENDIF
And this is how you were able to get the Ticket created - Date to be honored?
I just used a filter for Ticket Created, yes.
Apologies, but I'm a bit confused. What I'm trying to achieve is what you posted on October 20th, where you have a date range filter and you still identify orgs with zero tickets. Do you still get orgs with zero tickets?
For the broader group, my goal is to build a report which shows the number of tickets created for the past 6 months, including no tickets.
Ahh...actually, my report is a little different. I only wanted to see orgs with no URGENT tickets. So, my report has total tickets column and a URGENT tickets column. However, I just now tried without the URGENT, and it only shows orgs with ticket count greater than 0 once I use date filters. So, looks like I am seeing the same results as you when trying to only see that info.
Maybe Phil Clark is still around? Looks like his original request was the same as yours (without date). I don't see Gab Guinto when I search.
Update: Still using the D_COUNT(Organizations), but I was able to get things working by using a calculated metric for the tickets, so instead of COUNT(Tickets) I use:
IF DATE_DIFF(TODAY(),[Ticket created - Date],"month") <= 6 THEN
[Ticket ID]
ENDIF
Jamie Farmer thanks for the collaboration!
Nice! I might steal it!
Please sign in to leave a comment.