Answers.Body: <p>It looks like the <code>*</code> was added as part of this:
  <a href="https://github.com/civicrm/civicrm-core/pull/7135" rel="nofollow noreferrer">https://github.com/civicrm/civicrm-core/pull/7135</a></p>
  
  <p>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.</p>
  
  <p>I'm thinking all the <code>*</code> should be <code>+</code>, 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).</p>
  
  <p>If you change the <code>*</code> to <code>+</code> does it work for you?</p>
  
  <p>You can check your database to see how the case types are actually stored with something like</p>
  
  <p><code>select case_type_id from civicrm_case where left(case_type_id,1) &lt;&gt; char(1);</code></p>
  
  <p>If anything is listed then that means the <code>+</code> will miss some cases because you have some case types stored without the <code>cntrl</code> character separators.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-05-10T10:59:16.380Z
Answers.Text: 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??
Answers.UserDisplayName: Oddjob
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-05-10T14:38:22.310Z
Answers.Text: 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.
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-05-13T09:59:29.190Z
Answers.Text: 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.
Answers.UserDisplayName: Oddjob
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-05-13T11:05:53.667Z
Answers.Text: 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 ?
Answers.UserDisplayName: Oddjob
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-05-13T11:36:11.367Z
Answers.Text: The duplicates caused by having more than one role per case are a known issue, but yes feel free to raise the * part.
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-05-09T16:32:17.440Z
Answers.LastActivityDate: 2019-05-09T22:49:52.523Z
Answers.LastEditDate: 2019-05-09T22:49:52.523Z
Answers.UserDisplayName: Demerit
Body: <p>Applying the case type filter in the case summary report <em>CRM_Report_Form_Case_Summary</em> is resulting in two case types being reported where only one is selected in the case type filter.</p>
  
  <p>Looking at the developer tab of the report shows that is is looking for anything that contains a case_type_id of <em>9</em> as follows in the where clause:-</p>
  
  <pre><code>WHERE case_civireport.case_type_id  REGEXP '[[:cntrl:]]*9[[:cntrl:]]*'
  </code></pre>
  
  <p>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.</p>
  
  <p>This doesn't happen with the case detail report <em>CRM_Report_Form_Case_Detail</em> that uses a different select statement as follows:-</p>
  
  <pre><code>WHERE ( case_civireport.case_type_id IN (9) )
  </code></pre>
  
  <p>I'm not sure if this is just an inconsistency that needs to be fixed, or if it's there for a reason?</p>
  
  <p>Help appreciated.</p>
  
Tags: <civicase><reports>
Title: Case Type Filter bug in Case Summary Report? Multiple case types reported
ThreadText: # Case Type Filter bug in Case Summary Report? Multiple case types reported
  
  (Asked by: username_0 on 2019-05-09)
  
  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.
  
  **Tags:** civicase, reports
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2019-05-09. Score: 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.
  
  > **Comments:**
  > **username_0**: 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??  
  > **username_1**: 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.  
  > **username_0**: 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.  
  > **username_0**: 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 ?  
  > **username_1**: The duplicates caused by having more than one role per case are a known issue, but yes feel free to raise the * part.  
  
  ---
  
  