Answers.Body: <p>This can be done when the &quot;DB Entity&quot; SearchKit display works correctly when joining with other tables. This will let you use the results of one SK query as the basis for a second query.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-04-08T08:36:22.410Z
Answers.Text: Thank for responding. I can see how this should work but I can't find a way to join the DB Entity table and other tables.
  
  For example (picture added to the post), on a Contact ID column in a DB Entity, I can see that the values fk_entity and fk_column are set to null by default. There doesn't appear to be any way to set these to 'Contact' and 'id' from either the SearchKit UI or the API. Is this something that can currently only be done from the terminal?
Answers.UserDisplayName: Sam Hunt
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-04-08T20:58:07.890Z
Answers.Text: @Sam Hunt - sorry if my language was unclear - my intent was to convey that this doesn't work at present because the joins don't work.  I asked the author about this last month during CiviCamp Montreal.  He said, "I started working on this and realized the way joins happen are overcomplicated so I started working on *that* but I didn't finish."  I have a client who cares about this a lot who I'm encouraging to fund this work, but it hasn't become sufficiently urgent for them yet.
Answers.UserDisplayName: Jon G - Megaphone Tech
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-04-09T10:31:58.067Z
Answers.Text: Thanks for the clarification and confirming that the feature hasn't yet been implemented!
Answers.UserDisplayName: Sam Hunt
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-04-06T17:24:08.053Z
Answers.LastActivityDate: 2024-04-06T17:24:08.053Z
Answers.UserDisplayName: Jon G - Megaphone Tech
Answers.Body: <p>I think we got this working for the last 3 years 'just' using SK by starting with contacts and relevant contributions and filtering on total amounts.</p>
  <p>I've put a sample on dmaster called <a href="https://dmaster.demo.civicrm.org/civicrm/admin/search#/edit/45" rel="nofollow noreferrer">Contact Contributions - Last 3 Years</a></p>
  <p>As that will not be there when site next refreshes, here's what it roughly looks like in just the search as well as the query info.</p>
  <p><a href="https://i.sstatic.net/8w3Nn.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/8w3Nn.png" alt="enter image description here" /></a></p>
  <pre><code>{
    &quot;version&quot;: 4,
    &quot;select&quot;: [
      &quot;id&quot;,
      &quot;sort_name&quot;,
      &quot;SUM(Contact_Contribution_contact_id_01.total_amount) AS SUM_Contact_Contribution_contact_id_01_total_amount&quot;,
      &quot;SUM(Contact_Contribution_contact_id_02.total_amount) AS SUM_Contact_Contribution_contact_id_02_total_amount&quot;,
      &quot;SUM(Contact_Contribution_contact_id_03.total_amount) AS SUM_Contact_Contribution_contact_id_03_total_amount&quot;
    ],
    &quot;orderBy&quot;: [],
    &quot;where&quot;: [
      [
        &quot;OR&quot;,
        [
          [
            &quot;Contact_Contribution_contact_id_01.total_amount&quot;,
            &quot;IS NOT EMPTY&quot;
          ],
          [
            &quot;Contact_Contribution_contact_id_02.total_amount&quot;,
            &quot;IS NOT EMPTY&quot;
          ],
          [
            &quot;Contact_Contribution_contact_id_03.total_amount&quot;,
            &quot;IS NOT EMPTY&quot;
          ]
        ]
      ]
    ],
    &quot;groupBy&quot;: [
      &quot;id&quot;
    ],
    &quot;join&quot;: [
      [
        &quot;Contribution AS Contact_Contribution_contact_id_01&quot;,
        &quot;LEFT&quot;,
        [
          &quot;id&quot;,
          &quot;=&quot;,
          &quot;Contact_Contribution_contact_id_01.contact_id&quot;
        ],
        [
          &quot;Contact_Contribution_contact_id_01.receive_date&quot;,
          &quot;=&quot;,
          &quot;\&quot;this.fiscal_year\&quot;&quot;
        ]
      ],
      [
        &quot;Contribution AS Contact_Contribution_contact_id_02&quot;,
        &quot;LEFT&quot;,
        [
          &quot;id&quot;,
          &quot;=&quot;,
          &quot;Contact_Contribution_contact_id_02.contact_id&quot;
        ],
        [
          &quot;Contact_Contribution_contact_id_02.receive_date&quot;,
          &quot;=&quot;,
          &quot;\&quot;previous.fiscal_year\&quot;&quot;
        ]
      ],
      [
        &quot;Contribution AS Contact_Contribution_contact_id_03&quot;,
        &quot;LEFT&quot;,
        [
          &quot;id&quot;,
          &quot;=&quot;,
          &quot;Contact_Contribution_contact_id_03.contact_id&quot;
        ],
        [
          &quot;Contact_Contribution_contact_id_03.receive_date&quot;,
          &quot;=&quot;,
          &quot;\&quot;previous_before.fiscal_year\&quot;&quot;
        ]
      ]
    ],
    &quot;having&quot;: []
  }
  </code></pre>
  <p>Do have a look if this is roughly what you are aiming for. I'm pretty sure you can create custom relative date filters that would allow you to then add years 4 and 5.</p>
  <p>EDIT - the sum for needs to be set to distinct</p>
  <p><a href="https://i.sstatic.net/n1KhF.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/n1KhF.png" alt="enter image description here" /></a></p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-03-07T10:49:30.883Z
