Development issueshttps://lab.civicrm.org/groups/dev/-/issues2020-11-09T13:37:51Zhttps://lab.civicrm.org/dev/financial/-/issues/155Incorrect deductible shown in contribution summary report2020-11-09T13:37:51ZMonish DebIncorrect deductible shown in contribution summary reportSteps to replicate:
1. Ensure that there are multiple contributions with non-zero non-deductible amount.
2. Go to Contribution Summary Report
3. Select Non-Deductible amount column + Grouping on Receive Date - Month
Result: Total non-d...Steps to replicate:
1. Ensure that there are multiple contributions with non-zero non-deductible amount.
2. Go to Contribution Summary Report
3. Select Non-Deductible amount column + Grouping on Receive Date - Month
Result: Total non-deductible amount is incorrect against each month
On closer look, group function (- SUM) is not applied on non-deductible amount, thus the report column show random amount.
Here's a screencast that explains the issue, where I added two contributions this month w/o non-deductible amount respectively, but the Contribution summary report shows incorrect value:
![yhv](/uploads/e851d5c3b56e46c1de049d12d1f977ff/yhv.gif)
ping @JoeMurray @eileen @mattwire @EdselopezMonish DebMonish Debhttps://lab.civicrm.org/dev/core/-/issues/2162Reports cannot filter for Contacts with multiple selections in a multi-select...2020-12-02T21:04:03ZjhungerfordReports cannot filter for Contacts with multiple selections in a multi-select Custom FieldOverview
----------------------------------------
If a Contact has multiple selections in a multi-select Custom Field, a Report which filters for "one of" those selections will never find that Contact. There is no "includes" or similar o...Overview
----------------------------------------
If a Contact has multiple selections in a multi-select Custom Field, a Report which filters for "one of" those selections will never find that Contact. There is no "includes" or similar option which finds Contacts who have the specified selection and other selections as well. The generated SQL syntax is not compatible with the storage format of multi-select custom fields when more than one option is selected.
For example: Consider the demo database at https://demo.circle-interactive.co.uk/
It has a Custom Field called "Most Important Issue" with the options "Environment", "Education", and "Social Justice".
Suppose we create another Custom Field which uses the same options, and it is a multi-select. We'll call this field "Important Issues" and check "searchable" and "multi-select".
Now we edit two contacts at random - give one of them "Environment" in their "Important Issues" field, and give the other one "Environment" and "Social Justice" in the same field.
Last, we create a Contact Summary report showing the Important Issues. In the filters, we select "Is one of" - "Environment", and run the report. It will find the Contact who only has "Environment" selected, but the one who has that and "Social Justice" will not be shown.
If you see this issue shortly after it is reported, the described setup may be visible in this report:
https://demo.circle-interactive.co.uk/civicrm/report/instance/39?reset=1&force=1
Reproduction steps
----------------------------------------
- Log into a civi demo site (e.g. demo.circle-interactive.co.uk)
- Create new custom fieldset "Test Report Filter Issue"
- Add a custom field:
- Field Label: Important Issues
- Alphanumeric Select
- Multi-Select is enabled
- Reuse an existing set of multiple choice options if suitable (e.g. from "Most Important Issue") or create a new set
- Searchable: Yes
- Edit two random Contacts
- For one, set the new "Important Issues" field to "Environment"
- For the other, set the new field to "Environment" and "Social Justice"
- Create a new "Constituent Report (Summary)" (/civicrm/report/contact/summary?reset=1)
- in "Columns", tick "First name", "Last name", and "Important Issues" (the new custom field)
- in "Filters", choose "Important Issues" - "Is one of" - "Environment"
- Run the report
Current behaviour
----------------------------------------
The report only finds the Contact who has the single selection.
There is no other way to filter for people who do have the Environment selection (e.g. "includes one of" as distinct from "is exactly one of").
Expected behaviour
----------------------------------------
If some sites do want the "is exactly one of" filter, others (like ours) will need a separate "includes one of" option. I may be able to do this with custom report templates for our site as needed, but a system-wide solution would be much better.
The behaviour appears to have changed when we upgraded to 5.27 from the previous ESR, though we've only noticed it a few months later.
From our perspective, the old behaviour was correct and expected, and the "is one of" text could have been improved by changing it to "includes one of". However, the new behaviour may be desired by some sites. I think that may be implied by this old issue, but I may be misunderstanding since they're also discussing the regex as it relates to the storage format:
https://issues.civicrm.org/jira/browse/CRM-18803
Environment information
----------------------------------------
* __Browser:__ Chrome 86.0.4240.111 (Official Build) Arch Linux (64-bit)
* __CiviCRM:__ 5.27.5, also tested on 5.29.1 and 5.30.1
* __PHP:__ 7.3
* __CMS:__ Drupal 7.73
* __Database:__ MariaDB 10.3
* __Web Server:__ Apache 2.4
Comments
----------------------------------------
A couple of years ago, there was some discussion of a similar issue with contact subtypes, and Eileen wondered whether it might affect custom fields as well:
https://github.com/civicrm/civicrm-core/pull/13158#issuecomment-442334591
At the time, it was still using a regex, but the new SQL generated by the report takes the simpler form "IN ('3')", which will never match a column with a '3' nestled between control codes and other values.
Yesterday when trying to trace the path which generated the SQL, I thought this line might be relevant:
https://lab.civicrm.org/dev/core/-/blob/master/CRM/Core/BAO/CustomField.php#L2583-2584
Considering that the new behaviour might be deliberate, I thought I should report the issue rather than trying to find a solution since the correct path forward is not obvious to me.5.33.0https://lab.civicrm.org/dev/core/-/issues/2104Add more columns for Activity Report2023-05-03T05:47:11ZyashodhaAdd more columns for Activity ReportAdd more columns for Activity Report e,g target related fields currently it is showing target name and email only.Add more columns for Activity Report e,g target related fields currently it is showing target name and email only.5.62.0yashodhayashodhahttps://lab.civicrm.org/dev/core/-/issues/3198Can't see Soft Credit fields on a Contribution Detail Report?2023-12-18T05:03:27ZswebervnaCan't see Soft Credit fields on a Contribution Detail Report?When I add Soft Credit columns to a Contribution Detail Report, the data doesn't show - they're just empty cells:
![image](/uploads/63ee33909c9e1a3b9e314bbd3cd0308e/image.png)
(That first row/entry you see is in fact a contribution with...When I add Soft Credit columns to a Contribution Detail Report, the data doesn't show - they're just empty cells:
![image](/uploads/63ee33909c9e1a3b9e314bbd3cd0308e/image.png)
(That first row/entry you see is in fact a contribution with a soft credit, I checked.)https://lab.civicrm.org/dev/core/-/issues/3217[Feature] Please give us the option to disable subtotals for Soft Credit Cont...2023-12-26T05:03:25Zswebervna[Feature] Please give us the option to disable subtotals for Soft Credit Contribution ReportsCurrently, Soft Credit Contribution Reports always output subtotal: ![image](https://lab.civicrm.org/dev/mail/uploads/39254fd8a1be8b42d9889da4230d91d0/image.png)
I think it would be nice to disable this as an option, so we can remove th...Currently, Soft Credit Contribution Reports always output subtotal: ![image](https://lab.civicrm.org/dev/mail/uploads/39254fd8a1be8b42d9889da4230d91d0/image.png)
I think it would be nice to disable this as an option, so we can remove the subtotal if we don't need them.https://lab.civicrm.org/dev/core/-/issues/1991Fix the counter-intuitive steps required to save a new CiviCRM Report, which ...2020-09-17T09:47:10Zjustinfreeman (Agileware)Fix the counter-intuitive steps required to save a new CiviCRM Report, which currently requires: Clicking the View the results button and then selecting Save Report from the Actions menuFix the counter-intuitive steps required to save a new CiviCRM Report, which currently requires: Clicking the View the results button and then selecting Save Report from the Actions menu.
What users actually expect to see as a button on...Fix the counter-intuitive steps required to save a new CiviCRM Report, which currently requires: Clicking the View the results button and then selecting Save Report from the Actions menu.
What users actually expect to see as a button on the CiviCRM Report page is just simple "Save report" as a button.
Having the action only shown after the results of the report are visible is the problem. This also prevents a report from being saved which does not currently show any results now, but may show results in the future - when the criteria is met.https://lab.civicrm.org/dev/core/-/issues/1948Print Report from Manage Case shows relationships from client's other cases2023-07-03T18:38:08ZDaveDPrint Report from Manage Case shows relationships from client's other casesNot sure yet when it started.
1. Create two cases for a client with different case managers assigned.
2. Click on Print Report on Manage Case for either case.
3. It lists the case manager from the other case in the Client Relationships ...Not sure yet when it started.
1. Create two cases for a client with different case managers assigned.
2. Click on Print Report on Manage Case for either case.
3. It lists the case manager from the other case in the Client Relationships section.https://lab.civicrm.org/dev/core/-/issues/3259Add the possibility to use the hook_civicrm_alterReportVar('sql') in all reports2022-04-22T15:53:00ZrubofvilAdd the possibility to use the hook_civicrm_alterReportVar('sql') in all reportsSome reports have rewrited the method "buildQuery" from class CRM_Report_Form. Not in all reports is included the line to launch the hook `hook_civicrm_alterReportVar` -> `CRM_Utils_Hook::alterReportVar('sql', $this, $this);`
Example
h...Some reports have rewrited the method "buildQuery" from class CRM_Report_Form. Not in all reports is included the line to launch the hook `hook_civicrm_alterReportVar` -> `CRM_Utils_Hook::alterReportVar('sql', $this, $this);`
Example
https://github.com/civicrm/civicrm-core/blob/abc4afe890add20796585a2a997bc3d832815abe/CRM/Report/Form/Member/ContributionDetail.php#L552https://lab.civicrm.org/dev/core/-/issues/1855Allow different output formats for CiviReport results, like native excel form...2020-09-03T15:17:35ZDaveDAllow different output formats for CiviReport results, like native excel format, and untangle codeThis [started as a question](https://github.com/civicrm/civicrm-core/pull/17145) like "without having to patch core or adding another `if outputformat == 'x'` into some already awkward code in core, how can an extension implement a new o...This [started as a question](https://github.com/civicrm/civicrm-core/pull/17145) like "without having to patch core or adding another `if outputformat == 'x'` into some already awkward code in core, how can an extension implement a new output format for civireport results?"
My own motivation for this is a couple things:
1. I've had to look at the related code blocks more than once and it takes at least 15 minutes just to wade through and get to a point where you can start looking into what you were originally trying to look into. Then you get lost again while circling back.
1. I like the idea of being able to prevent people sending me listings of contacts in *pdf* format. No I will not import or analyze this data you've sent me in pdf format. (You can prevent this currently, it would just be easier after.)
1. The email body when sent by the mail_report job is hardcoded. There's been one or two requests to have it be more configurable. You can sort of do it with hook_alterMailParams now but it's not the most robust. I'm not fully addressing that here with any UI changes, but the proposed changes set the stage for it being easier to get there. At the very least you would now be able to write an extension that simply extends the existing handler class (e.g. csv) and overrides the small function that returns the text.
1. I'm currently doing this a different way and don't know if I would switch, but it opens up the possibility for an output handler that provides templates into which the data is inserted which then automatically is available in the existing UI and mail_report job. For most people this would mean a pdf template, but for people like me this means something like an excel template with a prebuilt pivot table and the handler would update the source rows so the pivot would automatically update.
And then beyond my own motivation some other possibilities which are theoretical:
1. A variation of the last point above would be an outputhandler where when run by mail_report it connects to another network service and sends the data there server-to-server. You could set this up via cron exactly the same way you normally use the mail_report job. The equivalent download would then still also be automatically available to users to do manually the same as any other civireport.
## So...
After doing some investigating, can summarize how the current output code works as
* type `sendmail`, which gets the output as a string and sends an email, OR
* type `not sendmail`, which starts a download. Note that "download" technically is also a string, but sent to the browser instead of used in an email.
Then further
* Some of the convoluted nature of the existing code is partly because how to get either of those is different depending on the output format (e.g. csv/pdf/etc).
* Also note that "download" for type 'print' is actually the same as other downloads it's just that you don't need to change http headers first. So that's also adding to the awkwardness a bit in the current code because the way it's written it almost seems like a different code path.
**THEREFORE --->**: My thinking is to start on this by having a common interface to get the string. I mean interface in the general sense of the word, not necessarily OO Interface but maybe. But **then it reduces the number of if/else's to just one**:
* If sendmail,
* Get string (delegated to the output handler).
* Send email (for now extract this to its own function just for readability).
* Else
* Delegate to output handler to set headers and send string to browser (aka download).
There's a little more to it, and it can borrow a bit from the existing PRs, but that's what I'm thinking in terms of first steps to untangling. I will work on a PR.
Then the next steps after that would be to allow for other output handlers, whether it be by a new hook or existing hook or other.
#### Misc notes
* save/copy/delete are handled in beginPostProcess and do nothing in endPostProcess
* there's a difference between compileContent() and the output content. compileContent() is not used for csv, but is used for pdf and 'print'.
* endPostProcess has 4 different "categories":
1. "add contacts to group", which doesn't output anything
2. _sendmail, which uses the other output handlers but requests the results as a string.
* defaults to print, but also does csv or pdf and then exits
* There is also some hardcoding of the email body, partly because it includes a computed url, but it would be nice not to hardcode this.
* Note pdf is called with TRUE for 3rd param, so that it returns the pdf as a string.
3. Download, which uses the other output handlers but requests the results as a download.
* Note that print is a download technically, it just goes to the browser window. Csv or pdf starts an attachment download.
* Here pdf is the default, but at the point in the code where that happens it has to be either print or pdf and print would have been already handled.
* Note pdf is called with FALSE for 3rd param.
* csv is done by a wrapper around the same function that is used in _sendmail
4. Tests that extend CiviReportTestCase use _resultSet/getResultSet() and have _outputMode blank, so endPostProcess (which gets called from preProcess because force=1 here) does none of the above and just stores the results in an array.5.29.0https://lab.civicrm.org/dev/core/-/issues/1839Notice error Contribution Aggregate by Relationship report2023-03-29T05:03:21ZJoeMurrayNotice error Contribution Aggregate by Relationship reportOn dmaster just now (5.28.alpha1):
Notice: Undefined variable: entryFound in CRM_Report_Form_Contribute_History->alterDisplay() (line 842 of /srv/buildkit/build/dmaster/web/sites/all/modules/civicrm/CRM/Report/Form/Contribute/History.php).On dmaster just now (5.28.alpha1):
Notice: Undefined variable: entryFound in CRM_Report_Form_Contribute_History->alterDisplay() (line 842 of /srv/buildkit/build/dmaster/web/sites/all/modules/civicrm/CRM/Report/Form/Contribute/History.php).https://lab.civicrm.org/dev/core/-/issues/3238Graphs on Contribution Summary report replace final row with grand total value2022-04-22T15:51:46ZAndrew WestGraphs on Contribution Summary report replace final row with grand total valueGraphs on the Contribution Summary report will include the grand total in some circumstances. To replicate on demo site:
1. Using demo data, launch the Contribution Summary report
2. Leave all settings as default
3. Refresh results a...Graphs on the Contribution Summary report will include the grand total in some circumstances. To replicate on demo site:
1. Using demo data, launch the Contribution Summary report
2. Leave all settings as default
3. Refresh results and note the grand total. Current data shows this:
![Annotation_2020-05-20_211434](/uploads/17d1b5942dde265bf0076d857396e46b/Annotation_2020-05-20_211434.png)
4. Switch to a bar chart in the top right and click 'View'. Hover over the final column and you'll see it's the grand total number:
![barchart](/uploads/3454788b2eecfc7ea4b8d44538b371d6/barchart.png)
I'm not terribly familiar with reports, but my initial fix was to add this to the top of CRM_Report_Form_Contribute_Summary::buildChart()
if ($this->_rollup) {
array_pop($rows);
}
If this makes sense I can make it a PR.5.28.0https://lab.civicrm.org/dev/core/-/issues/1729CiviReport "Source Name" filter is broken / has no impact on report results2020-04-30T22:59:59ZmarshCiviReport "Source Name" filter is broken / has no impact on report resultsOverview
----------------------------------------
Setting the "Source Name" filter on a report has no effect on the results
Reproduction steps
----------------------------------------
1. open [Activity Details report](https://dmaster.de...Overview
----------------------------------------
Setting the "Source Name" filter on a report has no effect on the results
Reproduction steps
----------------------------------------
1. open [Activity Details report](https://dmaster.demo.civicrm.org/civicrm/report/instance/3?reset=1&output=criteria).
1. go to **filters** (tab)
1. **Source Name** : Contains : [any text]
1. Preview results
Current behaviour
----------------------------------------
- notice 80 results
- **results do not change** when changing the 'source name' filter
- no errors
Expected behaviour
----------------------------------------
- report results get filtered, so different filter values give different results
Environment information
----------------------------------------
testing and workflow was on https://dmaster.demo.civicrm.org/
Comments
----------------------------------------
This looks like a regression, and could be from:
- https://github.com/civicrm/civicrm-core/pull/16672
- https://lab.civicrm.org/dev/core/issues/1627
(thanks to Pradeep Nayak for investigations)5.24.6https://lab.civicrm.org/dev/core/-/issues/1710Activity Detail report gives syntax error with force=1 if include case activi...2020-04-28T19:40:48ZelilisseckActivity Detail report gives syntax error with force=1 if include case activities is onDirectly related to: https://lab.civicrm.org/dev/core/-/issues/1552
Summary from 1552: On the Activity Details report, when the filter "Include Case Activities" is set to "Yes" the report can generate an SQL syntax error as follows:
``...Directly related to: https://lab.civicrm.org/dev/core/-/issues/1552
Summary from 1552: On the Activity Details report, when the filter "Include Case Activities" is set to "Yes" the report can generate an SQL syntax error as follows:
```
`[type] => DB_Error
[user_info] => CREATE TEMPORARY TABLE civicrm_tmp_e_dflt_607d7436413c499f88a03a05edf1fd9c ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci AS SELECT civicrm_contact_target.sort_name as civicrm_contact_contact_target, civicrm_contact_target.id as civicrm_contact_contact_target_id, activity_civireport.id as civicrm_activity_id, activity_civireport.source_record_id as civicrm_activity_source_record_id, activity_civireport.activity_type_id as civicrm_activity_activity_type_id, activity_civireport.subject as civicrm_activity_activity_subject, activity_civireport.activity_date_time as civicrm_activity_activity_date_time, activity_civireport.status_id as civicrm_activity_status_id, address_civireport.street_name as civicrm_address_street_name, address_civireport.street_number as civicrm_address_street_number, address_civireport.street_address as civicrm_address_street_address, address_civireport.city as civicrm_address_city, address_civireport.postal_code as civicrm_address_postal_code
FROM civicrm_activity activity_civireport
INNER JOIN civicrm_activity_contact activity_contact_civireport
ON activity_civireport.id = activity_contact_civireport.activity_id AND
activity_contact_civireport.record_type_id = 3
INNER JOIN civicrm_contact civicrm_contact_target
ON activity_contact_civireport.contact_id = civicrm_contact_target.id
LEFT JOIN civicrm_case_activity
ON .activity_id = activity_civireport.id
LEFT JOIN civicrm_address address_civireport
ON (civicrm_contact_target.id =
address_civireport.contact_id) AND
address_civireport.is_primary = 1
WHERE activity_civireport.is_test = 0 AND
activity_civireport.is_deleted = 0 AND
activity_civireport.is_current_revision = 1 AND ( activity_civireport.activity_date_time >= 20200401000000) AND ( activity_civireport.activity_date_time <= 20200430235959) AND ( activity_civireport.activity_type_id IN (66,27,20,19,37,29,18,16,53,26,15,33,35,36,48,52,51,6,42,49,43,3,50,5,41,54,14,12,45,62,55,25,64,34,56,1,8,17,7,58,23,13,4,46,32,2,30,10,11,22,24,47,40,21,44,60,28,9,39,38,67,68,31) ) [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= activity_civireport.id
LEFT JOIN civicrm_address address_civi' at line 10]`
```
The exact same syntax error still exists in a demo install of 5.26.alpha1 but only when the direct "View Results" link is used without first viewing report criteria i.e. the report form is viewed with a param like "force=1" or "output=result" as it generally would be in a direct menu link to that report.
Further, it seems the following notices appear only when the Activity Detail report result are accessed directly. These notices to not appear when one views criteria first and clicks "view results":
`Notice: Undefined index: civicrm_case_activity in CRM_Report_Form_Activity->buildFrom() (line 1157 of /home/eli/buildkit/build/drupal526/web/sites/all/modules/civicrm/CRM/Report/Form/Activity.php).`
`Notice: Undefined offset: 3 in CRM_Report_Utils_Report::getInstanceID() (line 307 of /home/eli/buildkit/build/drupal526/web/sites/all/modules/civicrm/CRM/Report/Utils/Report.php).`
It appears `{$this->_aliases['civicrm_case_activity']}` is only populated when the report criteria is accessed before results.5.25.0https://lab.civicrm.org/dev/core/-/issues/1673Report Dev tab display SQL multiple times2021-05-17T21:39:04ZMonish DebReport Dev tab display SQL multiple timesOverview
----------------------------------------
Report Dev tab prints single SQL multiple times.
Reproduction steps
----------------------------------------
1. Click on any core Report, say Contribution Summary Report
1. Simply submit...Overview
----------------------------------------
Report Dev tab prints single SQL multiple times.
Reproduction steps
----------------------------------------
1. Click on any core Report, say Contribution Summary Report
1. Simply submit the result
1. Go to Dev tab
Current behavior
----------------------------------------
You will find a single report SQL is printed multiple times:
![Screen_Shot_2020-03-27_at_11.27.16_AM](/uploads/ebc4667a578bbb9dfddf10d2acb5fa01/Screen_Shot_2020-03-27_at_11.27.16_AM.png)
Expected behavior
----------------------------------------
Dev page should display unique report SQL5.26.0seamusleeseamusleehttps://lab.civicrm.org/dev/core/-/issues/3202In membership detail report the join date field has a different output date f...2023-11-10T00:08:12ZDaveDIn membership detail report the join date field has a different output date format than the other date fieldsCan reproduce on dmaster.demo.
On the membership details report in the columns tab select join date as a field. When you run the report the join date column has a different date format than the start/end date. It seems to always be yyyy...Can reproduce on dmaster.demo.
On the membership details report in the columns tab select join date as a field. When you run the report the join date column has a different date format than the start/end date. It seems to always be yyyy-mm-dd.
Doesn't seem to be a recent issue.5.69.0https://lab.civicrm.org/dev/core/-/issues/3283Contribution Summary Report: The "general total" row does not take the curren...2022-04-22T15:53:43ZdmunioContribution Summary Report: The "general total" row does not take the currency filteredWhen the contribution summary report is used by filtering for a currency other than the site's default currency, the "grand total" row shows the sign of the default currency instead of the filtered currency.
![image](/uploads/e65afcbdde...When the contribution summary report is used by filtering for a currency other than the site's default currency, the "grand total" row shows the sign of the default currency instead of the filtered currency.
![image](/uploads/e65afcbdde35490ab7602d21161c13f0/image.png)
This issue is resolved by changing the code of the following line: https://github.com/civicrm/civicrm-core/blob/2235525e475edd2573a1ad71897cd42d2ea3cdfd/templates/CRM/Report/Form/Layout/Table.tpl#L139
By the following code:
```php
{if $currencyColumn}
{$grandStat.$field|crmMoney:$row.$currencyColumn}
{else}
{$grandStat.$field|crmMoney}
{/if}
```5.29.0https://lab.civicrm.org/dev/core/-/issues/1627Activity Report: filtering by "is null" or "is not null" is ignored2020-04-23T14:15:36ZJonGoldActivity Report: filtering by "is null" or "is not null" is ignoredOverview
----------------------------------------
On the Activity Details report, any filter that allows selecting "Is Null" or "Is Not Null" is broken (with the possible exception of the Source/Target/Assignee Contact filters).
Reprodu...Overview
----------------------------------------
On the Activity Details report, any filter that allows selecting "Is Null" or "Is Not Null" is broken (with the possible exception of the Source/Target/Assignee Contact filters).
Reproduction steps
----------------------------------------
* Go to the Activity Details report.
* Remove the "Activity Date is this month" default filter.
* Run the report. Observe the total number of activities in the database.
* Filter a field that allows null/not null (e.g. *Location*) to **Is Null**.
* Run the report again. Observe that the total number of activities is the same (but that could be legitimate).
* Filter the same field to **Is Not Null**.
* Run the report one more time. Observe that the total number of activities is *still* the same.
Current behaviour
----------------------------------------
Filtering by "is null" or "is not null" is ignored.
Expected behaviour
----------------------------------------
Filtering by "is null" or "is not null" should work.
Comments
----------------------------------------
This was broken by the fix to [CRM-18356](https://issues.civicrm.org/jira/browse/CRM-18356). Since my initial attempts at fixing this reintroduced the bug described there, I wrote tests for both scenarios to lock both fixes in.5.24.0JonGoldJonGoldhttps://lab.civicrm.org/dev/core/-/issues/3201Participant Listing report Unknown column 'contact_civireport.age_at_event2023-12-20T05:03:24ZEmerykZielinskiParticipant Listing report Unknown column 'contact_civireport.age_at_eventHey all,
when I try to sort Participant Listing report by age of the Participant (in the time of the event) i get the following error:
`[debug] $backTrace = #0 /var/www/html/sites/all/modules/civicrm/CRM/Core/Error.php(208): CRM_Core_E...Hey all,
when I try to sort Participant Listing report by age of the Participant (in the time of the event) i get the following error:
`[debug] $backTrace = #0 /var/www/html/sites/all/modules/civicrm/CRM/Core/Error.php(208): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 /var/www/html/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(922): CRM_Core_Error::handle(Object(DB_Error))
#2 /var/www/html/sites/all/modules/civicrm/packages/DB.php(987): PEAR_Error->__construct("DB Error: no such field", -19, 16, (Array:2), "SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_so...")
#3 /var/www/html/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(575): DB_Error->__construct(-19, 16, (Array:2), "SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_so...")
#4 /var/www/html/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(223): PEAR->_raiseError(Object(DB_mysqli), NULL, -19, 16, (Array:2), "SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_so...", "DB_Error", TRUE)
#5 /var/www/html/sites/all/modules/civicrm/packages/DB/common.php(1920): PEAR->__call("raiseError", (Array:7))
#6 /var/www/html/sites/all/modules/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-19, NULL, NULL, "SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_so...", "1054 ** Unknown column 'contact_civireport.age_at_event' in 'field list'")
#7 /var/www/html/sites/all/modules/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError()
#8 /var/www/html/sites/all/modules/civicrm/packages/DB/common.php(1229): DB_mysqli->simpleQuery("SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_so...")
#9 /var/www/html/sites/all/modules/civicrm/packages/DB/DataObject.php(2416): DB_common->query("SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_so...")
#10 /var/www/html/sites/all/modules/civicrm/packages/DB/DataObject.php(1607): DB_DataObject->_query("SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_so...")
#11 /var/www/html/sites/all/modules/civicrm/CRM/Core/DAO.php(435): DB_DataObject->query("SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_so...")
#12 /var/www/html/sites/all/modules/civicrm/CRM/Core/DAO.php(1428): CRM_Core_DAO->query("SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_so...", TRUE)
#13 /var/www/html/sites/all/modules/civicrm/CRM/Report/Form.php(3076): CRM_Core_DAO::executeQuery("SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_so...")
#14 /var/www/html/sites/all/modules/civicrm/CRM/Report/Form.php(3561): CRM_Report_Form->buildRows("SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_so...", (Array:0))
#15 /var/www/html/sites/all/modules/civicrm/CRM/Report/Form/Event/ParticipantListing.php(616): CRM_Report_Form->postProcess()
#16 /var/www/html/sites/all/modules/civicrm/CRM/Core/Form.php(495): CRM_Report_Form_Event_ParticipantListing->postProcess()
#17 /var/www/html/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Submit.php(74): CRM_Core_Form->mainProcess()
#18 /var/www/html/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Submit->perform(Object(CRM_Report_Form_Event_ParticipantListing), "submit")
#19 /var/www/html/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Report_Form_Event_ParticipantListing), "submit")
#20 /var/www/html/sites/all/modules/civicrm/CRM/Core/Controller.php(351): HTML_QuickForm_Page->handle("submit")
#21 /var/www/html/sites/all/modules/civicrm/CRM/Utils/Wrapper.php(114): CRM_Core_Controller->run()
#22 /var/www/html/sites/all/modules/civicrm/CRM/Report/Page/Instance.php(90): CRM_Utils_Wrapper->run("CRM_Report_Form_Event_ParticipantListing", NULL, (Array:0))
#23 /var/www/html/sites/all/modules/civicrm/CRM/Core/Invoke.php(284): CRM_Report_Page_Instance->run((Array:4), NULL)
#24 /var/www/html/sites/all/modules/civicrm/CRM/Core/Invoke.php(84): CRM_Core_Invoke::runItem((Array:14))
#25 /var/www/html/sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:4))
#26 /var/www/html/sites/all/modules/civicrm/drupal/civicrm.module(444): CRM_Core_Invoke::invoke((Array:4))
#27 /var/www/html/includes/menu.inc(527): civicrm_invoke("report", "instance", "26")
#28 /var/www/html/index.php(21): menu_execute_active_handler()
#29 {main}`
is there a way to set up the column `contact_civireport.age_at_event` by myself?https://lab.civicrm.org/dev/core/-/issues/1619Activity Summary civireport gives fatal error when grouping activity date by ...2020-02-27T23:22:50ZDaveDActivity Summary civireport gives fatal error when grouping activity date by quarterNot working since at least 5.20. Haven't checked farther back.
```
INSERT INTO civicrm_tmp_e_dflt_c96bd73d5a616c0a85f8b776fdaf8a13 ( civicrm_contact_id,civicrm_activity_activity_date_time_start,civicrm_activity_activity_date_time_interv...Not working since at least 5.20. Haven't checked farther back.
```
INSERT INTO civicrm_tmp_e_dflt_c96bd73d5a616c0a85f8b776fdaf8a13 ( civicrm_contact_id,civicrm_activity_activity_date_time_start,civicrm_activity_activity_date_time_interval,civicrm_activity_activity_date_time_subtotal,civicrm_activity_activity_type_id,civicrm_activity_duration,civicrm_activity_id_count )
SELECT SQL_CALC_FOUND_ROWS contact_civireport.id as civicrm_contact_id, STR_TO_DATE(CONCAT( 3 * QUARTER( activity_civireport.activity_date_time ) -2 , '/', '1', '/', YEAR( activity_civireport.activity_date_time ) ), '%m/%d/%Y') AS civicrm_activity_activity_date_time_start, QUARTER(activity_civireport.activity_date_time) AS civicrm_activity_activity_date_time_subtotal, QUARTER(activity_civireport.activity_date_time) AS civicrm_activity_activity_date_time_interval, GROUP_CONCAT(DISTINCT activity_civireport.activity_type_id ORDER BY activity_civireport.activity_type_id ) as civicrm_activity_activity_type_id, activity_civireport.duration as civicrm_activity_duration, COUNT(DISTINCT(activity_civireport.id)) as civicrm_activity_id_count
FROM civicrm_activity activity_civireport
LEFT JOIN civicrm_activity_contact target_activity
ON activity_civireport.id = target_activity.activity_id AND
target_activity.record_type_id = 3
LEFT JOIN civicrm_activity_contact assignment_activity
ON activity_civireport.id = assignment_activity.activity_id AND
assignment_activity.record_type_id = 1
LEFT JOIN civicrm_activity_contact source_activity
ON activity_civireport.id = source_activity.activity_id AND
source_activity.record_type_id = 2
LEFT JOIN civicrm_contact contact_civireport
ON target_activity.contact_id = contact_civireport.id
LEFT JOIN civicrm_contact civicrm_contact_assignee
ON assignment_activity.contact_id = civicrm_contact_assignee.id
LEFT JOIN civicrm_contact civicrm_contact_source
ON source_activity.contact_id = civicrm_contact_source.id
LEFT JOIN civicrm_option_value
ON ( activity_civireport.activity_type_id = civicrm_option_value.value )
LEFT JOIN civicrm_option_group
ON civicrm_option_group.id = civicrm_option_value.option_group_id
LEFT JOIN civicrm_case_activity
ON civicrm_case_activity.activity_id = activity_civireport.id
LEFT JOIN civicrm_case
ON civicrm_case_activity.case_id = civicrm_case.id
LEFT JOIN civicrm_case_contact
ON civicrm_case_contact.case_id = civicrm_case.id WHERE civicrm_option_group.name = "activity_type" AND
activity_civireport.is_test = 0 AND
activity_civireport.is_deleted = 0 AND
activity_civireport.is_current_revision = 1 AND ((`activity_civireport`.`activity_type_id` IS NULL OR (`activity_civireport`.`activity_type_id` IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 53, 33, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 54, 55, 56, 58, 60, 62, 64, 66))) OR civicrm_contact_source.is_deleted=0 OR civicrm_contact_assignee.is_deleted=0) GROUP BY YEAR(activity_civireport.activity_date_time), QUARTER(activity_civireport.activity_date_time), contact_civireport.id, STR_TO_DATE(CONCAT( 3 * QUARTER( activity_civireport.activity_date_time ) -2 , '/', '1', '/', YEAR( activity_civireport.activity_date_time ) ), '%m/%d/%Y'), QUARTER(activity_civireport.activity_date_time), activity_civireport.duration ORDER BY activity_civireport.activity_type_id ASC LIMIT 0, 50 [nativecode=1055 ** Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'core16560724hxcivi_syiur.activity_civireport.activity_type_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by]
```5.24.0https://lab.civicrm.org/dev/core/-/issues/1596unreleased regression - Contribution summary report gives fatal error in mast...2020-02-25T20:25:25ZDaveDunreleased regression - Contribution summary report gives fatal error in master with just the defaultsI think it's from this line https://github.com/civicrm/civicrm-core/pull/16467/files#diff-c2f466cf0d5fef7048b1ec82ec5aaaceR615 which then adds a grouping parameter after WITH ROLLUP, which it doesn't like.I think it's from this line https://github.com/civicrm/civicrm-core/pull/16467/files#diff-c2f466cf0d5fef7048b1ec82ec5aaaceR615 which then adds a grouping parameter after WITH ROLLUP, which it doesn't like.5.24.0