Level 4

Solved

Report on custom field with multiple entries

Forum|Forum|5 years ago
November 17, 2020
4 replies
2365 views

Has anyone experience in reporting on a custom field with multiple entries?

We have keywords for every project (one or multiple) and would like to know how often each keyword was used this year, preferably in a nice graph.

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

Best answer by Richard_Le_

Hi Manuel,

If you want to group by a multi-select field in a report, the below text mode for your grouping should do it:

group.0.displayname=Keywords

group.0.valueexpression={DE:Keywords}

group.0.valueformat=HTML

textmode=true

However, please note that you will not be able to create a graph using this grouping.

If you want to create a graph against your multi-select field, you will need to create a calculated field on the custom form that references the 'Keywords' field. Enter the following into the calculation: {DE:Keywords}. You will then be able to reference this calculated field in your reports and use it to build graphs. There is more help on this subject in this article here on Workfront One.

One thing to point out though, is that when you report on a multi select field Workfront will group by each of the custom field entry variations in their entirety. So in your example above, a report group would appear as "Business Case, Business Model'". Or if a user was to select all fields, another group would appear as "Business Case, Business Model, Change Management, Cost Reduction, etc. etc.". Workfront will provide a string for each of the multi-select variations that have been entered into the custom field to group by. For this reason, it could be difficult for you to count the number of times a keyword was used, especially if you have a large report with lots of field entry variations.

Hope this helps!

Best Regards,

Rich.

R

Richard_Le_

Accepted solution

Community Advisor

Hi Manuel,

If you want to group by a multi-select field in a report, the below text mode for your grouping should do it:

group.0.displayname=Keywords

group.0.valueexpression={DE:Keywords}

group.0.valueformat=HTML

textmode=true

However, please note that you will not be able to create a graph using this grouping.

If you want to create a graph against your multi-select field, you will need to create a calculated field on the custom form that references the 'Keywords' field. Enter the following into the calculation: {DE:Keywords}. You will then be able to reference this calculated field in your reports and use it to build graphs. There is more help on this subject in this article here on Workfront One.

One thing to point out though, is that when you report on a multi select field Workfront will group by each of the custom field entry variations in their entirety. So in your example above, a report group would appear as "Business Case, Business Model'". Or if a user was to select all fields, another group would appear as "Business Case, Business Model, Change Management, Cost Reduction, etc. etc.". Workfront will provide a string for each of the multi-select variations that have been entered into the custom field to group by. For this reason, it could be difficult for you to count the number of times a keyword was used, especially if you have a large report with lots of field entry variations.

Hope this helps!

Best Regards,

Rich.

MoniqueEvans

Community Advisor and Adobe Champion

This is possible but it's a pain. You'll need to create a calculated custom field for each option that counts when that option is selected. Then you can report on the sum of the calculated custom field. It's not pretty and it will make your forms very ugly but it's possible and something that I have to do regularly as my users insist on having multi-select fields. I've been told with Reporting V2 this will be much easier to accomplish.

Here's the Workfront Article for more details and directions.

GOOD LUCK!!!

_Manish_Singh

Level 9

Hello Monique - Can you re-share the link please (if it is still available). The above link doesn't work.

Thanks 🙂

K

Krystle_RightPoint

New Member

Hi Manish, I think I found the new link: https://experienceleague.adobe.com/docs/workfront/using/reporting/reports/custom-view-samples/grouping-by-multi-select-custom-field.html

M

ManuelBaAuthor

Level 4

@Richard Leek‚ and @Monique Evans‚ : thank you so much for the comprehensive and very helpful answers!

It's a bit sad and very hard to explain to my users that a task that looks so simple ist so hard to realize... let's see how we make the best out of it!

Doug_Den_Hoed_AtAppStore

Community Advisor

Hi Manuel,

Another option (pardon the pun) would be to use our Excel Updater solution to break those Multi Selects into separate checkboxes within a couple of hours (permanently), as follows:

On the Custom Form with the Multi Select, create a single checkbox for each Multi Select value (e.g. Business Case, single option of "Yes", default to Null, rinse and repeat)
Export the existing data to Excel (e.g. Project ID in column A, the Multi Selects in column B)
Reformat the Excel Sheet with a column for each new checkbox (e.g. column C, D, etc.)
Use a VLookup to put a "Yes" in each such column where the corresponding value exists in column B
Use the Excel Updater to update the data from columns C, D, etc. back into Workfront
Confirm everything worked
Once you are comfortable, DELETE the multi select form
Make a note to avoid multi-selects (at least on data that might need grouping or charting), going forward

At that point, because each checkbox is persisted separately in the Workfront database, you can then easily Filter, Group, and Chart on any of them, such as a Filter for Business Case = Yes, in a vertical Column Chart, grouped by Project Entry Date (by Month).

Regards,

Doug

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded