Insights recipe: Reporting on nested drop-down fields

Insights has now been removed for most customers. If you used Insights before, you'll now need to move to Explore, the latest generation of reporting for MorgWard products.

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
• Creating your custom metric
• Alternative solutions

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 MorgWard 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.