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:
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.
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
- In the What panel, click the (advanced) link next to Add New Metric.
- Select Custom metric.
- 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.
-
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.
39 Comments
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
Can anyone help me?
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.
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! :)
Cool beans! I'm glad you two got it all worked out :)
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?
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?
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
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?
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.
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?
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:
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.
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 :)
Found it, nevermind!
Hey @Dennis Lynn, how would you handle having more than 20 metrics (representing the root category A)?
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!
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.
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?
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 :)
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?
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!
Hi,
nice recipe! Thanks alot. I would like to know, if it is possible to have such recipe on conditional fields. I will try to explain my scenario:
For example I have a custom drop-drown field "product category" with different values like mobile, notebook, pc, component.... Now I have set up some conditions with the Conditional Fields app, to display other custom drop-down fields, based on which "product category" I have chosen. In this example, if I chose product category "mobile" another custom drop-down field "mobile" with the values "tablet", "smartphone" and "2in1 notebook" will be displayed and is required for solving the ticket.
I have about 20 categories and for 10 of these categories I have conditions to show custom fields for the specific sub-category.
Now I have a report showing the number of tickets (# Tickets) for every "product category".
So in the WHAT section I have selected the metric # Tickets.
In the HOW section I have selected "product category" and filtered this attribute to show no "(Empty Value)" and this works perfectly.
In this report I could see the total number of tickets for every product category but not the number of tickets of every sub-category.
If I add all sub-categories to this report, the report will have to many "empty values" and then it will look not really clear anymore. The problem is, that the sub-categories will only have a countable number of tickets, if the correct product category was chosen. I hope you can understand the problem.
Now the tricky part and I have no idea to solve this.
My aim is to solve this with drilling but I'm not sure if this will work. For example if I click on the number of tickets for product category "mobile", the drill-in should have the number of tickets for the custom field "mobile". I want to have a detailed view on product category mobile with the sub-category (custom field "mobile").
Drilling accross another report seems not be a solution. I still have the problem, that the drill-report need to have all custom fields in it and then I will have a lot of "empty values" again. Via drilling I cannot control the "HOW" section of the further drill-report. For example, if I click on the number of tickets for product category "mobile", the drill-report shall have only the attribute "mobile" in the HOW section and not all the other sub-categories.
Sorry for my bad englisch. I do not know how to explain better.
Hopefully you can understand my problem :)
Hi Oliver! I'm going to see if one of our Community Moderators can help with this!
Hi Oliver,
I've been down a similar path myself. What I ended doing in the past was coming up with some of the most common filters from my team and building a dashboard that included reports that were already filtered down to what I wanted to show. So in your case, you might have a report that shows Mobile, but it only has the secondary drop-down that is applicable to mobile on that report so it doesn't have all the empty space.
The other solution I used was to have global drop-downs with categories. We used the concept of an About field which is a global, agent facing field. We used this field to re-categorize what the customer submitted for reporting. Sometimes our customers chose the wrong form, which gave them the wrong drop-down, so we didn't have clean data from them. The About field had EVERY combination in one drop-down field that the agent selected and we reported off that one field. You can have triggers/macros set these fields as well (if mobile > feedback is common, you could have a trigger that sets your About field to it's equivalent based on the end user selections in the other fields). This doesn't help you go backwards, but could make reporting forward easier.
Hey, I have already seen something on this subject matter but got a slightly different question.
There seems to be a limit on the metrics that you can have per report. Is is currently 20. Will this be increased at any point? I have considered splitting the report in two but would unfortunately still be 2 metrics short (42 in total) Is there any way to report on all 42 within 2 reports or even one?
I would need to see all 42 metrics for the type of reporting that I need to fdo.
Thank you for you help.
Hey Bettina! I'm going to check with some of our Insights experts on this to see what we can find out!
Hello Bettina,
Thanks for reaching out to us. Unfortunately, there are strict limits imposed by GoodData which we cannot alter. The limit is 20 metrics and 20 attributes per report. You can find a complete list of these report size limitations here: Maximum Report Size
Because of these limitations there is really no workaround. You would have to split this report in 3, i'm afraid.
Thanks again for your question and i'm sorry I do not have better news.
Hey Jessie and Stephen,
Thank you very much for letting me know. I will split the reports instead.
I have just tried doing this now but for some reasons I can't find the second part of the formula: Custom Attribute Label <Field Title> [<Field Title>] is in Attribute labels > Field title. The Field title folder will be your custom field name.
When I click on Attribute Labels it shows Attributes only at the top:
Has there been any change? Thanks again for your help.
Bettina
Hey Bettina,
I apologize for the confusion. The screenshots do not incorporate every step in the process. When you first select "Attribute Labels" you will be taken to a list of all your Attributes. You have to select the one you're interested in and once you do that it will take you to a page listing the labels for that attribute that will be titled "Attribute Labels". So that middle step of choosing the relevant Attribute is not included in the screenshot.
Hi Stephen,
Thanks for your quick reply. I thought that might be the case but the problem I am experiencing is that the attribute cannot be found at all. I cannot find Attribute labels - Field title. Not via the search function or by manually looking.
I started this report before the holidays and all was good but it is now no longer available to select.
Can you recreate it at your end? I am trying to do the report for the nested- dropdown fields using this formular:
The #ticket part is fine but the Custom Attribute Label <Field Title> [<Field Titel>] is not found.
Thank you again
Bettina
Please sign in to leave a comment.