Insights recipe: Reporting on nested drop-down fields Follow

Nesting your field options is a great way to organize your custom drop-down fields for your end users and agents. Reporting on these fields can be difficult, and you might find yourself struggling to report on each level within the field.

This article will help you understand why reporting on nested drop-down fields can be difficult and how you can overcome these obstacles using a custom metric. 

This article contains the following sections: 

Note: For information about organizing drop-down list options (nesting), see Organizing drop-down list options.

How Insights sees nested drop-down fields

In short, what you see is not exactly what Insights receives. When you build your nested drop-down fields, you are essentially telling the UI to present the options as selectable choices. In practice, Insights only works with what it's given, so Insights uses exactly what you put in the title and tag fields of your drop-down options. 

As you probably noticed when you were building out your fields, each custom drop-down option has a title and a corresponding tag. The entire field option title, including the colons, is carried over to Insights exactly as you created. The field title is brought over as an attribute and the field option title is brought over as one attribute label. 

For example, the images below demonstrate how the drop-down field options in the Organizing drop-down list options article will be translated into Insights.  

 

 

The image below adds the Camera Model custom field as an attribute from the How panel. The data is sliced by each field option title.

 

If you want to see how many tickets came in for Digital SLR Cameras for example, you can use the custom metric in the section below. 

Creating your custom metric

You will need to tell Insights to only give you the number of tickets there are based on a portion of the attribute label. You can achieve this by creating custom metrics. 

Note: For more information on creating custom metrics, see Creating custom metrics in Insights

Within the metric editor, you want to create a filter metric using the LIKE clause. GoodData has a great article on how to use the LIKE clause (see Filtering with the LIKE clause). This custom metric uses the LIKE clause to create a filter with the % wildcard. 

To create your custom metric

  1. In the What panel, click the (advanced) link next to Add New Metric
  2. Select Custom metric
  3. Enter the following formula:
    • SELECT # Tickets WHERE Custom Attribute Label <Field Title> [<Field Title>] LIKE "%<partial_attribute_label>%")
    Note: You cannot copy and paste the formula, you must select the items below from the folders in the Elements drop-down list.
    • # Tickets is in Metrics
    • Custom Attribute Label <Field Title> [<Field Title>] is in Attribute labels > Field title. The Field title folder will be your custom field name.  

     

  4. Substitute <partial_attribute_label> with the drop-down option you would like to report on. This is a case-sensitive character string.

 

The example above would use the following metric:

  • SELECT # Tickets WHERE Custom Attribute Label Camera Model? [Camera Model] LIKE "%Consumer%")

If you use this custom metric for every nested drop-down level you want to report on, your report will look like the image below.

Alternative solutions

The benefit of the above solution is that you can report on the attributes already available in your Zendesk without having to make any modifications to your workflow. If you want to use more conventional reporting methods, see the Conditional Fields app. This enables you to create unique fields, and the app will determine how your fields appear. When you use unique drop-down fields rather than nesting within one drop-down, you can report on the fields as individual attributes rather than creating custom metrics. 

 

 

 

 

Have more questions? Submit a request

Comments

  • 0

    Hi,

    really nice article but I tried this, but i get always the result "Report not computable due to improper metric definition" ?

    My query is

    SELECT COUNT (# Tickets) WHERE Custom Attribute Label Escalation [Escalation] LIKE "%Anxious Customer%"


    Can anyone help me?

     

    Edited by Andreas Schuster
  • 2

    Andreas

    In your metric definition, you should have SELECT #Tickets rather than SELECT COUNT # TIckets.

    Also, remember you cannot type the report elements, you must use the elements drop-down list.

  • 0

    Thanks, i had probably some mismatch because i looked how "# Tickets" is aggregating the data. (they are counting the ticket IDs) 

     

    Now it works, perfect! :)

  • 0

    Cool beans! I'm glad you two got it all worked out :)

  • 0

    Thanks for this article.  Just to be clear, in the example above we are creating 4 custom fields: Digital SLR, Digital Compact, Professional Cameras, Consumer Cameras. Is that right?

    If so, this doesn't seem sustainable if you are categorizing tickets with a high level of specificity.  For example, we use a similar 3-level hierarchy and have 100+ unique category tags.  Would we need to create custom fields for each of these?

  • 0

    Hey David,

    You are correct that this case is designed around the use of 4 custom fields, however this guide is assuming the fields have already been created. The metrics I'm suggesting are meant to isolate a specific level (in this case, the sub-category) so that you can report on the number of tickets in that sub-category, without having to aggregate each of the options within that sub-category separately. 

    In short, you don't need to create hundreds of fields if you don't want to - but if you do, this guide helps you to build reports on the various levels within those nested drop-down options. 

    Does that help clarify things?

  • 0

    Hi Dennis,

    Yes, thanks for your clarification.  The solution isn't ideal for us because of the challenge involved in drilling down into the deep nested sub-categories, but I can understand the constraints and can see how this solution makes sense.

    Thanks,

    David

  • 0

    Hi,


    Just followed the instructions and although it generally worked, I was wondering why the results still showed the "::" whereas on the example it doesn't?

  • 0

    Hey Emily!

    In your particular case, it looks like you're reporting on nested dropdown menus. Since each option is formatted with the '::' in the dropdown settings, that's how it'll show up on your report.

  • 0

    Thanks Jessie! I thought the example was also using the nested dropdown menus. Do you have any suggestions on how to best use that and be able to generate a cleaner looking report?

     

  • 1

    Emily

    You have to have eagle eyes to recreate the layout shown above.

    Fist make sure that your custom metrics are named as you want to display on your report. For example:

    • # Account Activations
    • # Email Changes

    Now remove your custom field from the HOW section of your report.

    The text you are seeing in the example, is actually a report legend rather than column names:

    You have a few options, depending on your preference. To configure your bar chart, click 'Show Configurations'.

    For a bar chart with the Custom Metric Names below each column, use these settings:

    To remove the column names and show a legend, change to:

    ... ensuring that then 'Legend' option is ticked.

     

     

     

  • 0

    Hi, 

    I need to show the root category only, so instead of A::B, i want to see A. Any idea how can i extract the root only ? Is there any function that i could use ?

Please sign in to leave a comment.

Powered by Zendesk