Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Finding Overdue Records
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Finding Overdue Records
 Login/Join
 
Member
posted
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,


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 2 | Registered: September 11, 2014Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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?


WebFOCUS 8204, Unix, Windows
 
Posts: 1617 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Guru
posted Hide Post
You should put the filter in a WHERE:

 
WHERE OVERDUE_FLAG EQ &&OVERDUE_OPTION; 


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 381 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
Member
posted Hide Post
@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.


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 2 | Registered: September 11, 2014Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.


WebFOCUS 8204, Unix, Windows
 
Posts: 1617 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Finding Overdue Records

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.