0

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.

Duplication issues after adding a second Contact Contributions Duplication issues after adding a second Contact Contributions

Resulting SUM issues Resulting SUM issues

--- Update 2 ---

FK Entity and Column unset

FK entity and column

2 Answers 2

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

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

{
  "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

4
  • 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.
    – Sam Hunt
    Mar 7 at 10:49
  • Oh got it now. Can replicate on dmaster too, will do some digging as that's not right. Mar 7 at 11:11
  • 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. Mar 7 at 11:18
  • 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.
    – Sam Hunt
    Mar 7 at 11:52
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.

3
  • 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?
    – Sam Hunt
    Apr 8 at 8:36
  • @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. Apr 8 at 20:58
  • Thanks for the clarification and confirming that the feature hasn't yet been implemented!
    – Sam Hunt
    Apr 9 at 10:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.