Build Explore reports to utilize the relationship between parent and child tickets (advanced)

This knowledge base article for the SweetHawk tasks app will cover how to build reports in Zendesk Explore that utilize the relationship between parent and child tickets. 


The first report that we'll be creating is a table of all of your parent tickets and their sub-tickets listed next to them and will look something like this: 




To set this report up we will first need to do some housework in Zendesk Support to ensure the data is delivered to Explore in a way that it can be sliced. This is covered in the stage 1...

STAGE 1: Zendesk support setup 

1. Record the ID of the numeric field called Parent ticket ID.

2. Create a custom ticket field for the new parent ticket ID that Explore can use to slice reports.

3. Create a Zendesk webhook that will be used to copy the parent ticket ID to the new field.

4. Create a Zendesk automation to update all unclosed tickets. 

5. Create a Zendesk trigger to fire on the update made by the automation to push data to the new field so you can start reporting on everything straight away.


STAGE 2: Building the report in Explore


STAGE 1: Zendesk support setup 

Step 1: Recording the ID of the ticket field callend "Parent ticket ID'. 

To do this click on the cog on the left of the screen, then in Ticket fields search for Parent and then copy and save the number next to Paretn Ticket ID like this: 



Step 2: Creating a custom field. 

To create a custom field, first click on the cog on the left of the screen, then go into Ticket Fields and at the top right click on Add field like this: 


Now call your ticket field "Parent ticket ID (for reporting)", select Text, make it an Agent only field and click Save at the bottom right like this:



Once the field has been created, copy and save this field ID the same as you did in step 1.  


Step 3: Creating a Zendesk webhook. 

Given that you webhooks can be used to do a multitude of things in Zendesk, we have a separate article on how to create these here

The particular of this webhook are that you will need to set the endpoint URL of the webhook to:{{}}.json

Don't forget to replace "yourhelpdesk" with the name of your Zendesk domain.


Step 4: Create a Zendesk automation. 

The purpose of this automation is to ensure that the parent ticket ID is recorded on all unclosed tickets so you can start reporting on tickets as soon as possible. Automations will fire on a maximum of 1000 tickets at a time so if you have many tickets in an unclosed state, it may take some time for all tickets to be completed. However once it has done its initial run, moving forward it will only run on new sub-tickets.

To create an automation click on the cog on the left of the screen, then under Automations click Add automation on the right of the screen like this:


Call the automation: "Update all tickets with a parent ticket"


Then, under Meet ALL of the following conditions, set the rules to the following: 

  • Ticket: Status - Less than - Closed
  • Ticket: Tags - contains none of the following - parent_check_done
  • Ticket: Tags - contains at least one of the following - tasks_subticket

Under perform these actions set it to add two tags "pcheck_done" and "push_parent_id" like this: 

  • Ticket: Add tags - parent_check_done push_parent_id


It should look like this when you're done: 



Step 5: Create a Zendesk trigger. 

To create a trigger click on the cog on the left of the screen, then under Triggers click Add trigger on the right of the screen like this:


Now name the trigger "Pass parent ticket ID to the reporting field" and set the following rule under Meet ALL of the following conditions

  • Tags - contains at least one of the following - push_parent_id


Then under Actions set the rules:

  • Remove tags - push_parent_id
  • Notify active webhook - Update ticket (as named by you when you created it in step 2). 


...and for the JSON body place the following code: 

"ticket": {
"custom_fields": [
{"id": 5555555555, "value":"{{ticket.ticket_field_999999999}}"}]

Where 5555555555 = ID of the ticket field "Parent Ticket ID (for reporting) - (That you saved in step 2)

And 999999999 = the ID of the ticket field "Parent Ticket ID"  - (That you saved in step 1). 

The trigger should look like this: 



Now click save at the bottom.



STAGE 2: Creating the reports

Before beginning stage 2 you may need to wait between 1-24 hours until the field that you've created above gets pushed over into Explore.


Go to Explore and click into the Queries section like this: 


Now click to create a new query at the top right.


Now select Support, set the dataset to Tickets, choose the default data set and select New query like this:


At the top right of the screen click add next to metrics click Add, search for Tickets, click Tickets and select Apply like this" 


Then under Rows, search for Parent, select Parent ticket ID (for reporting) and click Apply:


Now click back onto Parent ticket ID (for reporting) and then click on Excluded, check NULL and select Apply


Now add the Ticket ID as another row by following the same steps like this:


Finally add Ticket Subject in the same way and you will end up with a report like this: 


You can change the names of each column title by clicking on it and typing a name that makes more sense for the report like this:


Lastly ensure that you save the report before adding it to one of your dashboards.





Was this article helpful?
0 out of 0 found this helpful



Please sign in to leave a comment.