Body: <p>I have a query about analysing data from activities and contacts in Civi. I've tried to be very clear about what is being done and what has been tried, it's not a simple question so thankyou to anyone who is up for the challenge to help me tackle this problem!</p>
  <p>At the moment, the charity I'm working with downloads the data into Excel and then goes through a labourious exercise to see who has reached their outcomes. I am trying to find a solution that involves:</p>
  <ol>
  <li>Being able to gather this data easily without having to export the data (i.e. within Civi)</li>
  <li>A way of automating the excel process more</li>
  </ol>
  <hr />
  <h2>1 Civi customisation and what's been tried so far:</h2>
  <p>Within Civi, the charity has a number of field groups, with fields within them, and then multiple fields options for each. For example:</p>
  <ul>
  <li><strong>Field</strong>: Advice Given with <strong>options</strong>: Benefits, Housing support, GP</li>
  <li><strong>Field</strong>: Referred to with <strong>options</strong>: Benefits, Housing support, GP</li>
  <li><strong>Field</strong>: Appointment Attended with <strong>options</strong>: Benefits, Housing Support, GP
  There are hundreds of these fields and options.</li>
  </ul>
  <p>The charity then needs to report on when an outcome has been acheived (i.e. when a field has been ticked). They need to count the number of activities AND the number of beneficiaries (unique) who have acheived that outcome.</p>
  <p><strong>One example of an outcome (of which there are about 50) is:</strong></p>
  <p><strong>Outcome 1</strong> is acheived when: Advice Given = Benefits OR Referred to = (Benefits OR housing support) OR Appointment attended =  (GP OR Housing Support)</p>
  <p>So the challenge is to find a way within Civi of reporting on how many beneficiaries and how many activities acheived Outcome 1 in a certain period (e.g. Q1 2020). Ideally, each time the staff would just need to update the dates (and occasionally which fields contribute to Outcome 1 i.e. if a new field is added).</p>
  <p>I've tried:</p>
  <ul>
  <li><p><strong>Saving a Constituent report and Activities Report</strong>. LIMITATIONS: As far as I'm aware, the query between fields is an AND rather than an OR so the question above cannot be answered by using a saved report.</p>
  </li>
  <li><p><strong>Creating an Advanced Search</strong>. LIMITATIONS: You can't save these? Advanced searches allow for OR searches, but it would apply across the dates as well as the fields so you're unable to restrict the activities to a certain date range.</p>
  </li>
  <li><p><strong>Using the Search Builder</strong>. LIMITATIONS: You need to have 6 search fields in this example, as the only way I could see of doing it would be:
  <em>Advice Given = Benefits AND Date between (1/1/2020 and 31/3/2020)
  OR<br />
  Referred to IN (Benefits, Housing Support) AND Date between (1/1/2020 and 31/3/2020)
  OR
  Appointment Attended IN (GP, Housing Support) AND Date between (1/1/2020 and 31/3/2020).</em> This would make the search possible, however, the fact you can't save a search builder AND you have to type the search criteria AND the fact it only shows Contacts (not activities) leaves room for human error. Plus, doing this for 50 outcomes each time would make the reporting just as difficult as it is when exporting to Excel.</p>
  </li>
  </ul>
  <h2>2 Excel reporting, what happens and what's been tried so far:</h2>
  <p>At the moment, a full activities report of ALL activities between two dates is downloaded into Civi. (For an idea of scale, this is about 5,000 rows and 90 collumns) (I've included a screenshot of the sample data for Outcome 1 below)</p>
  <p><em><strong>A reminder of what Outcome 1 needs</strong></em>:<strong>Outcome 1</strong> is acheived when: Advice Given = Benefits OR Referred to = (Benefits OR housing support) OR Appointment attended =  (GP OR Housing Support</p>
  <p>At the moment, the raw Civi data is transformed by:</p>
  <ul>
  <li>Creating a new collumn for each output that needs to be reported on. This looks for the data that meets the outcomes criteria (e.g. new collumn created that searches in the 'Referred to' collumn for 'Benefits' and marks that activities with a 1 if it exists. Then another collumn is created for 'Referred to' collumn for 'Housing Support' and marks that row with a 1 if it exists etc.).</li>
  <li>A further collumn (called Outcome 1) is then created to look across the new collumns and marks with a 1 if, for that activity, one of the criteria is met.</li>
  <li>A seperate sheet is then created to sum the outcomes (e.g. Number of activities with Outcome 1 achieved AND the number of Contacts with Outcome 1 achieved)</li>
  </ul>
  <p>As you may be able to guess, this took a long time to build but with all the collumns, it's really tricky to navigate and if anything changes in the raw data (e.g. a new collumn is added) it takes ages to re-structure the collumns to do that. The have asked me to look if I can do a process to make this more automated.</p>
  <p>Solutions tried:</p>
  <ul>
  <li><strong>Building a Macros to do the data transformation</strong>. LIMITATIONS: the formulae would have to be hardcoded into the Macros which would mean if any additional fields are added or removed to the raw Civi data, the formulae would have to be re-edited in the Macros. At the moment don't have the skills to do this, so this would be quite unsustainable.</li>
  <li><strong>Creating Pivot tables for each of the outcomes without transforming the raw data first.</strong> LIMITATIONS. I need to create 3 pivot tables to look across the 3 collumns (the pivot table filter is an AND rather than an OR, as far as I'm aware so I can't do it in one pivot table), then use a lookup to see which individuals have met at least one of the field's criteria. This enables me to count Beneficiaries, but double counts the activities (e.g. if 'Benefits' appears in more than one collumn on the same row it counts it twice).</li>
  <li><strong>Countif on the raw data.</strong> I can make this work using a formula similar to =COUNTIF('Raw Civi Data'!$H$2:$H$4500,&quot;Benefits&quot;) this counts activities but not unique contacts. I can't find a formula to help me count contacts. If anyone can suggest one that would be the BIGGEST help!!</li>
  </ul>
  <p>Here's a sample of the raw data how it appears in Excel (bear in mind I've only included data for one Outcome out of the 50!) and the criteria required for one of the outcomes.</p>
  <p><a href="https://i.sstatic.net/LHhxp.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/LHhxp.png" alt="raw data sample" /></a></p>
  <p><a href="https://i.sstatic.net/qLrrl.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/qLrrl.png" alt="Criteria for Outcome 1" /></a></p>
  <p>If you've got this far, thankyou <strong>SO MUCH</strong> for taking a look and I hope you enjoy the challenge of helping me to find alternative solutions!</p>
  <p>Chloe</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-12-04T19:52:33.970Z
Comments.Text: hi chloe - that is a big and well detailed question. What CMS are you using (perhaps add as a Tag) since my answer would like be a Drupal View (which we have done for clients with similar data) but then I am bit of a Drupalviewphile
Comments.UserDisplayName: petednz - fuzion
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-12-05T21:06:27.507Z
Comments.Text: Hi Pete, thanks for your response. Yes very big! I've tried lots so wanted to explain where I had got to so far. I believe it is on Drupal. Do you mean a Tag that could be added automatically based on whether one of the criteria was met? If so I'm not sure how you would do that (I did also try a smart group but it would only then give me the ability to count contacts and not the activities.....). Can you expand on the Drupal view? Is this a layer where you could query on the data and add a view to show some kind of dashboard?
Comments.UserDisplayName: Chloe Parker
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-12-06T18:10:23.357Z
Comments.Text: Views is a Drupal module that lets you build queries and displays of civi or drupal data.
Comments.UserDisplayName: petednz - fuzion
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-12-06T22:07:58.840Z
Comments.Text: It sounds like the difficulty is the computed nature of "outcome". Are you able/willing to write a little cron script? You could have a computed custom field in civi that represents outcome, and your cron script could update this field periodically based on the values in the other fields. Then your reporting could just be on that computed field which would be an easier type of report that you could pull directly from civi. P.S. you can actually save searches, they're just called "smart groups" in the actions dropdown. Internally they're actually called saved_search.
Comments.UserDisplayName: Demerit
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-12-07T20:54:48.267Z
Comments.Text: Thanks @petednz-fuzion I'll ask them if this is an option.
Comments.UserDisplayName: Chloe Parker
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-12-07T20:56:14.093Z
Comments.Text: @Demerit I could write the script, but the problem will be editing it if the computation of the outcome changes (for example an additional field is added). I can see that you can save searches, but can't see how you edit a save search (e.g. to change the date range when reporting on a new period). I.e. if I wanted to edit a search on an existing Smart Group. Are you able to point me in the right direction of that?
Comments.UserDisplayName: Chloe Parker
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-12-07T22:30:10.703Z
Comments.Text: You can find the group at Contacts - Manage Groups, then on the far right is a settings link, and then on that screen at the bottom is a link to edit criteria.
Comments.UserDisplayName: Demerit
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-12-09T17:47:43.183Z
Comments.Text: Hi @Demerit ok thanks! Yes, this could be an option! I can't seem to access the settings link? I'm an admin user.... the Manage Groups screen only allows me to click 'Contacts'? Will these groups also allow me to count activities too and not just contacts?
Comments.UserDisplayName: Chloe Parker
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-12-09T18:17:05.293Z
Comments.Text: For the settings link as far as I know you just need Edit Groups permission. For activities no you can't use smart groups. The newer Search Kit (under the Search menu but is hidden until version 5.33) does allow saving activity searches, but seems still a bit rough around the edges.
Comments.UserDisplayName: Demerit
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-12-11T10:46:49.517Z
Comments.Text: Thanks @Demerit for all your help. I'll feed this back to them and see where we go from here. Many thanks again!
Comments.UserDisplayName: Chloe Parker
Tags: <civireport><search-builder><advanced-search>
Title: Help to analyse Civi Data. Counting activities and contacts when the field has more than one option
ThreadText: # Help to analyse Civi Data. Counting activities and contacts when the field has more than one option
  
  (Asked by: username_0 on 2020-12-04)
  
  I have a query about analysing data from activities and contacts in Civi. I've tried to be very clear about what is being done and what has been tried, it's not a simple question so thankyou to anyone who is up for the challenge to help me tackle this problem!
  
  At the moment, the charity I'm working with downloads the data into Excel and then goes through a labourious exercise to see who has reached their outcomes. I am trying to find a solution that involves:
  
  1. Being able to gather this data easily without having to export the data (i.e. within Civi)
  2. A way of automating the excel process more
  
  ---
  
  ## 1 Civi customisation and what's been tried so far:
  
  Within Civi, the charity has a number of field groups, with fields within them, and then multiple fields options for each. For example:
  
  * **Field**: Advice Given with **options**: Benefits, Housing support, GP
  * **Field**: Referred to with **options**: Benefits, Housing support, GP
  * **Field**: Appointment Attended with **options**: Benefits, Housing Support, GP
    There are hundreds of these fields and options.
  
  The charity then needs to report on when an outcome has been acheived (i.e. when a field has been ticked). They need to count the number of activities AND the number of beneficiaries (unique) who have acheived that outcome.
  
  **One example of an outcome (of which there are about 50) is:**
  
  **Outcome 1** is acheived when: Advice Given = Benefits OR Referred to = (Benefits OR housing support) OR Appointment attended = (GP OR Housing Support)
  
  So the challenge is to find a way within Civi of reporting on how many beneficiaries and how many activities acheived Outcome 1 in a certain period (e.g. Q1 2020). Ideally, each time the staff would just need to update the dates (and occasionally which fields contribute to Outcome 1 i.e. if a new field is added).
  
  I've tried:
  
  * **Saving a Constituent report and Activities Report**. LIMITATIONS: As far as I'm aware, the query between fields is an AND rather than an OR so the question above cannot be answered by using a saved report.
  * **Creating an Advanced Search**. LIMITATIONS: You can't save these? Advanced searches allow for OR searches, but it would apply across the dates as well as the fields so you're unable to restrict the activities to a certain date range.
  * **Using the Search Builder**. LIMITATIONS: You need to have 6 search fields in this example, as the only way I could see of doing it would be:
    *Advice Given = Benefits AND Date between (1/1/2020 and 31/3/2020)
    OR  
    Referred to IN (Benefits, Housing Support) AND Date between (1/1/2020 and 31/3/2020)
    OR
    Appointment Attended IN (GP, Housing Support) AND Date between (1/1/2020 and 31/3/2020).* This would make the search possible, however, the fact you can't save a search builder AND you have to type the search criteria AND the fact it only shows Contacts (not activities) leaves room for human error. Plus, doing this for 50 outcomes each time would make the reporting just as difficult as it is when exporting to Excel.
  
  ## 2 Excel reporting, what happens and what's been tried so far:
  
  At the moment, a full activities report of ALL activities between two dates is downloaded into Civi. (For an idea of scale, this is about 5,000 rows and 90 collumns) (I've included a screenshot of the sample data for Outcome 1 below)
  
  ***A reminder of what Outcome 1 needs***:**Outcome 1** is acheived when: Advice Given = Benefits OR Referred to = (Benefits OR housing support) OR Appointment attended = (GP OR Housing Support
  
  At the moment, the raw Civi data is transformed by:
  
  * Creating a new collumn for each output that needs to be reported on. This looks for the data that meets the outcomes criteria (e.g. new collumn created that searches in the 'Referred to' collumn for 'Benefits' and marks that activities with a 1 if it exists. Then another collumn is created for 'Referred to' collumn for 'Housing Support' and marks that row with a 1 if it exists etc.).
  * A further collumn (called Outcome 1) is then created to look across the new collumns and marks with a 1 if, for that activity, one of the criteria is met.
  * A seperate sheet is then created to sum the outcomes (e.g. Number of activities with Outcome 1 achieved AND the number of Contacts with Outcome 1 achieved)
  
  As you may be able to guess, this took a long time to build but with all the collumns, it's really tricky to navigate and if anything changes in the raw data (e.g. a new collumn is added) it takes ages to re-structure the collumns to do that. The have asked me to look if I can do a process to make this more automated.
  
  Solutions tried:
  
  * **Building a Macros to do the data transformation**. LIMITATIONS: the formulae would have to be hardcoded into the Macros which would mean if any additional fields are added or removed to the raw Civi data, the formulae would have to be re-edited in the Macros. At the moment don't have the skills to do this, so this would be quite unsustainable.
  * **Creating Pivot tables for each of the outcomes without transforming the raw data first.** LIMITATIONS. I need to create 3 pivot tables to look across the 3 collumns (the pivot table filter is an AND rather than an OR, as far as I'm aware so I can't do it in one pivot table), then use a lookup to see which individuals have met at least one of the field's criteria. This enables me to count Beneficiaries, but double counts the activities (e.g. if 'Benefits' appears in more than one collumn on the same row it counts it twice).
  * **Countif on the raw data.** I can make this work using a formula similar to =COUNTIF('Raw Civi Data'!$H$2:$H$4500,"Benefits") this counts activities but not unique contacts. I can't find a formula to help me count contacts. If anyone can suggest one that would be the BIGGEST help!!
  
  Here's a sample of the raw data how it appears in Excel (bear in mind I've only included data for one Outcome out of the 50!) and the criteria required for one of the outcomes.
  
  [![raw data sample](https://i.sstatic.net/LHhxp.png)](https://i.sstatic.net/LHhxp.png)
  
  [![Criteria for Outcome 1](https://i.sstatic.net/qLrrl.png)](https://i.sstatic.net/qLrrl.png)
  
  If you've got this far, thankyou **SO MUCH** for taking a look and I hope you enjoy the challenge of helping me to find alternative solutions!
  
  username_0
  
  **Tags:** civireport, search-builder, advanced-search
  
  > **Comments:**
  > **username_1**: hi username_0 - that is a big and well detailed question. What CMS are you using (perhaps add as a Tag) since my answer would like be a Drupal View (which we have done for clients with similar data) but then I am bit of a Drupalviewphile  
  > **username_0**: Hi Pete, thanks for your response. Yes very big! I've tried lots so wanted to explain where I had got to so far. I believe it is on Drupal. Do you mean a Tag that could be added automatically based on whether one of the criteria was met? If so I'm not sure how you would do that (I did also try a smart group but it would only then give me the ability to count contacts and not the activities.....). Can you expand on the Drupal view? Is this a layer where you could query on the data and add a view to show some kind of dashboard?  
  > **username_1**: Views is a Drupal module that lets you build queries and displays of civi or drupal data.  
  > **username_2**: It sounds like the difficulty is the computed nature of "outcome". Are you able/willing to write a little cron script? You could have a computed custom field in civi that represents outcome, and your cron script could update this field periodically based on the values in the other fields. Then your reporting could just be on that computed field which would be an easier type of report that you could pull directly from civi. P.S. you can actually save searches, they're just called "smart groups" in the actions dropdown. Internally they're actually called saved_search.  
  > **username_0**: Thanks @username_1 I'll ask them if this is an option.  
  > **username_0**: @username_2 I could write the script, but the problem will be editing it if the computation of the outcome changes (for example an additional field is added). I can see that you can save searches, but can't see how you edit a save search (e.g. to change the date range when reporting on a new period). I.e. if I wanted to edit a search on an existing Smart Group. Are you able to point me in the right direction of that?  
  > **username_2**: You can find the group at Contacts - Manage Groups, then on the far right is a settings link, and then on that screen at the bottom is a link to edit criteria.  
  > **username_0**: Hi @username_2 ok thanks! Yes, this could be an option! I can't seem to access the settings link? I'm an admin user.... the Manage Groups screen only allows me to click 'Contacts'? Will these groups also allow me to count activities too and not just contacts?  
  > **username_2**: For the settings link as far as I know you just need Edit Groups permission. For activities no you can't use smart groups. The newer Search Kit (under the Search menu but is hidden until version 5.33) does allow saving activity searches, but seems still a bit rough around the edges.  
  > **username_0**: Thanks @username_2 for all your help. I'll feed this back to them and see where we go from here. Many thanks again!  
  
  ---
  
  ## 0 answers
  
  