[SOLVED] Set Default Parameter as Min & Max value in Report

I have a weekly reoccurring report and when the report is executed there are 2 parameters which drives it. The parameters are Start Date and End Date, I would like to set the Start Date and End Data parameters to default to MIN (Start Date) and MAX (Start Date)

I'm trying to accomplish this but can't seem to figure it out:

WHERE WORK_DATE GE MIN(TABLE.WORK_DATE );
WHERE WORK_DATE LE MAX(TABLE.WORK_DATE );

I'm not sure what i'm doing wrong...

This message has been edited. Last edited by: <Kathryn Henning>,

WebFocus 8.02, SQL Server 2008r2

Posts: 51 | Registered: November 30, 2012

njsden

Virtuoso

posted

Hide Post

You need to "capture" your MIN/MAX value first, and then use them to drive your report:

TABLE FILE MYTABLE
SUM
   MIN.WORK_DATE AS 'MIN_WORK_DATE'
   MAX.WORK_DATE AS 'MAX_WORK_DATE'
ON TABLE SET ASNAMES ON
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS HDATES
END
-RUN

-READFILE HDATES

TABLE FILE MYTABLE
PRINT
   COLUMN1
   COLUMN2
   COLUMN3
BY WORK_DATE

WHERE WORK_DATE FROM '&MIN_WORK_DATE' TO '&MAX_WORK_DATE'
END

You may also try some of the very cool features I've heard are available in WebFOCUS 8, such as better support for subqueries and such to see if you can achieve that in a single round-trip to the database, but the technique above should get you going for now.

Hope that helps.

Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.

Posts: 1533 | Registered: August 12, 2005

Ignored post by njsden posted

Show Post

Waz

Expert

posted

Hide Post

You can also reduce this code.

TABLE FILE MYTABLE
SUM
   COMPUTE MIN_WORK_DATE/YYMD = MIN.WORK_DATE ;
   COMPUTE MAX_WORK_DATE/YYMD = MAX.WORK_DATE ;
PRINT
   COLUMN1
   COLUMN2
   COLUMN3
BY WORK_DATE

WHERE TOTAL WORK_DATE FROM MIN_WORK_DATE TO MAX_WORK_DATE.

ON TABLE SET ASNAMES ON
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS HDATES
END
-RUN

Depending on the data source, the different examples may perform better than the other.

Waz...

Prod:	WebFOCUS 7.6.10/8.1.04	Upgrade:	WebFOCUS 8.2.07	OS:	Linux	Outputs:	HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006

Ignored post by Waz posted

Show Post

j.gross

Virtuoso

posted

Hide Post

quote:
... to default to MIN (Start Date) and MAX (Start Date)

If the report needs to be runnable both in batch (using its defaults) and from a launch page (which always supplied dates), you can have

-DEFAULT &start_date = '?';
-DEFAULT &end_date = '?';

followed by dialog manager code to either use the received parameter values (if they over-ride the '?'default values) or look up the min and max values and apply them (if the values are still '?').

But you'll need to use a launch page, not autoprompt.

- Jack Gross
WF through 8.1.05

Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005

Ignored post by j.gross posted

Show Post

George Patton

Master

posted

Hide Post

quote:
FROM MIN_WORK_DATE TO MAX_WORK_DATE

Interesting. I've never used FROM ... TO, always just GE DATE1 AND LE DATE2

FROM .. TO is more elegant ...

Thanks Waz.

WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP

Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010

Ignored post by George Patton posted

Show Post

Waz

Expert

posted

Hide Post

Hey George, its wasn't me it was njsden.

I also tend to use GE and LE, perhaps its old school ?

Waz...

Prod:	WebFOCUS 7.6.10/8.1.04	Upgrade:	WebFOCUS 8.2.07	OS:	Linux	Outputs:	HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!