Explore recipe: Reporting on nested drop-down fields

Return to top
Have more questions? Submit a request

22 Comments

  • Fabio Strasser

    Hi Rob,

    worked great for me regarding dropdown fields, thanks!

    How about multi-select fields though? It's the same thing so I thought it would work the same way. But unfortunately it doesn't work for me...

    0
  • Rob Stack
    Zendesk Documentation Team

    Hi Fabio, I don't think this recipe would work with a multi-select field, but you should get some starting points to set up reports for multi-select fields at https://support.zendesk.com/hc/en-us/articles/360022182014-Reporting-with-custom-fields - Hope this helps!

    0
  • Andrei Kamarouski
    Community Moderator

    Hi Rob, 
    Could you provide any details on reporting for Multi-select fields in Explore. Thre is nothing specific in the article you referred to earlier. Just general statement:

    > Reporting on this field type is more complex than reporting for a drop-down field.

    1
  • Kevin Gervais

    Hi Rob,

     

    I used the steps listed in the article on my production site and get only a top level number for the total tickets returned.  However, when I use the same steps in my sandbox, I see the expected breakdown of tickets by top level value.  Any thoughts on what I could be doing wrong?

    0
  • Devan - Community Manager
    Zendesk Community Team

    Hello Kevin Gervais,

    I went ahead and created a ticket for you, so one of our experts can look into this potential issue more in-depth. You should get a response shortly on the progress of this matter.

    Best regards.  

    0
  • Jaïs Pingouroux

    Followed the tutorial, and it's not working for me, I have various errors running the query.

    Ticket created: https://support.zendesk.com/hc/en-us/requests/5462838

    0
  • Nicole Saunders
    Zendesk Community Team

    Hi Jais, 

    Would you prefer that we try to respond to you here or to handle this issue in your ticket? 

    0
  • Jaïs Pingouroux

    Hi Nicole Saunders

    Considering I still didn't get an answer from support@ yes we can talk it over here.

    I have a custom nested dropdown field called Functional Component.
    Here's my first calculated metrics:
    IF (CONTAINS([Functional Component],"::")) THEN LEFTPART([Functional Component],FIND([Functional Component],"::",0)) ELSE [Functional Component] ENDIF
     
    If I run the query (screenshot below), I have an error message "This query failed to be executed. (Error code: 70565ca7)"
     
    If I try to go further with the tutorial, I create a "Functional Component Level 2 Prep" calculated metrics with this formula: IF (CONTAINS([Functional Component],"::")) THEN SUBSTR([Functional Component],FIND([Functional Component],"::",0)+2,LENGTH([Functional Component])) ELSE "Other" ENDIF
    Then another metric "Function Component Level Two": IF (CONTAINS([Functional Component Level 2 Prep],"::")) THEN LEFTPART([Functional Component Level 2 Prep], FIND([Functional Component Level 2 Prep],"::",0) ) ELSE [Functional Component Level 2 Prep] ENDIF
    If I try to run this query, I have another error: "The set of calculations Calculated member named 'SUM(functioncomponentleveltwo-63ed633178)' references 'Functional Component Level 2 Prep' which is unknown as attribute. isn’t valid. Adjust the calculation and try again. (Error code: 514de5e3)"
     
    Can you please help?
     
    0
  • Brett Bowser
    Zendesk Community Team

    Hi Jaïs,

    It looks like you're creating this as a metric and not an attribute which is causing the error. Can you try creating as an attribute instead to see if that resolves the issue?

    Let me know!

    0
  • Jaïs Pingouroux

    Indeed Brett Bowser I was working with calculated metrics instead of calculated attributes.

    Thanks for correcting my mistake and for your answer.

     

    0
  • Brett Bowser
    Zendesk Community Team

    Happy to help Jaïs!

    0
  • Hannah Voice

    Thanks for this very helpful guide. 🙌

    Is it possible to create a custom attribute that includes all final level options across multiple fields? Here's the context:

    We have 15 ticket forms and each form has its own topic field. I've created the queries outlined in this guide for each of those topic fields (example, another example). However, I would like to create one final query that simply gives the ticket count for every single topic across all 15 of those topic fields. Some have one level, some have two, and some have three. So, ideally we'd just see the final level topic in that query.

    Would appreciate any guidance on whether this is possible. Many thanks.

    0
  • John Burns

    Hi there, my brain my just be foggy but what if we have 4 "levels"? What would category 3 prep look like? I cant seem to sort out in the attribute why its pulling level 2 vs level 3 etc. Thanks.

    John

    0
  • Tom Montgomery

    Great tutorial Rob Stack, thank you so much.

    I would like to ask the same question as John Burns, what would the attributes look like for a 4th level? I've tried and I just can't work the same magic!

    Your help would be greatly appreciated.

    0
  • Rob Stack
    Zendesk Documentation Team

    Hi John Burns and Tom Montgomery, I had to do a little research to get the answer to this so apologies for the delay. Here's what I received back. I hope this helps!

    LEVEL 3 PREP:
    IF (CONTAINS([Level 2 Prep],"::")) THEN
    SUBSTR([Level 2 Prep],
    FIND([Level 2 Prep],"::",0)+2,
    LENGTH([Level 2 Prep]))
    ELSE "" ENDIF 

    LEVEL 3:
    IF (CONTAINS([Level 3 Prep],"::")) THEN
    LEFTPART([Level 3 Prep], FIND([Level 3 Prep],"::",0) )
    ELSE [Level 3 Prep] ENDIF

    LEVEL 4:
    IF (CONTAINS([Level 3 Prep],"::")) THEN
    SUBSTR([Level 3 Prep],FIND([Level 3 Prep],"::",0)+2,LENGTH([Level 3 Prep]))
    ELSE "" ENDIF

    0
  • Theodore Wolf

    Hey Team,

    Rob Stack

    Is there anyways we could report on just the level 3? Or would you recommend just hiding those lower levels in the chart configuration?

    And could I potentially layer this with: https://support.zendesk.com/hc/en-us/articles/360052540974-Explore-recipe-Excluding-tickets-closed-by-merge 

    Thanks!

     

    0
  • Tom Montgomery

    Theodore Wolf

    If I understand what you need correctly, it's actually pretty simple. Once you have setup all the levels above, just add "[name of level 3]" to the column or row and it will only show values that come under that category.

    I hope that helps!

    0
  • Theodore Wolf

    Thanks Tom Montgomery!

    Essentially we want to just show the level 3, as its the "meat and potatoes" of our reporting. 

    Im curious if you'd know, could we modify the initial metric to just show solved tickets and exclude the "closed_by_merge" tag?

    We want to use this as a way to track agents solved tickets, and I think a hybrid of this post and this one would do a great job. 

    Thanks!

    0
  • Tom Montgomery

    Hey Theodore Wolf,

    We do exactly that - we exclude the tag. It works like a charm.

    I set this up almost a year ago and it took under an hour from what I remember. It's definitely worth doing.

    To exclude the tag, open up your query and under "filters" add ticket tags then type closed_by_merge in the search bar. Make sure "Excluded" is selected.

    Then, you can add "Level 3" in the Columns section and it will look something like this:

    Note: in this example I also filtered by brand to reduce the sample size.

    I hope that helps!

    1
  • Theodore Wolf

    Thanks Tom Montgomery!

     

    I've tried using this setup in the past, but after spot checking a few tickets, it would still include some that had the tag. Did you do any custom metrics on this query?

    0
  • Tom Montgomery

    No problem Theodore Wolf,

    I have noticed that too. We ignore that issue as the data gives us a good overview - a few additional merge tickets is not a huge problem to us. The solution in the article you shared above might be your best bet.

    Maybe one of the Zendesk team can give some more insight into why the exclude tag feature is not 100% reliable.

    0
  • Rob Stack
    Zendesk Documentation Team

    Hi Tom Montgomery, I wasn't completely sure so I've opened a ticket so someone on the team can investigate. Hopefully, you'll here from someone soon.

    0

Please sign in to leave a comment.

Powered by Zendesk