Expand my Community achievements bar.

SOLVED

Reporting multi-select field

Avatar

Level 3

We have a multi-select field called "Brand" where they are able to select between 1-15 different brands, and I am looking for a way to build 1 project report (chart view) that will count each of those selections individually -- rather than needing a separate report for each brand.

 

I am thinking we will need calculated fields for each of the 15 different brands that populates a count of 1, but am unsure how to go from there to sum up the counts into 1 combined chart... I've seen a few similar posts about this but can't seem to find the correct solution. 

 

For example:

If Brand A, Brand B, Brand, C, Brand D are selected, I'd like to have

A = 1

B = 1

C = 1

D = 1

 

rather than

A, B, C, D = 1. 

 

Any suggestions are appreciated Thank you!!

Topics

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

1 Accepted Solution

Avatar

Correct answer by
Level 4

Hello @Kasey-Gagne 

You could have a custom form that is attached to every project. That will check the brand input and then add 1 to the field representing that brand and put 0 in all the others. You then create a report based on these new values and sum them up. 

 

Giving you a report that shows you the Sum of each Brand count across all projects. 

 

Example: 

Project 1 has had Brand "Scott" input. My new custom form will have calculated fields to represent every possible brand. So "Scott Brand" "Good Brand" "Win Brand" 

They will be IF statements to check the original brand input so IF({DE:brandinput}="Scott", 1, 0) and rinse and repeat for all your brand's. 

Then pull report on the new Custom form. 

 

Caveat is that you need to make sure you update both custom forms if a new brand is added. There mite be a nicer way out there. 

 

Hopefully this makes sense if not let me know a and I can make an example. 

View solution in original post

5 Replies

Avatar

Correct answer by
Level 4

Hello @Kasey-Gagne 

You could have a custom form that is attached to every project. That will check the brand input and then add 1 to the field representing that brand and put 0 in all the others. You then create a report based on these new values and sum them up. 

 

Giving you a report that shows you the Sum of each Brand count across all projects. 

 

Example: 

Project 1 has had Brand "Scott" input. My new custom form will have calculated fields to represent every possible brand. So "Scott Brand" "Good Brand" "Win Brand" 

They will be IF statements to check the original brand input so IF({DE:brandinput}="Scott", 1, 0) and rinse and repeat for all your brand's. 

Then pull report on the new Custom form. 

 

Caveat is that you need to make sure you update both custom forms if a new brand is added. There mite be a nicer way out there. 

 

Hopefully this makes sense if not let me know a and I can make an example. 

Avatar

Level 3

Thank you so much, I'm going to give this a try!

 

I guess my only question would be how to use these individual brand fields as a Grouping to display the sum of each brand within 1 chart - if that makes sense.. 

Avatar

Level 2

Did you ever figure out how to do this? I have a similar situation and the solution above didn't make total sense to me.

Avatar

Level 2

@Scott_Goodwin Would you mind providing an example of your above solution. Mine is a multi select deliverable type field that would require me to create 45 calculated fields. I am ok with this, but truly unsure how to then create the report with a total sum of each deliverable type. Could you please elaborate in more detail with an example? Thanks much!

Avatar

Community Advisor

 

Hi @JessicaBCrum,

 

I invite you to consider The Tally Technique which I just rediscovered, retested, and posted so others can more easily find and use it, too.

 

Regards,

Doug