We have a lot of report requiring a date range for users to enter, but we also need a way for users to quick select a time period (last week, last month, last quarter, year to date, etc...) and that auto populates the correct dates for report to run.

We currently have this code in a SQL stored procedure, but trying to figure out a way to get this into a fex file. I havent had any luck searching forums so far. Anyone done this before?

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

Why not create metadata (synonym) (.acx + .mas) for the stored procedure?

Documentation: Adapter Administration for UNIX, Windows, OpenVMS, IBM i, and z/OS, Version 7 Release 7.03 > Using the Adapter for Oracle > Reporting Against an Oracle Stored Procedure

we did that, but my company is trying to see if we can completely move away from stored procedures and have webfocus do everything.
not my call...

Then you'll have to reproduce with WebFOCUS code what the stored procedure is doing. Read the date functions section of the Functions manual.

Here's an example of what can be done:

-SET &ECHO=ALL;

-SET &DT1 = '10312003';
-SET &DT1_YYMD = CHGDAT('MDYY', 'YYMD', &DT1, 'A8');

-*-- Determine Beginning of Week for selected date ---------
-SET &WTDF =
- DATECVT( DATEMOV(DATECVT(&DT1_YYMD,'I8YYMD','YYMD'),'BOW'),
- 'YYMD','I8YYMD');

-*-- Determine Beginning of Month for selected date --------
-SET &MTDF =
- DATECVT( DATEMOV(DATECVT(&DT1_YYMD,'I8YYMD','YYMD'),'BOM'),
- 'YYMD','I8YYMD');

-*-- Determine Beginning of Quarter for selected date ------
-SET &QTDF =
- DATECVT( DATEMOV(DATECVT(&DT1_YYMD,'I8YYMD','YYMD'),'BOQ'),
- 'YYMD','I8YYMD');

-*-- Determine Beginning of Year for selected date ---------
-SET &YTDF =
- DATECVT( DATEMOV(DATECVT(&DT1_YYMD,'I8YYMD','YYMD'),'BOY'),
- 'YYMD','I8YYMD');

-*-- Determine Beginning of Fiscal Year for selected date --
-SET &DT1_YY  = EDIT(&DT1_YYMD, '9999$$$$');
-SET &DT1_MM  = EDIT(&DT1_YYMD, '$$$$99$$');
-SET &FYDF_YY = IF &DT1_MM LE 10 THEN &DT1_YY - 1 ELSE &DT1_YY;
-SET &FYDF    = &FYDF_YY || '1101';

-SET &ECHO=OFF;

-TYPE
-TYPE ------ DATES ------
-TYPE Selected Date (DT1_YYMD) : &DT1_YYMD
-TYPE Beginning of Week (WTDF) : &WTDF
-TYPE Beginning of Month (MTDF) : &MTDF
-TYPE Beginning of Quarter (QTDF) : &QTDF
-TYPE Beginning of Year (YTDF) : &YTDF
-TYPE Beginning of Fiscal Year (FYDF) : &FYDF

quote:

my company is trying to see if we can completely move away from stored procedures and have webfocus do everything.

Good for you... WebFOCUS can do it all... Can't it?

We've added ( and update daily ) columns to the calender dimension.

Relative day ( 0 = today, -1 = yesterday, etc. )
Relative week ( 0 = running week, -1 = last week, etc. )

Works fine.

Im not sure the fex is going to work. Or Im just not figuring out a good way for the fex to work. What we need is a Time Period drop down parameter in the htm file that auto populates the begin and end date parameters in the htm file.

We have someone at our company developing this in html so Im going to see if that ends up working.

Have a prefex getting your dates:

 
in the procedure:
-SET &DefaultBD = ...
-SET &DefaultED = ...
-HTMLFORM myhtm.htm


then in the myhtm.htm for the drop down 'selected value' box put in BD and ED respectively:

BD dropdown Selected Value
!IBI.AMP.DefaultBD;

ED dropdown Selected Value
!IBI.AMP.DefaultED;