Focal Point
[SOLVED] Date Parameters with 12 Month default

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/7437095376

November 18, 2014, 02:15 PM
cldiaz
[SOLVED] Date Parameters with 12 Month default
Hi
I am pretty new to webfocus and am thinking this is a simple question but have been stuck on it for much too long. I have searched documentation and forums and can't seem to get to what I need. We have a report that we would like to be defaulted to the most recent 12 months of data upon opening the HTML page, then have date parameters where the user will be able to select and run a larger or smaller date range if they wish as well as choosing their output. This data set is always 1 month behind so we thought we could just default the start date to 13 months ago and either default end date to 1 month ago or leave it as is. I have had many different attempts using DM with no luck. I know I'm missing something but just can't figure it out. Below is my most recent attempt, which gives me this error - (FOC295) A VALUE IS MISSING FOR: &TODAY_OFFSET
-SET &TODAY_OFFSET = DATECVT(&YYMD , 'I8YYMD' , 'YYMD');
-SET &13MO = DATEADD(&TODAY_OFFSET, 'M', -13);

-DEFAULT &Start_Month_Year_Defined = &13MO;  


Hoping someone might be able to lead me in the right direction! Thanks!

This message has been edited. Last edited by: cldiaz,


WebFOCUS 8204
Windows, All Outputs
November 18, 2014, 02:42 PM
eric.woerle
I'm not sure why you are getting the FOC295 error, but when I did this in my environment, I didn't get that error. But I did have to re-convert the date back to I8YYMD. Please see my code below
 -SET &TODAY_OFFSET = DATECVT(&YYMD , 'I8YYMD' , 'YYMD');
-SET &13MO = DATEADD(&TODAY_OFFSET, 'M', -13);
-SET &13MO_YYMD = DATECVT(&13MO , 'YYMD' , 'I8YYMD');

-TYPE TODAY_OFFSET -> &TODAY_OFFSET 
-TYPE 13MO -> &13MO
-TYPE 13MO_YYMD -> &13MO_YYMD 



FYI,

The DEFAULT command will execute before any of your Dialogue Manager Commands because of the order of operations. If you are thinking that you will be able to set a dynamic default like this, it probably won't work. I would create an HTML front end to handle this. Have you tried using HTML Composer before?

This message has been edited. Last edited by: eric.woerle,


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
November 19, 2014, 03:15 AM
Alan B
This code should not produce a FOC295. Is this part of a larger procedure or just these lines?

What are your settings for parameter prompting?

Do you really want the day portion of the date, or do you need just YYM?

Eric is correct that you will need to convert the date back into readable format form the internal format produced by DATEADD.

But the -DEFAULT should work inline with no problem, however you will need:
-DEFAULT &Start_Month_Year_Defined = &13MO.EVAL


Alan.
WF 7.705/8.007
November 19, 2014, 02:01 PM
cldiaz
Thanks Eric and Alan! My original post just included the DM commands which is what I thought was the root of the error, but after both of your comments I'm thinking I have more than one issue, including my lack of knowledge on how dates work in webfocus. Below is the entire procedure, with some modifications based on your comments. I don't get the FOC295 error anymore but still not having luck. I don't need day included, just month and year.

I have used HTML composer to set static values in date prompts but not dynamic like this. To accomplish what I'm looking for is it better to go the route of doing it in HTML composer instead of the fex?

-SET &TODAY_OFFSET = DATECVT(&YYMD , 'I8YYMD' , 'YYMD');
-SET &13MO = DATEADD(&TODAY_OFFSET, 'M', -13);
-SET &13MO_YYMD = DATECVT(&13MO , 'YYMD' , 'I8YYMD');


-DEFAULT &Start_Month_Year_Defined = &13MO_YYMD.EVAL



TABLE FILE CEOHCAHPS_OVERALL_RATING_
SUM 
     COMPUTE computed_adjusted_score/D4.1% = CEOHCAHPS_OVERALL_RATING_.CEOHCAHPS_OVERALL_RATING_.NUMERATOR / CEOHCAHPS_OVERALL_RATING_.CEOHCAHPS_OVERALL_RATING_.N_SIZE;
BY  HIGHEST CEOHCAHPS_OVERALL_RATING_.CEOHCAHPS_OVERALL_RATING_.Month_Year_Defined
BY  CEOHCAHPS_OVERALL_RATING_.CEOHCAHPS_OVERALL_RATING_.UNIT
     
