Insights recipe: Reporting on nested drop-down fields Follow

Comments

21 comments

  • Avatar
    Andreas Schuster (Edited )

    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?

     

  • Avatar
    Graeme Carmichael

    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.

  • Avatar
    Andreas Schuster

    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! :)

  • Avatar
    Dennis Lynn

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

  • Avatar
    David Roth

    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?

  • Avatar
    Dennis Lynn

    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?

  • Avatar
    David Roth

    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

  • Avatar
    Emily Sia

    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?

  • Avatar
    Jessie Schutz

    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.

  • Avatar
    Emily Sia

    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?

     

  • Avatar
    Graeme Carmichael

    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.

     

     

     

  • Avatar
    Pedro Reis

    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 ?

  • Avatar
    Dennis Lynn

    Hey Pedro,

    Absolutely you can - that's exactly what this guide is for! What you need to do is create a metric that counts the number of tickets with A listed in the attribute label. Take a look and if you run in to any issues, feel free to post your questions here! We have some super helpful people around here, and I'm sure we can crowd-source some ideas :)

  • Avatar
    Aleks Surowiak (Edited )

     

    Found it, nevermind!

  • Avatar
    Gabriel Scott

    Hey @Dennis Lynn, how would you handle having more than 20 metrics (representing the root category A)? 

  • Avatar
    Dennis Lynn (Edited )

    That's a great question, Gabriel! You *could* do a "top 10" or "top 20" report and then a secondary report to capture the rest (if you have less than 40 metrics, anyway). However, I would probably step back a bit and look at the data itself, asking this question:

    "What do I need to see with this data?"

    If you just want to see the most-used field options, a top 10 report would work, but at this point it might be worth creating a couple different reports and then generating a dashboard for these categories. In this dashboard, you could have a top 10 (or top 20) report, and a complementary report detailing which options are seeing the most activity in recent weeks or which are seeing increasing activity. You could create a report showing which agents are using which fields...

    In short, think about the story you want your data to tell, and then maybe create a dashboard, with reports targeting each of the metrics you are wanting to track. This will vary depending on the purpose of your custom field, or the needs of your business, but when you get beyond 20 separate data points, the value in that numbers themselves can to see diminishing returns. 

    Feel free to post your thoughts here once you have an idea what you want to to do! Your fellow reporting friends (and I) would probably be happy to chime in ;)

    Happy reporting!

  • Avatar
    Gabriel Scott

    Thanks Dennis, these are great insights (pun intended). I had originally begun working from this custom metric on a separate Zendesk page. Aside from the goals you mentioned (keeping the data relevant, basically), I also want to reduce future overhead as far as upkeep of reports, and reduce potential errors as I pass various reporting duties to other admins. I did already separate our full list into two categories, as we have probably ~40 items in the list.

    Maintaining two reports is fine, it just adds further instruction and potential for mistakes. Generally the top (say 15) would not change much, but in the event an item is advertised which normally resides in the bottom half of our metrics, we would need to adjust and switch out metrics on the two reports.

    The method in my message above doesn't allow for the 'dynamic' addition of new nested items, which is why I switched to the method in this post (plus it was a lot less time in selecting individual attribute values).

    I would have avoided the nested solution altogether, however I believe nesting the options will reduce user error (and omission) on the forms filled out.

     

    Using these custom metrics does cause some display funk when creating reports, since the data is no longer based initially on # Tickets (or # Tickets created, depending on your report). I have to use the pivot or 'display metrics in different rows' to better display the tables.

    Also, I may be missing something, but it gets a bit confusing if I want to report on a nested item filtered or just reported via an item in the previous part of the nest. For example, how many tickets are found in category C via Category B in the nest A::B::C, where you would normally use the How attribute in the report. Hopefully that makes sense.

  • Avatar
    Matan Alpervoitch

    Hi Dennis,

    Is it possible to report of the history of a dropdown field?

    I am using a nested dropdown for escalations. So the field contains Tier 1, Tier 2 , Tier 3 and I would like to report on how many tickets had Tier 2 or 3 at some point. Unfortunately, the tags are deleted with each new value chosen so I can't report on those.

    Any suggestion?

  • Avatar
    Diogo Maciel

    Hi Matan!

     

    What do you mean tags are deleted with the new values chosen? You can possibly report on all tags representing the values nested under "Tier 2" for this purpose. If you like us to check further, please send an email to support@zendesk.com with the GoodData link to your report so we can take a look :)

  • Avatar
    Pedro Reis
    • 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 ? 

    • Hey Pedro,

      Absolutely you can - that's exactly what this guide is for! What you need to do is create a metric that counts the number of tickets with A listed in the attribute label. Take a look and if you run in to any issues, feel free to post your questions here! We have some super helpful people around here, and I'm sure we can crowd-source some ideas :)

     

    Hi Dennis, i'm sorry but i don't see in the article a way to do it, i can see that there is a way to filter by root category, but not a way to "isolate" the root category. How can i "extract" that part using a metric?

     

  • Avatar
    Dennis Lynn

    Hey Pedro,

    If you create the metrics following this guide, the labels you are seeing in your graph should actually be the metric names, rather than the attribute labels in the report. I would check to see if you have anything listed in the "How" section of your report. If you are hoping to report on these "Categories" and therefore are creating custom metrics to support it, you wouldn't have anything listed in the "How" of this report, rather all of your values are determined by the metric. 

    Graeme spells this out in more detail above, so I will nod to his expertise above if you want a more visual representation of what I'm talking about. :)
    Happy Reporting!

Please sign in to leave a comment.

Powered by Zendesk