Explore recipe: Reporting on nested drop-down fields

What's my plan?

In Zendesk Support, you can create custom drop-down lists that contain multiple levels of organization. Using Explore, you can analyze the data returned by each level of your custom drop-down list.

In this Explore recipe, you'll create a drop-down list with three levels and then create some Explore reports that analyze results from it.

This recipe contains the following topics:

• What you'll need
• Creating the initial report
• Reporting on Level 1 fields
• Reporting on Level 2 fields
• Reporting on Level 3 fields

What you'll need

Skill level: Advanced

Time required: 20 minutes

• Zendesk Explore Professional or Enterprise
• Editor or Admin permissions (see Giving users access to Explore)
• Ticket data in Zendesk Support
• Some experience creating reports with Explore
• At least one nested custom drop-down list (see Organizing drop-down list options) 

Creating the custom drop-down list

In this example, you'll create a simple nested drop-down list for a camera shop. From the Cameras list, you can select one of three cameras, Camera 1, Camera 2, and Camera 3 and select to Rent or Buy the selected camera.

To create the drop-down list

1. In Zendesk Support, click ( ).
2. From the Admin menu, click Manage > Ticket Fields.
3. On the Ticket Fields page, click Add field.
4. On the Select field type page, choose Drop-down.
5. Under Title shown to agents, enter a name for the field like Select your camera.
6. Under Field values, enter the following values, one line for each field:

   Cameras::Camera 1::Buy Camera 1
   Cameras::Camera 1::Rent Camera 1
   Cameras::Camera 2::Buy Camera 2
   Cameras::Camera 2::Rent Camera 2
   Cameras::Camera 3::Buy Camera 3
   Cameras::Camera 3::Rent Camera 3

   Tip: If you're working in a language other than English, read this article to help you enter Explore formulas in your language.
7. Once you are finished, click Save. You'll end up with something that looks like the following screenshot:

   

Now, the ticket field you just created will appear on your new and existing tickets. Make sure to select one of the values from this field on a few of your tickets. After Explore next updates its data (based on the schedule determined by your plan), the data will be available in Explore and you can proceed to the next section.

Creating the initial report

In this procedure, you'll create the basic report for your custom field which reports the number of tickets that have each selected combination from your drop-down list. You'll then refine this in later procedures.

To create the initial report

1. In Explore, click the reports ( ) icon.
2. In the Reports library, click New report.
3. On the Select a Dataset page, click Support > Support - Tickets, then click Start report. The report builder opens.
4. In the Metrics panel, click Add.
5. From the list of metrics, choose Tickets > Tickets, then click Apply.
6. In the Rows panel, add your custom field, in this case Select your camera.

You'll see the results from your custom fields in a table like the following example:

While this reports the full results from the drop-down list, sometimes you might just want to return data from the second or third level of the list such as the camera model, or whether it's rented or bought. In the next topics, you'll learn how to do this.

Reporting on Level 1 fields

Now, you'll create a standard calculated attribute that returns the name of the first level of your nested drop-down field. It does this by returning the text before the first :: in the field name.

To create the Level 1 attribute

1. In the report builder, with the same report opened, open the calculations menu (), then click Standard calculated attribute.
2. On the Standard calculated attribute page, name the new attribute List level one.
3. In the formula window, enter or paste the following formula:

   REGEXP_EXTRACT([About],"^[^::]*")

   Replace About with your own custom field name.

4. When you are finished, click Save.

Now, when you use this attribute, you'll only return results the first level of the results (before the first ::), in this case, Cameras.

Reporting on Level 2 fields

To find the second level, i.e. everything from the first to the second :: in the string, we need to take two steps. First, make another standard calculated attribute called List level two with a formula like:

REGEXP_EXTRACT([About],"(?<=^[^:]*::)([^:]*)")

Replace About with your own custom field name.

You now have a list of results for Level 1 and Level 2 in a hierarchy.

Reporting on Level 3 fields

Finally, if you want to display your level 3 results, make a third standard calculated attribute.

Create an attribute called List level three to create a string from the second :: in the original to the end of the string:

REGEXP_EXTRACT([About],"(?<=^[^:]*::[^:]*::)([^:]*)")