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.
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.
Just move that calculation into the filter expression, i.e.
_days_between(current_date,[Appointment Date]) >=9
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!
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:
maximum([Appointment Date] for [Patient Account Number]) < _add_days(current_date,-9)