ON CEOHCAHPS_OVERALL_RATING_.CEOHCAHPS_OVERALL_RATING_.Month_Year_Defined RECOMPUTE AS '*TOTAL'
FOOTING
"Report Ran on <+0>&DATEMDYY "
WHERE ( CEOHCAHPS_OVERALL_RATING_.CEOHCAHPS_OVERALL_RATING_.Month_Year_Defined GE '&Start_Month_Year_Defined.(FIND CEOHCAHPS_OVERALL_RATING_.CEOHCAHPS_OVERALL_RATING_.Month_Year_Defined,CEOHCAHPS_OVERALL_RATING_.CEOHCAHPS_OVERALL_RATING_.Month_Year_Defined IN CEOHCAHPS_OVERALL_RATING_).Select Start Date.' ) AND ( CEOHCAHPS_OVERALL_RATING_.CEOHCAHPS_OVERALL_RATING_.Month_Year_Defined LE '&End_Month_Year_Defined.(FIND CEOHCAHPS_OVERALL_RATING_.CEOHCAHPS_OVERALL_RATING_.Month_Year_Defined,CEOHCAHPS_OVERALL_RATING_.CEOHCAHPS_OVERALL_RATING_.Month_Year_Defined IN CEOHCAHPS_OVERALL_RATING_).End_Month_Year_Defined.' ) AND ( CEOHCAHPS_OVERALL_RATING_.CEOHCAHPS_OVERALL_RATING_.UNIT EQ &UNIT.(OR(FIND CEOHCAHPS_OVERALL_RATING_.CEOHCAHPS_OVERALL_RATING_.UNIT,CEOHCAHPS_OVERALL_RATING_.CEOHCAHPS_OVERALL_RATING_.UNIT IN CEOHCAHPS_OVERALL_RATING_)).Select Unit. );
ON TABLE SET PAGE-NUM OFF 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &WFFMT.(<HTML,HTML>,<PDF,PDF>,<Excel 2007,XLSX>,<Excel 2000,EXL2K>,<Excel 2007 Formula,XLSX FORMULA>,<Excel 2000 Formula,EXL2K FORMULA>,<HTML Active Report,AHTML>,<Active Report for Adobe Flash Player,FLEX>,<Active Report for PDF,APDF>,<PowerPoint,PPT>).Select type of display output.
ON TABLE SET LINES 998
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
TYPE=REPORT,
     FONT='TREBUCHET MS',
     SIZE=9,
     COLOR=RGB(94 37 39),
     BACKCOLOR=RGB(239 225 198),
     STYLE=NORMAL,
$
TYPE=DATA,
     COLOR=RGB(94 37 39),
     BACKCOLOR=( 'WHITE' RGB(239 225 198) ),
$
TYPE=TABFOOTING,
     SIZE=10,
     STYLE=BOLD+ITALIC,
$
TYPE=TABFOOTING,
     LINE=1,
     JUSTIFY=LEFT,
$
TYPE=TABFOOTING,
     LINE=2,
     JUSTIFY=LEFT,
$
TYPE=TABFOOTING,
     LINE=3,
     JUSTIFY=LEFT,
$
TYPE=TABFOOTING,
     LINE=4,
     JUSTIFY=LEFT,
$
TYPE=FOOTING,
     STYLE=BOLD,
     JUSTIFY=LEFT,
$
TYPE=FOOTING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=1,
     STYLE=NORMAL,
$
TYPE=FOOTING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=2,
     STYLE=NORMAL,
$
TYPE=FOOTING,
     LINE=3,
     OBJECT=TEXT,
     ITEM=1,
     STYLE=NORMAL,
$
TYPE=FOOTING,
     LINE=4,
     OBJECT=TEXT,
     ITEM=1,
     STYLE=NORMAL,
$
TYPE=FOOTING,
     LINE=5,
     OBJECT=TEXT,
     ITEM=1,
     STYLE=NORMAL,
$
TYPE=SUBTOTAL,
     COLOR='WHITE',
     BACKCOLOR=RGB(102 102 102),
     STYLE=BOLD,
$
ENDSTYLE
END
  


The month_year_defined is a defined field in the master file. My original date field is formatted as HYYMDS, I then do a define to remove the time using HDATE and have the format as YYMD. Then to get the Month_Year Defined I take my YYMD date and change the format to M-YY.

Thanks again!


WebFOCUS 8204
Windows, All Outputs
November 26, 2014, 03:44 PM
cldiaz
I recieved some help on this from an IBI technical specialist and here is what was done to solve the issue:
1. Add -DEFAULT to get the start and end date, this is used to initially populate the calendar control in the HTML, after you change to use the newly created procedures you comment these out
2. Have the date parameters in the HTML use a calendar control instead of a drop down box
3. The calendar control is dynamically populated using custom procedures, one for start and one for end. Here is a sample of the code for these:
-* get_start_date
         -SET &TODAY_OFFSET = DATECVT(&YYMD , 'I8YYMD' , 'YYMD');
         -SET &13MO = DATEADD(&TODAY_OFFSET, 'M', -13);
         -SET &13MO_YYMD = DATECVT(&13MO , 'YYMD' , 'I8YYMD');

         -SET &Start_Month_Year = EDIT(&13MO_YYMD.EVAL,'$$$$99') || '/' || EDIT(&13MO_YYMD.EVAL, '9999');
         TABLE FILE DATA
         SUM
         FST.DATA.Month_Year_Defined AS 'FST,DATA.Month_Year_Defined'
         BY  DATA.Month_Year_Defined
         WHERE DATA.Month_Year_Defined EQ '&Start_Month_Year';
         ON TABLE SET PAGE-NUM NOLEAD
         ON TABLE NOTOTAL
         ON TABLE PCHOLD FORMAT XML
         END
  


4. Calendar controls do not support the M-YY format so I had to change the Month_Year_Defined field in the master file to be MYY (01/2014).

This message has been edited. Last edited by: cldiaz,
July 07, 2017, 05:41 AM
Nana
Hi all,

I have used this in a fex but it doesn't work
-SET &DATVAN = AYMD (&YYMD, -7, 'I8YYMD');

-DEFAULT &DATUM_VAN_JJJJMMDD =&DATVAN.EVAL

TABLE FILE T_VISIT
PRINT *
WHERE LASTCHANGE GE DT(&DATUM_VAN_JJJJMMDD);
END
-EXIT

By running this fex an error is trown :
0 ERROR AT OR NEAR LINE 7 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC177) INVALID DATE CONSTANT : &DATVAN.EVAL
(FOC009) INCOMPLETE REQUEST STATEMENT

Any help ?


WF 7.6.11
Windows, All Outputs
July 07, 2017, 08:08 AM
MartinY
Nana,

See your other post and please try not to duplicate your post. Thks


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007