Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

Report on custom field with multiple entries

Avatar

Level 4

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.

0694X00000AtLIJQA3.jpg

Topics

Topics help categorize Community content and increase your ability to discover relevant content.

1 Accepted Solution

Avatar

Correct answer by
Level 10

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.

View solution in original post

0 Replies

Avatar

Correct answer by
Level 10

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.

Avatar

Community Advisor

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!!!

Avatar

Level 3

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

Thanks 🙂

Avatar

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!

Avatar

Level 10

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