trying to filter on difference in dates and receiving true or false

[Ryz]

  I created a column on the Report Page called "Date Difference".  The expression definition is as follows :  (_days_between(current_date,[Appointment Date]) >=9)   
 
  I was hoping to return records to the report where it has been 9 days or more since the Appointment Date.

  Instead, I receive all records back to the report.  The column has a "1" if the expression is True in that there have been 9 or more days since the appointment.  The column has a "0" if the expression is false if there have been 8 or fewer days since the appointment.

  For example :

Appointment Date    Date Difference
--------------------    ------------------
Feb 10, 2021                1
Feb 18, 2021                0
Feb  4, 2021                 1
Feb 26, 2021                0
Feb 26, 2021                0

I would like the report to display only the records where the Date Difference is equal to 1, or true in this case.  I have tried several approaches such as using an " = 1 " at the end of the expression, or trying to "cast" the value to an integer in case the system is seeing it as a boolean, however, I always get a parse error.

Does anyone have a suggestion on how to make this work?  Thank you.

[bus_pass_man]

It's doing exactly what you told it to do.  The problem is that you've created a calculation, not a filter.

What you want to do is create a filter.  Usually you do that by editing the report and choosing the filter button from the on-demand toolbar, and creating a filter expression.  The UI will allow you to either do one from a dialog or from the expression editor. 

Since you say you are using Cognos 10 (which is pretty well out of support I think), you might want to think about creating a stand-alone filter in the FM package with your expression.

[BigChris]

Just move that calculation into the filter expression, i.e.

Code Select Expand

_days_between(current_date,[Appointment Date]) >=9

[Ryz]

Thank you both for your help!  I was able to add the information to the filter.  For some reason when I was dragging items from the Data Items area to the filter area it was preventing me from customizing the filter.  I am not sure why.  I used the tool box to create a filter item.

We have vendor software which still uses Report Studio.  We have no control on how Report Studio is set up.

I think that I am going to have to figure out how to create a subquery in Report Studio.  I have found a couple of sources of information.  I will see if I can make it work.

I don't want patients that were seen less than 9 days ago.  (_days_between(current_date,[Appointment Date]) >=9 )  by itself won't give me what I am looking for.  I think that I will first need a subquery like this :  (maximum([Appointment Date] for [Patient Account Number])

After I have the most recent appointment for the patient, then I should be able to apply the filter to check for whether the date is 9 or more days from the current date.

Thanks!

[BigChris]

Not sure that you even need a subquery, although you could obviously do that and join them together. I think all you need in your filter is something like:

Code Select Expand

maximum([Appointment Date] for [Patient Account Number]) < _add_days(current_date,-9)