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     [SOLVED] Set Default Parameter as Min & Max value in Report
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Set Default Parameter as Min & Max value in Report
 Login/Join
 
Gold member
posted
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, 2012Reply With QuoteReport This Post
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, 2005Reply With QuoteReport This Post
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.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6131 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
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, 2005Reply With QuoteReport This Post
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, 2010Reply With QuoteReport This Post
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.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6131 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Gold member
posted Hide Post
Hi all,
Thank you very much this helps! as well i never thought to use from and to Smiler


WebFocus 8.02, SQL Server 2008r2
 
Posts: 51 | Registered: November 30, 2012Reply With QuoteReport This Post
Master
posted Hide Post
Mea culpa.... Thanks njsden


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, 2010Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Set Default Parameter as Min & Max value in Report

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