Trying to report on a Custom Field, but the reporting function will not allow me to group by the custom field selection. Can someone please assist me?

Hi Shayla!

In order to group by a field, that field has to be a 1:1 value in the database, which means you'll need to create a calculated field that references your other custom field, then you can group by the calculated field.

I hope this helps!

Thanks,

Hi Shayla,

Here's our help article that goes over the process:

Link

Short story:

Say you have a checkbox field called Toppings. Your goal is to find out who wants what toppings on their ice cream.

Because you can have more than one choice selected, you can't group by it, as that's a many to one relationship (many choices to 1 field, or 1:n in database terms.)

So you create a calculated field, and call it Toppings Aggregated.

The calculation for the calculated field will be the name of the field you want to turn into a 1:1 relationship.

In this case, the calculation is just 'Toppings'. (Without single quotes.)

https://drop.workfront.com/p9uGlzBN

So now you have 2 fields on a custom form, a checkbox field and a calculated field.

You now add the custom form to a request queue where people can submit their favorite ice creams.

John Smith chooses Choc Chips and Sprinkles.

Jane Doe chooses Reeses Pieces and Sprinkles.

Jack Johnson chooses Sprinkles and Oreo Crumbs.

You now have 3 issues, each with a custom form showing all the above.

You create a new report, you add a grouping, and you can now group by the field Topping Aggregate.

Because no 2 people selected the exact same results, you'll have 3 groupings.

https://drop.workfront.com/7KuLvxjA

Now let's say Jack Johnson decides he wants Choc Chips instead of Oreo Crumbs. Now he has Choc Chips and Sprinkles.

You go back to the report and have 2 groupings, with "Choc Chips and Sprinkles" having 2 rows underneath it, one for John Smith and one for Jack Johnson.

This is because even though the Toppings field is multi-select, the same 2 toppings are selected on 2 different issues, and thus the calculated field shows the 2 toppings as 1 value. (Choc Chips, Sprinkles)

https://drop.workfront.com/WnuJdA6Q

I hope this helps make sense!

Thanks,