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>,

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.

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.

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.

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.

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

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

Hi all,
Thank you very much this helps! as well i never thought to use from and to

Mea culpa.... Thanks njsden