Search and Reporting - archived issueshttps://lab.civicrm.org/dev/report/-/issues2022-04-22T15:52:35Zhttps://lab.civicrm.org/dev/report/-/issues/75Include random as an option when sorting displays2022-04-22T15:52:35Zjoshjosh@civicrm.orgInclude random as an option when sorting displaysCurrently on SearchKit displays, users can sort by a designated field using either "ascending" or "descending". I propose that we include an option to sort data in a "random" format.
![Screenshot_2021-__1_](/uploads/a7c3516f007ed1b47317...Currently on SearchKit displays, users can sort by a designated field using either "ascending" or "descending". I propose that we include an option to sort data in a "random" format.
![Screenshot_2021-__1_](/uploads/a7c3516f007ed1b47317a910d0136c7d/Screenshot_2021-__1_.png)https://lab.civicrm.org/dev/report/-/issues/74Cannot save a copy of a report without the "administer reports" permission2022-04-22T15:48:24ZbgmCannot save a copy of a report without the "administer reports" permissionTo reproduce:
* Create a user with the following permissions
* access CiviReport
* access Report Criteria
* save Report Criteria
* Go to an existing report (or a template)
* Refresh the results
* Under Actions, click "save as"
Re...To reproduce:
* Create a user with the following permissions
* access CiviReport
* access Report Criteria
* save Report Criteria
* Go to an existing report (or a template)
* Refresh the results
* Under Actions, click "save as"
Result: javascript error: "TypeError: $refresh_field.html() is undefined"
![report-saveas-2021-08-05_14-45](/uploads/8f943673067f615dc13147159fc50c17/report-saveas-2021-08-05_14-45.png)https://lab.civicrm.org/dev/report/-/issues/73Search Kit doesn't display related contact custom fields2022-04-22T15:51:13ZJonGoldSearch Kit doesn't display related contact custom fieldsIf you have a Search Kit that includes a related contact, displaying the related contact's custom data will actually show the main contact's custom data.
See screenshots below. Teresa has a most important issue of "Education", Santina'...If you have a Search Kit that includes a related contact, displaying the related contact's custom data will actually show the main contact's custom data.
See screenshots below. Teresa has a most important issue of "Education", Santina's is "Social Justice". When I build a search and include both the `Constituent Information: Most Important Issue` and `Contact Related Contacts: Constituent Information: Most Important Issue` columns, they both have the same value.
![Selection_1184](/uploads/2784f2b7cb49e719f7d635ad9944d17b/Selection_1184.png)
![Selection_1185](/uploads/6daea0fb4b9d2e5db34913e9efff23c8/Selection_1185.png)
![Selection_1186](/uploads/a80892c685eca80550e1dfd09002b548/Selection_1186.png)5.42.0https://lab.civicrm.org/dev/report/-/issues/35APIv4-Based Reports2022-04-22T15:53:11Zjoshjosh@civicrm.orgAPIv4-Based ReportsThis project will allow report templates to be built with the Search-Builder UI. Presently, report templates must be implemented via PHP code. Prerequisite is the Search-Builder UI #32
Tasks:
- Integrate report schema with Form Builder...This project will allow report templates to be built with the Search-Builder UI. Presently, report templates must be implemented via PHP code. Prerequisite is the Search-Builder UI #32
Tasks:
- Integrate report schema with Form Builder UI
- Re-implement report system with APIv4 backend
- Implement API for creating report templates
- Bridge legacy report templates with the new systemSearch Builder Overhaul - Phase 2colemanwcolemanwhttps://lab.civicrm.org/dev/report/-/issues/34Form-Builder-Based Custom Searches2022-04-22T15:53:13Zjoshjosh@civicrm.orgForm-Builder-Based Custom SearchesThis project will allow user-friendly search screens to be built with the Search-Builder UI. Presently, custom searches must be implemented via PHP code. Prerequisite is the Search Builder UI #32
Tasks:
- Integrate custom search schema ...This project will allow user-friendly search screens to be built with the Search-Builder UI. Presently, custom searches must be implemented via PHP code. Prerequisite is the Search Builder UI #32
Tasks:
- Integrate custom search schema with Form Builder UI
- Implement API for creating custom searches
- Bridge legacy custom searches with the new systemSearch Builder Overhaul - Phase 2colemanwcolemanwhttps://lab.civicrm.org/dev/report/-/issues/33Query scheduling & delivered results2022-04-22T15:48:36Zjoshjosh@civicrm.orgQuery scheduling & delivered resultsThis project will allow APIv4 queries to run in the background and notify or deliver results when complete. It supplements the Search-Builder UI project, making complex searches more manageable.
Tasks:
- Add background options to Search...This project will allow APIv4 queries to run in the background and notify or deliver results when complete. It supplements the Search-Builder UI project, making complex searches more manageable.
Tasks:
- Add background options to Search-Builder UI
- Implement queue runner service
- Implement delivery serviceSearch Builder Overhaul - Phase 2colemanwcolemanwhttps://lab.civicrm.org/dev/report/-/issues/32Search Builder UI2022-04-22T17:01:24Zjoshjosh@civicrm.orgSearch Builder UIThis project will create a unified interface that replaces the current Advanced Search and Search Builder forms. This requires the APIv4 Query Improvements project to be done (or at least underway). This is a prerequisite to the Form-Bui...This project will create a unified interface that replaces the current Advanced Search and Search Builder forms. This requires the APIv4 Query Improvements project to be done (or at least underway). This is a prerequisite to the Form-Builder-based reports and custom searches projects.
The mockup for this interface is available at the bottom of https://lab.civicrm.org/dev/report/-/wikis/Search-Builder-Overhaul
Tasks:
- Selection of base table + joins
- Drag-n-drop nested and/or where clauses supporting all fields and operators
- Controls for grouping and sorting
- Display and paging of search results
- Configurable output columns with drag-n-droppable table headers
- Selection of actions to act upon output rows (e.g. “Add Tags”)
- Button to save query as APIv4-based smart group.Search Builder Overhaul - Phase 1colemanwcolemanw2020-06-30https://lab.civicrm.org/dev/report/-/issues/31APIv4 Query Improvements2022-04-22T15:53:48Zjoshjosh@civicrm.orgAPIv4 Query ImprovementsThis project will expand the query language of APIv4 to increase functionality. Implementing this will enhance the capabilities of APIv4-based Smart Groups; without this their utility will be limited to simple queries. This is also a pre...This project will expand the query language of APIv4 to increase functionality. Implementing this will enhance the capabilities of APIv4-based Smart Groups; without this their utility will be limited to simple queries. This is also a prerequisite to the Search Builder UI project.
Tasks:
- Implement Having, Group By parameters.
- Enable use of SQL Functions like SUM().
- Implement explicit joins.
- Implement a way to add subquery expressions to an API call.
Related pull-requests:
* [#16947 - Support GROUP BY and aggregate functions](https://github.com/civicrm/civicrm-core/pull/16947) (merged)
* [#17047 - Api4 Explorer: Support SQL functions and HAVING clause ](https://github.com/civicrm/civicrm-core/pull/17047) (merged)Search Builder Overhaul - Phase 1colemanwcolemanw2020-06-30https://lab.civicrm.org/dev/report/-/issues/30APIv4-Based Smart(er) Groups2022-04-22T15:52:30Zjoshjosh@civicrm.orgAPIv4-Based Smart(er) GroupsThis project will allow smart groups to be built from APIv4 criteria. The MVP would include a button in the API Explorer to save an api4 query as a smart group. This will also connect with the new Search Builder UI when that project is c...This project will allow smart groups to be built from APIv4 criteria. The MVP would include a button in the API Explorer to save an api4 query as a smart group. This will also connect with the new Search Builder UI when that project is complete.
Tasks:
- Allow swappable backend to smart groups
- Extend smart group schema to store api4 query parameters
- Add support for saving smart groups to API explorer UI.Search Builder Overhaul - Phase 1colemanwcolemanw2020-06-30https://lab.civicrm.org/dev/report/-/issues/18Participant Listing report filters incorrectly on role ID2022-04-22T15:48:33ZJonGoldParticipant Listing report filters incorrectly on role IDThis issue is identical to [CRM-18803](https://issues.civicrm.org/jira/browse/CRM-18803) except that CRM-18803 affected all other CiviReports with fields that stored values separated by `CRM_Core_DAO::VALUE_SEPARATOR`. Those were fixed ...This issue is identical to [CRM-18803](https://issues.civicrm.org/jira/browse/CRM-18803) except that CRM-18803 affected all other CiviReports with fields that stored values separated by `CRM_Core_DAO::VALUE_SEPARATOR`. Those were fixed everywhere else by [this PR](https://github.com/civicrm/civicrm-core/pull/8650). However, since the `where()` in this report is overridden, it has its own copy of the regex which wasn't fixed.
I grepped and confirmed this is the only place where this needs to be fixed, and applied the same regex as the commit above.
To replicate this bug, you need at least ten participant roles. The first one's value should be `1`. Searching on this value will return any participant whose role BEGINS with a `1` (i.e. `10`, `11`, `100`, etc.) rather than just records whose participant role value IS 1.JonGoldJonGoldhttps://lab.civicrm.org/dev/report/-/issues/10No pagination on Contribution Detail report2022-04-22T15:51:44ZJonGoldNo pagination on Contribution Detail reportThis seems to be a side effect of [this commit](https://github.com/civicrm/civicrm-core/commit/e6bab5eae9e7293dff9ecb9852f85e6aa01dad7f#diff-585f40aa84b2e15ef6b0768dd64bfd0c) fixing core#170. I'm not 100% sure why this isn't happening o...This seems to be a side effect of [this commit](https://github.com/civicrm/civicrm-core/commit/e6bab5eae9e7293dff9ecb9852f85e6aa01dad7f#diff-585f40aa84b2e15ef6b0768dd64bfd0c) fixing core#170. I'm not 100% sure why this isn't happening on the sandbox, but I'm reasonably certain it's because servers without Full Group By use a different code path.
There were 2.5 bugs that needed to be fixed:
* The first temp table for this report sets a limit of 50 rows - so the second time through `buildQuery()`, the SQL statement `SELECT * FROM civireport_contribution_detail_temp3 $this->_orderBy` will never return more than 50, so the pager won't be set.
* `setPager()` assumes that a) the last SQL statement executed includes `SQL_CALC_FOUND_ROWS`; b) that `$this->limit` is set.
Finally, I found a call to `setPager()` in a place where it would never be correct (on a temp table that's not the "final" temp table) so I removed it.5.10.4JonGoldJonGoldhttps://lab.civicrm.org/dev/report/-/issues/9Deprecate `getBasicContactFields` in favor of `getColumns('Contact')`2022-04-22T15:53:16ZJonGoldDeprecate `getBasicContactFields` in favor of `getColumns('Contact')`@eileen has ported some of the cleaner code for defining report specs from Extended Reports to core in the form of `getColumns()` and `getContactColumns()`. Since this appears to be the direction we're headed in, I think it makes sense ...@eileen has ported some of the cleaner code for defining report specs from Extended Reports to core in the form of `getColumns()` and `getContactColumns()`. Since this appears to be the direction we're headed in, I think it makes sense to deprecate `getBasicContactColumns()`, which does a similar job. However, `getBasicContactColumns()` adds a bunch of fields to core reports that `getContactColumns` doesn't.
My PR adds all the missing fields to `getContactColumns` (except for "Organization Name"; this seems unnecessary since it will virtually always match the display name). I also mark `getBasicContactFields()` as deprecated so future cleanup can target reports using it.5.12.0JonGoldJonGoldhttps://lab.civicrm.org/dev/report/-/issues/8Contribution reports don't include thank-you date2022-04-22T15:51:51ZJonGoldContribution reports don't include thank-you dateThis is an issue that a couple of people have noted [on Stack Exchange](https://civicrm.stackexchange.com/questions/15374/thank-you-letter-report/15377). However, this is the first time anyone has paid me to fix it :) PR incoming.This is an issue that a couple of people have noted [on Stack Exchange](https://civicrm.stackexchange.com/questions/15374/thank-you-letter-report/15377). However, this is the first time anyone has paid me to fix it :) PR incoming.JonGoldJonGoldhttps://lab.civicrm.org/dev/report/-/issues/7Transaction Date filter in Bookkeeping Transactions report2022-04-22T17:01:18ZMWestergaardTransaction Date filter in Bookkeeping Transactions reportIn the Bookkeeping Transactions report, the filter for Transaction Date does not include time values and leads to missing data. For example, if today is February 10 and I filter Transaction Date on "Yesterday", the WHERE clause looks li...In the Bookkeeping Transactions report, the filter for Transaction Date does not include time values and leads to missing data. For example, if today is February 10 and I filter Transaction Date on "Yesterday", the WHERE clause looks like:
> ( financial_trxn.trxn_date >= '20190209') AND ( financial_trxn.trxn_date <= '20190209' )
Only transactions that occurred exactly at midnight are included. Contrast that with the filter for Date Received:
> ( contribution.receive_date >= '20190209000000') AND ( contribution.receive_date <= '20190209235959' )
I verified on dmaster and wpmaster.5.12.0https://lab.civicrm.org/dev/report/-/issues/5Correct Mailing Report unique Count2022-04-22T15:51:29ZsunilCorrect Mailing Report unique CountMailing -> Scheduled and Sent Mailings -> Report
Unique Opens -> Report
Total Opens -> Report
Both Report show same count.Mailing -> Scheduled and Sent Mailings -> Report
Unique Opens -> Report
Total Opens -> Report
Both Report show same count.5.10