Answers.Body: <p>I was able to achieve something very similar to your request using multiple Data Segments, with the following steps:</p>
  <ol>
  <li>Create a Data Segment for each of your option values (e.g. if the options are A, B &amp; C you will create 3 data segments:
  <a href="https://i.sstatic.net/GXQnR.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/GXQnR.png" alt="Data segments" /></a></li>
  <li>For each segment, you'll create one default option (No) followed by an option (Yes) that checks whether the field contains the given value (order matters here - the default needs to come first):
  <a href="https://i.sstatic.net/ldJvu.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/ldJvu.png" alt="Data segment edit" /></a></li>
  <li>Create a search that includes all data segments as columns, and aggregates them using SUM(). For aggregation, you want to group all contacts into a single row, so group by something they all have in common (you can group by &quot;contact type&quot; or &quot;contact is deleted&quot;). In this example I gave one contact A+B and a second contact B+C and it correctly gives sums for all 3:
  <a href="https://i.sstatic.net/0RNv5.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/0RNv5.png" alt="Saved search" /></a></li>
  </ol>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-12-20T03:09:02.827Z
Answers.Text: hmm maybe this is what i need to do for summary reporting for lineitems, since all my approaches so far have given incorrect totals
Answers.UserDisplayName: petednz - fuzion
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-12-20T16:08:20.063Z
Answers.Text: Thanks @coleman, with a bit of playing around, I've managed to get this working correctly now, which is great. It works fine for the 17 options that we've got on this site but may be unwieldy if there are many more than this.
  
  As mentioned in chat, it may perform slowly if there is a lot of data, In which case you've suggested saving the search as an entity display so that it is run by cron rather than the user.
Answers.UserDisplayName: William Mortada
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-12-20T16:09:58.093Z
Answers.Text: I've discovered that data segments can be exported to PHP in a similar way to saved searches. This means that all of the configuration can be saved in an extension.
Answers.UserDisplayName: William Mortada
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-12-20T16:17:52.337Z
Answers.Text: Yes, and since you can put logic inside a `.mgd.php` file, it can generate each DataSegment in a loop based on the existing option values. That would make the `.mgd.php` file much smaller and also responsive to changes in the field's options.
Answers.UserDisplayName: Coleman
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-12-20T02:47:01.597Z
Answers.LastActivityDate: 2023-12-20T02:47:01.597Z
Answers.UserDisplayName: Coleman
Answers.Body: <p>Initially, I thought of creating a DataSegment with values like:</p>
  <ul>
  <li><strong>A</strong>: <code>[your_custom_field, CONTAINS, A]</code></li>
  <li><strong>B</strong>: <code>[your_custom_field, CONTAINS, B]</code></li>
  <li><strong>C</strong>: <code>[your_custom_field, CONTAINS, C]</code></li>
  </ul>
  <p>However the nature of DataSegments is that it stops looking after the first true condition is reached, so items with A+B would be listed as &quot;A&quot; only, B+C would be &quot;B&quot; only, etc.</p>
  <p>Instead I think you need to create one Data Segment per option, with one option per segment. You could then create a search, grouped by Contact Id, and add each segment as a column (this gives you a pivoted version of your example, but at least you will have the data).</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-12-19T18:50:42.390Z
Answers.Text: I've just been looking at data segments. The problem is that is that it only counts each contact once. It matches for the first segment but not the subsequent ones.
Answers.UserDisplayName: William Mortada
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-12-19T18:42:47.143Z
Answers.LastActivityDate: 2023-12-19T18:51:44.210Z
Answers.LastEditDate: 2023-12-19T18:51:44.210Z
Answers.UserDisplayName: Coleman
Answers.Body: <p>If you are using CiviCRM's out of the box multivalue checkbox then all the solutions are likely to be a hack and not performant due to that field type being an 'SQL antipattern'.</p>
  <p>It may not be feasible in your case, but, I would consider switching to storing the data in a custom dataset that allows multiple rows per entity (this is currently only possible for contacts but should become available for all in early 2024). This would open up the option to do the query 'properly'.</p>
  <p>Though note: if your plan is to collect this info from the public on a form builder form then you may need or want to improve the UI for presenting multi-value custom data as depending on your use case, this might look fairly clunky.</p>
  <p>In the mean time, aside from what Coleman has suggested, another approach that might be worth exploring is using the EntitySet API which allows you to do unions. I am not sure if / how well that is supported that in Searchkit but a union of single row queries that each gives a count for <code>where field like '%A%</code> might work. If there aren't too many rows and they don't change too often.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-12-19T19:39:15.250Z
Answers.Text: This is a potential option but as you say, isn't a great option for those inputting the data.
Answers.UserDisplayName: William Mortada
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-12-19T19:01:19.137Z
Answers.LastActivityDate: 2023-12-19T19:38:11.517Z
Answers.LastEditDate: 2023-12-19T19:38:11.517Z
Answers.UserDisplayName: michaelmcandrew
Body: <p>I have a custom field that allows users to select multiple options using a checkbox input.</p>
  <p>The custom field has:</p>
  <ul>
  <li>Option A</li>
  <li>Option B</li>
  <li>Option C</li>
  <li>...</li>
  </ul>
  <p>The user can pick one or more of these, e.g. A+B or A+C or B+C+D etc.</p>
  <p>I want to create a report that shows for example:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Selected option</th>
  <th>Number of contacts</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>A</td>
  <td>12</td>
  </tr>
  <tr>
  <td>B</td>
  <td>7</td>
  </tr>
  <tr>
  <td>C</td>
  <td>22</td>
  </tr>
  <tr>
  <td>...</td>
  <td></td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I've tried to create a search display using SearchKit by grouping on the custom field but instead of grouping on the individual options, this groups on the combined options that the user has selected.</p>
  <p>I get something like this instead, which is not what I want:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Selected option</th>
  <th>Number of contacts</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>A+B</td>
  <td>9</td>
  </tr>
  <tr>
  <td>A+C</td>
  <td>1</td>
  </tr>
  <tr>
  <td>B+C+D</td>
  <td>4</td>
  </tr>
  <tr>
  <td>...</td>
  <td></td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Any suggestions?</p>
  
Tags: <custom-fields><searchkit>
Title: How do I create a SearchKit report that segments data based on a multi-valued custom field?
ThreadText: # How do I create a SearchKit report that segments data based on a multi-valued custom field?
  
  (Asked by: username_0 on 2023-12-19)
  
  I have a custom field that allows users to select multiple options using a checkbox input.
  
  The custom field has:
  
  * Option A
  * Option B
  * Option C
  * ...
  
  The user can pick one or more of these, e.g. A+B or A+C or B+C+D etc.
  
  I want to create a report that shows for example:
  
  | Selected option | Number of contacts |
  | --- | --- |
  | A | 12 |
  | B | 7 |
  | C | 22 |
  | ... |  |
  
  I've tried to create a search display using SearchKit by grouping on the custom field but instead of grouping on the individual options, this groups on the combined options that the user has selected.
  
  I get something like this instead, which is not what I want:
  
  | Selected option | Number of contacts |
  | --- | --- |
  | A+B | 9 |
  | A+C | 1 |
  | B+C+D | 4 |
  | ... |  |
  
  Any suggestions?
  
  **Tags:** custom-fields, searchkit
  
  
  ---
  
  ## 3 answers
  
  ### Answer by username_1 on 2023-12-20. Score: 3 | Accepted answer
  
  I was able to achieve something very similar to your request using multiple Data Segments, with the following steps:
  
  1. Create a Data Segment for each of your option values (e.g. if the options are A, B & C you will create 3 data segments:
     [![Data segments](https://i.sstatic.net/GXQnR.png)](https://i.sstatic.net/GXQnR.png)
  2. For each segment, you'll create one default option (No) followed by an option (Yes) that checks whether the field contains the given value (order matters here - the default needs to come first):
     [![Data segment edit](https://i.sstatic.net/ldJvu.png)](https://i.sstatic.net/ldJvu.png)
  3. Create a search that includes all data segments as columns, and aggregates them using SUM(). For aggregation, you want to group all contacts into a single row, so group by something they all have in common (you can group by "contact type" or "contact is deleted"). In this example I gave one contact A+B and a second contact B+C and it correctly gives sums for all 3:
     [![Saved search](https://i.sstatic.net/0RNv5.png)](https://i.sstatic.net/0RNv5.png)
  
  > **Comments:**
  > **username_2**: hmm maybe this is what i need to do for summary reporting for lineitems, since all my approaches so far have given incorrect totals  
  > **username_0**: Thanks @coleman, with a bit of playing around, I've managed to get this working correctly now, which is great. It works fine for the 17 options that we've got on this site but may be unwieldy if there are many more than this.  As mentioned in chat, it may perform slowly if there is a lot of data, In which case you've suggested saving the search as an entity display so that it is run by cron rather than the user.  
  > **username_0**: I've discovered that data segments can be exported to PHP in a similar way to saved searches. This means that all of the configuration can be saved in an extension.  
  > **username_1**: Yes, and since you can put logic inside a `.mgd.php` file, it can generate each DataSegment in a loop based on the existing option values. That would make the `.mgd.php` file much smaller and also responsive to changes in the field's options.  
  
  ---
  
  ### Answer by username_1 on 2023-12-19. Score: 1
  
  Initially, I thought of creating a DataSegment with values like:
  
  * **A**: `[your_custom_field, CONTAINS, A]`
  * **B**: `[your_custom_field, CONTAINS, B]`
  * **C**: `[your_custom_field, CONTAINS, C]`
  
  However the nature of DataSegments is that it stops looking after the first true condition is reached, so items with A+B would be listed as "A" only, B+C would be "B" only, etc.
  
  Instead I think you need to create one Data Segment per option, with one option per segment. You could then create a search, grouped by Contact Id, and add each segment as a column (this gives you a pivoted version of your example, but at least you will have the data).
  
  > **Comments:**
  > **username_0**: I've just been looking at data segments. The problem is that is that it only counts each contact once. It matches for the first segment but not the subsequent ones.  
  
  ---
  
  ### Answer by username_3 on 2023-12-19. Score: 1
  
  If you are using CiviCRM's out of the box multivalue checkbox then all the solutions are likely to be a hack and not performant due to that field type being an 'SQL antipattern'.
  
  It may not be feasible in your case, but, I would consider switching to storing the data in a custom dataset that allows multiple rows per entity (this is currently only possible for contacts but should become available for all in early 2024). This would open up the option to do the query 'properly'.
  
  Though note: if your plan is to collect this info from the public on a form builder form then you may need or want to improve the UI for presenting multi-value custom data as depending on your use case, this might look fairly clunky.
  
  In the mean time, aside from what username_1 has suggested, another approach that might be worth exploring is using the EntitySet API which allows you to do unions. I am not sure if / how well that is supported that in Searchkit but a union of single row queries that each gives a count for `where field like '%A%` might work. If there aren't too many rows and they don't change too often.
  
  > **Comments:**
  > **username_0**: This is a potential option but as you say, isn't a great option for those inputting the data.  
  
  ---
  
  