I am creating a report that returns a list of tasks. The task has a status, a due date, and a completion date. A task is considered overdue if the status is 1 (open), the completion date is null, and the due date has passed.

It my metadata, I created the following field:
DEFINE OVERDUE_FLAG/I4 MISSING ON WITH AI_ACTIONITEMID=IF AI_STATUSID EQ 1.00 AND DTDIFF('&DATEYYMTD' , AI_TARGET_DT, DAY) GT 0 AND DTDIFF('&DATEYYMTD' , AI_COMPLETE_DT , DAY) LE 0 THEN 1 ELSE 0;

I have included this field on my report and it is working properly and can be used to highlight the overdue rows on the report.

The issue is creating a filter that shows all tasks, only overdue tasks, or only on schedule tasks.

I have this parameter:
&&OVERDUE_OPTION, PROMPT='Overdue', USAGE=I4, DEFAULT='_FOC_NULL',
ACCEPT=DECODE(<'All', '_FOC_NULL'>, <'Overdue', '1'>, <'Not Overdue', '0'>), $

And this filter:
SELECT_OVERDUE_OPTION WITH AI_ACTIONITEMID=OVERDUE_FLAG EQ &&OVERDUE_OPTION;

If I select All or Not Overdue, the report returns all records. If I select Overdue, the report returns no records.

How should I go about setting up a filter for this report that satisfies the user requirements? I am using App Studio and WF 8.2.0.3. Thanks.

This message has been edited. Last edited by: FP Mod Chuck,

Your OVERDUE_FLAG is an Integer and you're passing a '1' or '0' which I assume will be a character. Could that be the problem?

You should put the filter in a WHERE:

 
WHERE OVERDUE_FLAG EQ &&OVERDUE_OPTION; 

@BabakNYC: The parameter is defined as an I4, so I would expect it to pass an integer.

@Frans: I have a filter in the business view called SELECT_OVERDUE_OPTION as defined. The where clause is WHERE SELECT_OVERDUE_OPTION; I am prompted for the &&OVERDUE_OPTION, but the results are not as expected. I did try hard-coding the parameter and the criteria into the report instead and the results were the same.

I'd add -SET &ECHO=ALL; to the very top of the report and see what the WHERE test looks like once you've entered a value in the page source.