We can do that (next year). @CaitVirtue, can you remind me which database field tells us whether a donor prefers to be listed or anonymous? Sorry you had to slog through the muck again!

Tech note: segments are [$1k - 5k), [5k - 25k), [25k - 100k), [100k - 1M), 1M+

I think we have a good handle on the anonymous part, but it's not just that. Some donors want to be listed as something other than the name with which they make their donation, ie I donate with my credit card as "Caitlin M Virtue" but I want to be listed as "Patrick and Caitlin Virtue," or "In Honor of John Murray" or "Caitlin's Awesome Internet Business" And then those crazy fields have to be alphabetized...

Very interesting... Sounds like we should put that public name in a new custom field. Donno if you would want to fall back to using the normal name if no "public" name exists for a contact. A fun project for 2016 ;-)

@awight sorry, i realized i didn't answer your question...the benefactors page/annual report info is located in the contribution record under "note"

In T118822#1810146, @awight wrote:

We can do that (next year).

Now that next year is this year, has this happened? And if not, would someone want to add this to their to-do list for less rushed times? :)

@LeanneS we should discuss in the Civi call if this is still an issue

@Eileenmcnaughton - I compiled it for this year already, but it's was a pretty slow going process. Any suggestions on to make this process less time consuming would be great, lets discuss in our Civi check in or we can hold off until early 2017 if need be.

This task would make this much easier and expose donors who cumulatively donate enough in one year to count as MG: T151271

new "Defition of done"

Create a list of names (with url if applicable) in the 1,00 - 5,000 section in the benefactors page
criteria:

• has donated in that range in the last 18months
• in donor record "listed as public"

converting tables to text in programs like word: https://support.office.com/en-us/article/convert-text-to-a-table-or-a-table-to-text-in-word-for-mac-cca876cd-fef8-4edc-b66a-afd3193ac279

Also just noting we should test the following reports before we deploy since the underlying workings got a bit of an overhaul

select r.id, v.name, report_id FROM civicrm_report_instance r LEFT JOIN civicrm_option_value v ON report_id = v.value WHERE v.name LIKE '%Extendedreport%';
+-----+-----------------------------------------------------------+---------------------------------------------------------+
| id  | name                                                      | report_id                                               |
+-----+-----------------------------------------------------------+---------------------------------------------------------+
| 101 | CRM_Extendedreport_Form_Report_Contact_AddressHistory     | contact/addresshistory                                  |
| 102 | CRM_Extendedreport_Form_Report_Contribute_DetailExtended  | contribution/detailextended                             |
| 165 | CRM_Extendedreport_Form_Report_Contribute_Contributions   | contribution/contributions                              |

Another tech note - I filled all the order by fields where blank in using the following

CREATE TEMPORARY TABLE temp_bene_update
SELECT v.id, sort_name FROM civicrm_value_anonymous_ v
LEFT JOIN civicrm_contact c ON v.entity_id = c.id
WHERE benefactor_order_as_168 IS NULL OR benefactor_order_as_168 = ''
;


UPDATE civicrm_value_anonymous_ v
INNER JOIN temp_bene_update v2 ON v.id = v2.id
SET v.benefactor_order_as_168 = sort_name
;

DROP TEMPORARY TABLE temp_bene_update;
//

CREATE TEMPORARY TABLE temp_bene_update


SELECT contact_id, sort_name FROM (



  SELECT sum(contribution.total_amount) as civicrm_contribution_contribution_total_amount_sum, wmf_donor.entity_id as contact_id,  CONCAT(sort_name) as sort_name ,trxn_id
FROM civicrm_contribution contribution
  LEFT JOIN civicrm_contact civicrm_contact
ON contribution.contact_id = civicrm_contact.id
   LEFT JOIN wmf_donor wmf_donor ON wmf_donor.entity_id = civicrm_contact.id
  LEFT JOIN civicrm_value_anonymous_ v ON civicrm_contact.id = v.entity_id
   WHERE ( wmf_donor.lifetime_usd_total >= 1000 ) AND ( wmf_donor.lifetime_usd_total >= 1000 ) AND civicrm_contact.is_deleted = 0
   AND v.id IS NULL
   GROUP BY civicrm_contact.id  HAVING ( civicrm_contribution_contribution_total_amount_sum >= 1000 )


) as contact;


INSERT INTO civicrm_value_anonymous_
(entity_id, benefactor_order_as_168 )
SELECT * FROM temp_bene_update;

DROP TEMPORARY TABLE temp_bene_update;

@Eileenmcnaughton I have time tomorrow. Or we can wait until fortnightly?

@NNichols I did catch up with Leanne last week so fortnightly is fine

There is an updated version of extended reports in review - which addresses many of the issues for this but still leaves the question of whether I can so a sort on a combined field or we need to pre-fill the order by field - I'm hoping to do the former - the query works OK but it's just how to put it in the UI I'm pondering

Change 494640 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@master] Update extended reports, fix address history, pivots

https://gerrit.wikimedia.org/r/494640

Change 494640 merged by jenkins-bot:
[wikimedia/fundraising/crm@master] Update extended reports, fix address history, pivots

https://gerrit.wikimedia.org/r/494640

@LeanneS @NNichols I just configured 4 benefactor reports in the menu

I imagine you will need to do some initial cleanup but hopefully it will be easier later....

A couple of notes...

• it's set to 'last 18 months' so that's a 'rolling date range'
• it is sorted by the configured benefactor order by field but 'falls back' to the contact's sort name
• it will display the configured benefactor name but 'falls back' to the contact's display name
• you can see on the 50k+ report there is one contact with a website showing- I edited that contact so it would have a website type of Benefactor listing
• the top 2 reports in the list probably should come out of the menu to reduce confusion

!!!!!!

@LeanneS did you check this out (just looking to see if any of the things in the done column are closable)

@Eileenmcnaughton I've been testing around and cleaning up the listings. Overall it works great! Some wonkiness occurred with the Benefactors 15000-49999 report though after a few times of refreshing the report, and now it seems to be out of order after going through and updating the Order as field.

I did add the filter of Completed for contribution status, which might have messed with the results. I added this because a cancelled gift was causing an inaccuracy with a donor's aggregate totals. When I removed that filter though, the order still seems to be jumbled.

@LeanneS I think there is a bug in the fallback in order by when it's not in the output columns - I can dig further if it's a problem but in the meantime I've added it to the output columns

Thanks @Eileenmcnaughton. That seems to have worked!

@Eileenmcnaughton This report also seems to be acting out with getting out of order: instance/145

@LeanneS I fixed that report - I also added a fix so we should see this stop happening.

Do you think this is all good now? Ie should I close the phab?

@LeanneS is this good to close