I want the end user to select the Start Month, SDATE and a simple integer number of months, NUMMO. ASMTRYYYYMM defines an MtrYY field associated with each record. I am having trouble populating values for Start Month. I want a MtrYY List with this month as the default.
I have: -DEFAULT &NUMMO = 3; -DEFAULT &SDATE = &SDATE; DEFINE FILE RENT_TRENDS ASI8YYYYMM/I8=EDIT(Date_YYYYMM|'01'); ASMTRYYYYMM/MtrYY=DATECVT(ASI8YYYYMM,'I8YYMD','MtrYY'); NUMMO/I2=3; SDATE/MtrYY=DATECVT(&YYMD,'I8YYMD','MtrYY'); EDATE/MtrYY= DATECVT( DATEADD( DATECVT(&YYMD,'I8YYMD','YYMD') , 'M', 1* NUMMO ) ,'YYMD','MtrYY'); BETWEENFLAG/A4= IF SDATE LE ASMTRYYYYMM AND EDATE GE ASMTRYYYYMM THEN 'T' ELSE 'F';
...
WHERE TOTAL SDATE EQ '&ASMTRYYYYMM.(FIND ASMTRYYYYMM IN RENT_TRENDS).ASMTRYYYYMM.'; WHERE TOTAL BETWEENFLAG EQ 'T'; WHERE TOTAL NUMMO EQ &NUMMO.NUMMO:.;
Thanks for your input!This message has been edited. Last edited by: <Kathryn Henning>,
_____ WF 8.1.04 Win 7// Windows Server 2012 R2 SASS OLAP Cube
August 07, 2015, 05:27 AM
Tony A
Hi Nicholas and welcome to the forum,
You do not say how you are implementing this - via amper autoprompting or trying to populate a control on an HTML page.
If via amper autoprompting, then I think that you are over complicating things. You have to remember that MtrYY is a display format and that its usage is like a "normal" MYY.
Take this example, I have added a define to the synonym for GGSALES of DEFINE MTDATE/MtrYY = DATE; to allow the (FIND MTDATE IN GGSALES) to be able to use it in the prompt for the start date. Note that the prompt shows "January, 1996' but the value passed for that selection is 011996.
I then use the AYM function to add the months to get the new date. However, the input to AYM has to be I6YYM, I6, I4YM or I4, so I convert it using DATECVT. But I need the format of the new variable to be of the form MYY so I use DATECVT to convert it back again.
You should be able to use the following code (with the addition of the define within the synonym) to work out what you need to do for an amper autoprompt use. If it's to populate a control in an HTML page then you might need something different.
-DEFAULT &NUMMO = 3
-SET &Dummy = '&SDATE.(FIND MTDATE IN GGSALES).Select Start Date.';
-SET &EDATE = DATECVT(AYM(DATECVT(&SDATE, 'I6MYY', 'I6YYM'), &NUMMO, 'I6YYM'), 'I6YYM', 'I6MYY');
-TYPE &NUMMO
-TYPE &SDATE
-TYPE &EDATE
TABLE FILE GGSALES
SUM DOLLARS
BY MTDATE
WHERE MTDATE FROM &SDATE.QUOTEDSTRING TO &EDATE.QUOTEDSTRING;
END
T
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
August 10, 2015, 12:22 PM
Nicholas Spyrison
Tony-
Thanks for the quick reply!
I always try to accomplish everything via amper autopromting, regardless of it's use in HTML/ portal, is this not the best development path?
When I try to implement your suggestions I run into errors: (FOC0003) THE FIELDNAME IS NOT RECOGNIZED: DST.MTDATE (FOC009) INCOMPLETE REQUEST STATEMENT (FOC205) THE DESCRIPTION CANNOY BE FOUND FOR FILE NAMED: WF$HOLD (3)
it then asks for SDATE and has an Error Parsing MASTER File RENT_TRENDS(0).
When I try to open the Define Component, AS crashes DEFINE FILE RENT_TRENDS DATE_I8YYYYMM/I8=EDIT(Date_YYYYMM|'01'); MTDATE/MtrYY= DATECVT(DATE_I8YYYYMM,'I8YYMD','MtrYY'); END
Any ideas?
_____ WF 8.1.04 Win 7// Windows Server 2012 R2 SASS OLAP Cube
August 11, 2015, 02:58 AM
Tony A
quote:
(FOC0003) THE FIELDNAME IS NOT RECOGNIZED: DST.MTDATE
Did you add the define to the synonym?
quote:
DATE_I8YYYYMM/I8=EDIT(Date_YYYYMM|'01');
What is the usage format for Date_YYYYMM?
T
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
August 11, 2015, 11:40 AM
Nicholas Spyrison
No, shouldn't it be content with the fex define?
Date_YYYYMM Usage: A8
_____ WF 8.1.04 Win 7// Windows Server 2012 R2 SASS OLAP Cube
August 12, 2015, 08:10 AM
Tony A
quote:
'&ASMTRYYYYMM.(FIND ASMTRYYYYMM IN RENT_TRENDS).ASMTRYYYYMM.';
This amperautoprompt line would be actioned before any WebFOCUS code and therefore the field should exist in the synonym.
That is why I added my MTDATE into the GGSALES synonym for my example.
T
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
August 12, 2015, 04:23 PM
Nicholas Spyrison
If I stay within an I6YYM I have it working as expected with:
-DEFAULT &NUMMO = 3
-SET &Dummy = '&CURYM.(FIND YYYYMM IN DM).Select Start Date.';
-SET &CURYM3 = AYM ( &CURYM, &NUMMO, 'I6YYM');
...
WHERE RENT_TRENDS.RENT_TRENDS.Date_YYYYMM FROM '&CURYM' TO '&CURYM3';
My current understanding is that I can't -SET/DEFAULT and prompt for a dynamic current month within the fex. and that I should set the default in the html or portal; Is that accurate?
_____ WF 8.1.04 Win 7// Windows Server 2012 R2 SASS OLAP Cube
August 13, 2015, 02:55 AM
Tony A
quote:
I can't -SET/DEFAULT and prompt for a dynamic current month within the fex
Not strictly true, you can use -PROMPT or -SET &Dummy (or something similar), although in this case the default value doesn't stick! -
-DEFAULT &NUMMO = 3
-DEFAULT &SDATE = '1997/01/01'
-SET &Dummy = '&SDATE.(FIND MTDATE IN GGSALES).Select Start Date.';
-SET &EDATE = DATECVT(AYM(DATECVT(&SDATE, 'I6MYY', 'I6YYM'), &NUMMO, 'I6YYM'), 'I6YYM', 'I6MYY');
-TYPE &NUMMO
-TYPE &SDATE
-TYPE &EDATE
TABLE FILE GGSALES
SUM DOLLARS
BY MTDATE
WHERE MTDATE FROM &SDATE.QUOTEDSTRING TO &EDATE.QUOTEDSTRING;
END
T
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10