Reporting multi-select field

[Kasey-Gagne]

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

[Scott_Goodwin · Accepted solution]

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. 

[Kasey-Gagne]

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