1

Applying the case type filter in the case summary report CRM_Report_Form_Case_Summary is resulting in two case types being reported where only one is selected in the case type filter.

Looking at the developer tab of the report shows that is is looking for anything that contains a case_type_id of 9 as follows in the where clause:-

WHERE case_civireport.case_type_id  REGEXP '[[:cntrl:]]*9[[:cntrl:]]*'

We have case types in our Civi that have case_type_id's of both 9 and 19, therefore both are being selected when only case type 9 is in the filter.

This doesn't happen with the case detail report CRM_Report_Form_Case_Detail that uses a different select statement as follows:-

WHERE ( case_civireport.case_type_id IN (9) )

I'm not sure if this is just an inconsistency that needs to be fixed, or if it's there for a reason?

Help appreciated.

Improve this question

1 Answer 1

Reset to default
0

It looks like the * was added as part of this: https://github.com/civicrm/civicrm-core/pull/7135

I can't immediately see from that why it was added to the case report, but seems to be related to the "not one of" filter.

I'm thinking all the * should be +, except then it would miss cases that might have somehow been created without the char(1) separators (originally case type was potentially going to be multi-valued, and char(1) is historically how civi separates values in multi-valued fields).

If you change the * to + does it work for you?

You can check your database to see how the case types are actually stored with something like

select case_type_id from civicrm_case where left(case_type_id,1) <> char(1);

If anything is listed then that means the + will miss some cases because you have some case types stored without the cntrl character separators.

Improve this answer
5
  • Interesting, I can see the issue. Having looked at our data, case_type_id is a number that starts at 1 and works its way up as we add case types. I can't see any way that from the admin screens I can edit the case_type_id. In which case, I'm thinking that if no-one can edit that field, then the change to a '+' should be just fine??
    – Oddjob
    May 10, 2019 at 10:59
  • In some databases it will be "☺1☺", i.e. a 1 surrounded by char(1) control characters. The + will work for those, the * is needed for databases like yours where you say you don't have it, except then you have the problem you're seeing. And some databases will be mixed depending on how old they are and how cases get created. Hmm.
    – Demerit
    May 10, 2019 at 14:38
  • I've tried replacing the * with a +, however, then I just get no cases reported at all. If I use the same as the case_detail report, WHERE ( case_civireport.case_type_id IN (9) ) then it works as expected and I get just the right list of cases. I'm not sure how it might affect the 'not' filter.
    – Oddjob
    May 13, 2019 at 9:59
  • I am coming slowly to the conclusion that this report is fundamentally broken, often presenting multiple lines per case and as here, multiple case types when only one is selected. Adding in relationships to this report seems to have caused duplication of case lines, even with the relationship filters and columns switched off. it's been difficult getting my head round trying to guess how it's working, and I'm not sure I'm succeeding. Given the years ago I used to build web sites and write the SQL, if I don't get it, then I'm sure there'll be others that don't. I feel i should raise this ?
    – Oddjob
    May 13, 2019 at 11:05
  • The duplicates caused by having more than one role per case are a known issue, but yes feel free to raise the * part.
    – Demerit
    May 13, 2019 at 11:36

Your Answer

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

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