Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Date Parameters with 12 Month default

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Date Parameters with 12 Month default
 Login/Join
 
Silver Member
posted
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
 
Posts: 47 | Registered: March 21, 2014Report This Post
Master
posted Hide Post
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
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Silver Member
posted Hide Post
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
 
Posts: 47 | Registered: March 21, 2014Report This Post
Silver Member
posted Hide Post
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,
 
Posts: 47 | Registered: March 21, 2014Report This Post
Member
posted Hide Post
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
 
Posts: 5 | Registered: February 28, 2013Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Date Parameters with 12 Month default

Copyright © 1996-2020 Information Builders