Page MenuHomePhabricator

Adding 2 new columns to Engage Reconciliation reports
Closed, ResolvedPublic

Description

I would like to add two columns to the Engage Reconciliation - Last Month report and Engage Reconciliation - Current Month report. We use both reports depending on what time of the month we are reconciling.

  1. The first column would show the aggregate original amount received for each batch in the report (total original amount unconverted). This will help to reconcile the handful of International check batches we get each month.
  1. The second column would show the total amount for each batch in the report with fees subtracted from it. We have not started adding fee information to contribution records that Engage enters, but would like to for simplification of reconciliation. Once the fees are entered on the record we want to be able to see them subtracted from the total amount of the batch.

Thank you!

Event Timeline

Just noting the report in question is civicrm/report/instance/75 (and 74) based on CRM_Report_Form_Contribute_Summary - I tried just adding the field but that didn't work

and for report 74

This is the developer tab info

SELECT SQL_CALC_FOUND_ROWS contact_civireport.id as civicrm_contact_id, contribution_civireport.currency as civicrm_contribution_currency, COUNT(contribution_civireport.total_amount) as civicrm_contribution_total_amount_count, SUM(contribution_civireport.total_amount) as civicrm_contribution_total_amount_sum, ROUND(AVG(contribution_civireport.total_amount),2) as civicrm_contribution_total_amount_avg, value_1_check_number_9_civireport.import_batch_number as civicrm_value_1_check_number_9_custom_91   

FROM civicrm_contact contact_civireport   
             INNER JOIN civicrm_contribution   contribution_civireport
                     ON contact_civireport.id = contribution_civireport.contact_id AND
                        contribution_civireport.is_test = 0
             
  LEFT JOIN civicrm_contribution_soft contribution_soft_civireport
                       ON contribution_soft_civireport.contribution_id = contribution_civireport.id AND contribution_soft_civireport.id = (SELECT MIN(id) 

FROM civicrm_contribution_soft cs 

WHERE cs.contribution_id = contribution_civireport.id) 
             LEFT  JOIN civicrm_financial_type  financial_type_civireport
                     ON contribution_civireport.financial_type_id =financial_type_civireport.id
             

  LEFT JOIN civicrm_value_1_check_number_9 value_1_check_number_9_civireport ON value_1_check_number_9_civireport.entity_id = contribution_civireport.id 

WHERE ( contact_civireport.is_deleted = 0 ) AND ( contribution_civireport.receive_date >= 20210201000000) AND ( contribution_civireport.receive_date <= 20210203235959) AND ( contribution_civireport.contribution_status_id IN (1) ) AND ( contribution_civireport.financial_type_id IN (9, 26, 21) ) 

GROUP BY contribution_civireport.contribution_status_id, value_1_check_number_9_civireport.import_batch_number    

LIMIT 0, 50;

@Eileenmcnaughton Wanted to note that @EYener has built out a Superset dash that we are using in the meantime so this isn't super urgent. Although we do want to make sure these fields are included in the new reporting tool that is being built out when the time comes. Feel free to shift this to the back burner until then!

Thanks for the add @MDemosWMF. Is there a need to see individual contributions during this process? If not, I would advocate for keeping the report in Superset, which is great for running aggregated reports and adding / tweaking things as business requirements change. That would also remove the possibility of reports diverging as time goes on.

@EYener in this case we are only looking at the batch totals for reconciliation so this works great! If something comes up we go into civi to investigate individual contributions further. I agree with keeping the report in Superset, but I'm thinking in general it could be useful to have the ability to exclude fees and pull original currency into other reports using the new tool.

@MDemosWMF I'd be interested to hear about other reports you're currently running in Civi UI. This is something I'm always mentioning to folks - if a report doesn't require individual record or donor lookup, it's a great candidate to be brought over to Superset. I'd be happy to hear more about it!

@EYener good to know! Unfortunately the reconciliation reports are my bread and butter at this point, any other time I am looking at individual records. But @LeanneS might have some thoughts on whether fees and original currency would be useful in other reports?

@EYener @MDemosWMF There are many use cases for reporting and searching on original currency and original amounts! These are incorporated into a lot of mailings. Other examples are being able to pull lists or searches on gifts in other currencies, such as when a donor reaches out to see if we've received their 500 EUR check or a list of donors who've given X EUR or more in the past, etc. Advanced Search/Find Contributions can be restricted in pulling that info. Perhaps that's a bug @Eileenmcnaughton? I would say there are fewer use cases for lists involving fees, but could also see there being some with Donor Relations where incorporating fees might be useful.

@LeanneS thanks for pointing out these specific reports! I have previously used the 'original currency received' field in contribution searches, but have less experience with that in reports.

DStrine triaged this task as Medium priority.Feb 16 2021, 9:16 PM

I started digging into this just now & created this table with search kit https://civicrm.wikimedia.org/civicrm/search#/display/Engage/Engage_reconcilliation

I did hit a bug which turned out to because of some cleanup we need to do & I will log a subtask since I need to fix it for the full version of this but it's more pure tech than is appropriate for this ticket

@Eileenmcnaughton that looks great! I just switched the placement of the net sum and total sum columns. It's really helpful to see the criteria you used to get this as well. Thank you!

Just noting that I've been digging into taking this a bit further

https://civicrm.wikimedia.org/civicrm/batch-lising#!/

  • that allows you to put the batch number in as a filter.

I've been discussion with Coleman that there are couple of things that I think would be good here

  1. being able to link from https://civicrm.wikimedia.org/civicrm/search#/display/Engage/Engage_reconcilliation through to the detailed version with the batch number in the url - this upstream PR relates https://github.com/civicrm/civicrm-core/pull/19713
  1. I altered the row limit on https://civicrm.wikimedia.org/civicrm/batch-lising#!/ and it doesn't seem to flow through

I think you are OK with the https://civicrm.wikimedia.org/civicrm/search#/display/Engage/Engage_reconcilliation but I was just keeping this open to follow up those 2

@Eileenmcnaughton this batch number filter is exactly what I was looking for in regards to this ticket: https://phabricator.wikimedia.org/T275707. It solves point #2 but looks like #1 would still need to be built out.

Just noting that of the 2 things I was digging into

  1. this is complicated - I'll keep chatting with upstream about how to do this but will leave out of scope for this ticket and
  2. maybe I was imagining it

@Eileenmcnaughton That's true! We can use the reconciliation search for the totals (fees total, batch total, etc) and if we need to look further into the contributions within the batch use the contributions in batch form.

For the contributions in batch form can we add a fees column and date received column as well? I tried adding it to the search, but it doesn't look like it follows through to the form.

There is an inbetween stage called a search display - you can see it shows both 'Compose search' and the name of the search display (here Engage batch listing) - the final form is based on the search display not just the search so they need to be added there too

@Eileenmcnaughton Ahh I see that now. That looks great! Thanks for explaining. With the updated fields and the reconciliation search for the totals I think that covers everything I was looking for in this ticket as well: https://phabricator.wikimedia.org/T275707

I think this can be resolved now based on ^^