Answers.Text: Thanks for responding! I was hoping that this would work and with the data set you've got, it does provide accurate results. The issues start when you have multiple contributions in each year for one contact.
  
  I've updated my post to include an example of what goes wrong. With two years, you get two copies of each contribution, 3 with three years.
Answers.UserDisplayName: Sam Hunt
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-03-07T11:11:06.293Z
Answers.Text: Oh got it now. Can replicate on dmaster too, will do some digging as that's not right.
Answers.UserDisplayName: RebeccaTregenna
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-03-07T11:18:26.403Z
Answers.Text: I've updated my answer and the search on dmaster - if the total amount 'sum' is ticked as distinct it does not appear to do the duplicating for year 2/3 but won't do first year accurately.
Answers.UserDisplayName: RebeccaTregenna
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-03-07T11:52:56.937Z
Answers.Text: I think the reason it's not accurate is because the distinct toggle seems to remove duplicate 'total amount' values, eg if you have two actual payments of £100 it would remove one despite them not being the same contribution ID.
Answers.UserDisplayName: Sam Hunt
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-03-07T09:59:22.937Z
Answers.LastActivityDate: 2024-03-07T11:17:34.393Z
Answers.LastEditDate: 2024-03-07T11:17:34.393Z
Answers.UserDisplayName: RebeccaTregenna
Body: <p>Essentially, I'm trying to create a report that lists all contributions per contact per year; one row per contact and a column for each year's total (say for the past five years). Pulling the information for any one year, grouped by contact, is fairly trivial with date filters but I've yet to find a way of including all this information in a single table.</p>
  <p>A few methods initially occurred to me that had obstacles I couldn't get around:</p>
  <ol>
  <li>Add multiple instances of <strong>Contact Contributions</strong>, one for each column needed.</li>
  <li>Add multiple columns of <strong>Contact Contributions: Total Amount</strong> with the plan to use aggregate functions to filter the results.</li>
  <li>Use data segmentation to pre-filter contributions into each year.</li>
  <li>Using secondary SearchKits, set up DB Entities containing the SUM per contact, one DB entity per year. Then on the main SearchKit, pull in each DB entity per contact in separate columns.</li>
  </ol>
  <p>1 – The first results in multiple copies of each contribution giving huge errors when all the results are summed. Is there a way to remove duplicates from the SUM aggregate function based on information from another field, ie remove duplicates based on Contribution ID and then SUM the results? The stock option to remove duplicates is too simple given it removes matching values.</p>
  <p>2+3 – These options end in the same place with the need for something like a SUM IF function. I tried playing around with the code and reimporting the SearchKit but couldn't find a working solution. Am I missing something obvious or is this outside the scope of SearchKit?</p>
  <p>4 – I'd hoped that DB Entities would be joinable back to their contact record if. Through SearchKit this doesn't seem possible. Does anybody know if this is doable?</p>
  <p>Perhaps there is another way that this sort of report could be achieved? I'd really hoped to use SearchKit as it's solved so many other reporting issues in the past!</p>
  <p>Thanks in advance!
  Sam</p>
  <p>CiviCRM 5.65.2 | Drupal 10.1.8</p>
  <p><em>--- Update 1 ---</em></p>
  <p>Examples of the issues with 1.</p>
  <p><strong>List of contributions per contact</strong>
  <a href="https://i.sstatic.net/JQ2tA.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/JQ2tA.png" alt="List of contributions per contact." /></a></p>
  <p><strong>Duplication issues after adding a second Contact Contributions</strong>
  <a href="https://i.sstatic.net/la0Wf.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/la0Wf.png" alt="Duplication issues after adding a second Contact Contributions" /></a></p>
  <p><strong>Resulting SUM issues</strong>
  <a href="https://i.sstatic.net/D0ZUg.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/D0ZUg.png" alt="Resulting SUM issues" /></a></p>
  <p><em>--- Update 2 ---</em></p>
  <p><strong>FK Entity and Column unset</strong></p>
  <p><a href="https://i.sstatic.net/HRLbUl.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/HRLbUl.png" alt="FK entity and column" /></a></p>
  
