Clinical Report date range not working

We are running reports from the Clinical Report and it is returning the same number of results no matter the date range that is chosen. This was reported before in 2013. It is happening again.

@visolveemr Any suggestions.

When I dump the sqlstatement I see this:

            concat(pd.fname, ' ', pd.lname) AS patient_name,
   AS patient_id,
            DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(),pd.dob)), '%Y')+0 AS patient_age,
   AS patient_sex,
            pd.race AS patient_race,pd.ethnicity AS patient_ethinic,
            concat(u.fname, ' ', u.lname)  AS users_provider,
            REPLACE(REPLACE(concat_ws(',',IF(pd.hipaa_allowemail = 'YES', 'Allow Email','NO'),IF(pd.hipaa_allowsms = 'YES', 'Allow SMS','NO') , IF(pd.hipaa_mail = 'YES', 'Allow Mail Message','NO') , IF(pd.hipaa_voice = 'YES', 'Allow Voice Message','NO') ), ',NO',''), 'NO,','') as communications from patient_data as pd left outer join users as u on = pd.providerid
        left outer join facility as f on = u.facility_id where 1=1  ORDER BY patient_id

hi @juggernautsei, just ran it on the 5.0.1(3) demo and some of the returned results and checkboxes look a little funny. What are you searching for with this report?

This is the report I they are trying to process.
Are we missing some boxes?

The where 1=1 seems to do nothing and the date range is missing from the query.

I have figured out the problem.
The issue is the $_POST[“form_diagnosis”]) is empty.
I used the file_post_contents to see what was in there and I get nothing.

So, I tried

      $_POST["form_diagnosis"]= "ICD10:M79.7";

Still no change in the query

  //$whr_stmt="where 1=1";
  file_put_contents("where.txt", $form_diagnosis);
if (strlen($form_diagnosis) > 0 || $_POST['form_diagnosis_allergy'] == true || $_POST['form_diagnosis_medprb'] == true) {
    $whr_stmt=" AND >= ? AND < DATE_ADD(?, INTERVAL 1 DAY) AND DATE( <= ?";
    $rags = array_push($sqlBindArray, $sql_date_from, $sql_date_to, date("Y-m-d"));


Any suggestion?

So, I tried changing the strlen from

   strlen($form_diagnosis) > 0 

to this

   strlen($form_diagnosis) == 0 

Got and SQL Query Error

hi @juggernautsei, what if you check Medical problems before you click search? I think it needs a true in the sort condition in order to return results. It’s not filtering on problem dxs though…

Do you see how tiny those are on my screen? Can’t even tell what they are let alone that they should be clicked on. The words Sort By is misleading. That is what you say after the search is conducted.

@juggernautsei @stephenwaite Hey guys,
Though this report is a little confusing, it works as designed. Any selected date range only apply to certain selected items like Medical Problems that where created within date range and so on.
The DX issue is because of a misuse of name attribute on input element around L248:
<td><input type='text' name='form_diagnosis form-control' class= 'form-control'
Note the form-control in name attr which is a BS class.

btw: The where 1=1 is the default search if noting is selected that meets a search option.

1 Like

@sjpadgett @stephenwaite

Thanks for the input. I was digging through the code and getting acclimated to how it is built. The thing I need to add is that we account for active and inactive patients. So the report needs to only show active patients.

How do we make patients inactive?

By placing a 0 in genericname2 column.

is there a reason we don’t have an inactive flag after all this time OpenEMR been around?

Yes, there is not a consensus on how it should be accomplished.

Guess I can see that due to all the scripts that do patient queries. Anyway, for your needs I would just add to the end of the where clause build if chain a AND genericname2 = ‘0’
edit meant genericname2 != ‘0’

Thanks for the advice.

Date range not working on Clinical Report,
When I try to patients from 2022/12/01-2022/12/31
System shows all patients.
I think date range is not working on Clinical Report.
I’m using version 7.