Reporting on multi-select picklist data

Reporting on multi-select picklist data

I'm wondering how others have solved this problem:  My company has a number of "strategic goals" that we attach to various potentials as they come in.  So, a potential can tick off none, one, or more than one of these as it moves through the pipeline.  Let's call them by fruit names: Apple, Banana, Cherry, Durian.  The data then appear as a comma separated list in the field for that question.  

Potential Name      Strategic Goal
Potential 1            Apple, Banana
Potential 2            Banana, Durian
Potential 3            Apple, Cherry
...

And so on.  My question is how to group potentials in reports by whether they tick a given strategic goal.  So, how did we do on Banana?  How did we do on Durian?  We're not worried about double-counting these, but the problem is that the database can't 'see' that Potential 1 and 2 both serve Banana.  Is there some kind of SQL formula that deals with this?  My alternative solution which is less desirable, is to make each strategic goal a checkbox instead of part of a multi-select.  

Any advice from others who are reporting on this kind of data?  

Andrew
    • Sticky Posts

    • What's New in Zoho Analytics - November 2025

      We're thrilled to announce a significant update focused on expanding your data connectivity, enhancing visualization capabilities, and delivering a more powerful, intuitive, and performant analytics experience. Here’s a look at what’s new. Explore What's
    • What's New in Zoho Analytics - October 2025

      Hello Users! We're are back with a fresh set of updates and enhancements to make data analysis faster and more insightful. Take a quick look at what’s new and see how these updates can power up your reports and dashboards. Explore What's New! Extreme
    • What’s New in Zoho Analytics – September 2025

      Hello Users!! In this month’s update, we’re raising the bar across multiple touchpoints, from how you bring in data, plan and track projects to how you design and brand your dashboards. We’ve added the all-new Gantt chart for project visualization, expanded
    • Announcing Agentic AI - Ask Zia!

      We are delighted to roll out the new agentic AI capabilities in Ask Zia, where every stage of the BI workflow is assisted by AI. With a human-in-the-loop approach, Ask Zia ensures that you’re in command of the decision, while AI handles the complexity.
    • Invitation-Based User Access in Zoho Analytics

      Hello everyone, We’re rolling out an important update on how users are added to your Zoho Analytics Organization and Workspaces. Previously, when admins added users, they were automatically added to the organization. Moving forward, to improve security