Tags: <contributions><reports><searchkit>
Title: SearchKit: Complex contribution reports – duplicating issues
ThreadText: # SearchKit: Complex contribution reports – duplicating issues
  
  (Asked by: username_0 on 2024-03-06)
  
  Essentially, I'm trying to create a report that lists all contributions per contact per year; one row per contact and a column for each year's total (say for the past five years). Pulling the information for any one year, grouped by contact, is fairly trivial with date filters but I've yet to find a way of including all this information in a single table.
  
  A few methods initially occurred to me that had obstacles I couldn't get around:
  
  1. Add multiple instances of **Contact Contributions**, one for each column needed.
  2. Add multiple columns of **Contact Contributions: Total Amount** with the plan to use aggregate functions to filter the results.
  3. Use data segmentation to pre-filter contributions into each year.
  4. Using secondary SearchKits, set up DB Entities containing the SUM per contact, one DB entity per year. Then on the main SearchKit, pull in each DB entity per contact in separate columns.
  
  1 – The first results in multiple copies of each contribution giving huge errors when all the results are summed. Is there a way to remove duplicates from the SUM aggregate function based on information from another field, ie remove duplicates based on Contribution ID and then SUM the results? The stock option to remove duplicates is too simple given it removes matching values.
  
  2+3 – These options end in the same place with the need for something like a SUM IF function. I tried playing around with the code and reimporting the SearchKit but couldn't find a working solution. Am I missing something obvious or is this outside the scope of SearchKit?
  
  4 – I'd hoped that DB Entities would be joinable back to their contact record if. Through SearchKit this doesn't seem possible. Does anybody know if this is doable?
  
  Perhaps there is another way that this sort of report could be achieved? I'd really hoped to use SearchKit as it's solved so many other reporting issues in the past!
  
  Thanks in advance!
  Sam
  
  CiviCRM 5.65.2 | Drupal 10.1.8
  
  *--- Update 1 ---*
  
  Examples of the issues with 1.
  
  **List of contributions per contact**
  [![List of contributions per contact.](https://i.sstatic.net/JQ2tA.png)](https://i.sstatic.net/JQ2tA.png)
  
  **Duplication issues after adding a second Contact Contributions**
  [![Duplication issues after adding a second Contact Contributions](https://i.sstatic.net/la0Wf.png)](https://i.sstatic.net/la0Wf.png)
  
  **Resulting SUM issues**
  [![Resulting SUM issues](https://i.sstatic.net/D0ZUg.png)](https://i.sstatic.net/D0ZUg.png)
  
  *--- Update 2 ---*
  
  **FK Entity and Column unset**
  
  [![FK entity and column](https://i.sstatic.net/HRLbUl.png)](https://i.sstatic.net/HRLbUl.png)
  
  **Tags:** contributions, reports, searchkit
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_1 on 2024-04-06. Score: 0
  
  This can be done when the "DB Entity" SearchKit display works correctly when joining with other tables. This will let you use the results of one SK query as the basis for a second query.
  
  > **Comments:**
  > **username_0**: Thank for responding. I can see how this should work but I can't find a way to join the DB Entity table and other tables.  For example (picture added to the post), on a Contact ID column in a DB Entity, I can see that the values fk_entity and fk_column are set to null by default. There doesn't appear to be any way to set these to 'Contact' and 'id' from either the SearchKit UI or the API. Is this something that can currently only be done from the terminal?  
  > **username_1**: @username_0 - sorry if my language was unclear - my intent was to convey that this doesn't work at present because the joins don't work.  I asked the author about this last month during CiviCamp Montreal.  He said, "I started working on this and realized the way joins happen are overcomplicated so I started working on *that* but I didn't finish."  I have a client who cares about this a lot who I'm encouraging to fund this work, but it hasn't become sufficiently urgent for them yet.  
  > **username_0**: Thanks for the clarification and confirming that the feature hasn't yet been implemented!  
  
  ---
  
  ### Answer by username_2 on 2024-03-07. Score: 0
  
  I think we got this working for the last 3 years 'just' using SK by starting with contacts and relevant contributions and filtering on total amounts.
  
  I've put a sample on dmaster called [Contact Contributions - Last 3 Years](https://dmaster.demo.civicrm.org/civicrm/admin/search#/edit/45)
  
  As that will not be there when site next refreshes, here's what it roughly looks like in just the search as well as the query info.
  
  [![enter image description here](https://i.sstatic.net/8w3Nn.png)](https://i.sstatic.net/8w3Nn.png)
  
  ```
  {
    "version": 4,
    "select": [
      "id",
      "sort_name",
      "SUM(Contact_Contribution_contact_id_01.total_amount) AS SUM_Contact_Contribution_contact_id_01_total_amount",
      "SUM(Contact_Contribution_contact_id_02.total_amount) AS SUM_Contact_Contribution_contact_id_02_total_amount",
      "SUM(Contact_Contribution_contact_id_03.total_amount) AS SUM_Contact_Contribution_contact_id_03_total_amount"
    ],
    "orderBy": [],
    "where": [
      [
        "OR",
        [
          [
            "Contact_Contribution_contact_id_01.total_amount",
            "IS NOT EMPTY"
          ],
          [
            "Contact_Contribution_contact_id_02.total_amount",
            "IS NOT EMPTY"
          ],
          [
            "Contact_Contribution_contact_id_03.total_amount",
            "IS NOT EMPTY"
          ]
        ]
      ]
    ],
    "groupBy": [
      "id"
    ],
    "join": [
      [
        "Contribution AS Contact_Contribution_contact_id_01",
        "LEFT",
        [
          "id",
          "=",
          "Contact_Contribution_contact_id_01.contact_id"
        ],
        [
          "Contact_Contribution_contact_id_01.receive_date",
          "=",
          "\"this.fiscal_year\""
        ]
      ],
      [
        "Contribution AS Contact_Contribution_contact_id_02",
        "LEFT",
        [
          "id",
          "=",
          "Contact_Contribution_contact_id_02.contact_id"
        ],
        [
          "Contact_Contribution_contact_id_02.receive_date",
          "=",
          "\"previous.fiscal_year\""
        ]
      ],
      [
        "Contribution AS Contact_Contribution_contact_id_03",
        "LEFT",
        [
          "id",
          "=",
          "Contact_Contribution_contact_id_03.contact_id"
        ],
        [
          "Contact_Contribution_contact_id_03.receive_date",
          "=",
          "\"previous_before.fiscal_year\""
        ]
      ]
    ],
    "having": []
  }
  
  ```
  
  Do have a look if this is roughly what you are aiming for. I'm pretty sure you can create custom relative date filters that would allow you to then add years 4 and 5.
  
  EDIT - the sum for needs to be set to distinct
  
  [![enter image description here](https://i.sstatic.net/n1KhF.png)](https://i.sstatic.net/n1KhF.png)
  
  > **Comments:**
  > **username_0**: Thanks for responding! I was hoping that this would work and with the data set you've got, it does provide accurate results. The issues start when you have multiple contributions in each year for one contact.  I've updated my post to include an example of what goes wrong. With two years, you get two copies of each contribution, 3 with three years.  
  > **username_2**: Oh got it now. Can replicate on dmaster too, will do some digging as that's not right.  
  > **username_2**: I've updated my answer and the search on dmaster - if the total amount 'sum' is ticked as distinct it does not appear to do the duplicating for year 2/3 but won't do first year accurately.  
  > **username_0**: I think the reason it's not accurate is because the distinct toggle seems to remove duplicate 'total amount' values, eg if you have two actual payments of £100 it would remove one despite them not being the same contribution ID.  
  
  ---
  
  