![]() |
||||||||||||
Go ![]() | New ![]() | Search ![]() | Notify ![]() | Tools ![]() | Reply ![]() | ![]() |
Master |
I am trying to create a report from a fiscal year file to show only the data for the months from the start of FY11 to the present. (Present = today's date). This is the synonym layout of my data: ?FF AGEDATA_11_A -EXIT FILENAME= AGEDATA_11_A AGE AGE A60 _TYPE_ _TYPE_ D20.2 JUL10 jul10 D20.2 AUG10 aug10 D20.2 SEP10 sep10 D20.2 OCT10 oct10 D20.2 NOV10 nov10 D20.2 DEC10 dec10 D20.2 JAN11 jan11 D20.2 FEB11 feb11 D20.2 MAR11 mar11 D20.2 APR11 apr11 D20.2 MAY11 may11 D20.2 JUN11 jun11 D20.2 I'd like to create a date field which I'd put into a WHERE statement to print only the fields from July 2010 to the previous month (current_month - 1). Anyone have any ideas? Thanks, ![]() Tomsweb WebFOCUS 8.1.05M, 8.2.x APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports Apache Tomcat/8.0.36 | ||
|
Expert |
Try the following: -* FocalPoint: create where "date" statement for limited output -SET &TODAYIS = &YYMD ; -* Test with different date values that are in the report as 'HIREDATE20' -SET &TODAYIS = 20090329 ; -SET &TODAYIS = 20090529 ; -TYPE REPORT FROM BOQ TO &TODAYIS -* -TYPEs are option -* CONVERT TODAYS DATE FOR DATE MATH, IN TO: &CURDATE -SET &CURDATE = DATECVT(&TODAYIS,'I8YYMD','YYMD'); -TYPE &CURDATE -* Use &CURDATE to get the Beg Of Qtr (BOQ) -SET &BEGTQTY0 = DATEMOV(&CURDATE, 'BOQ'); -TYPE &BEGTQTY0 -* Convert the BOQ to a useable date in the WHERE statement -* Legend for variable, use the Capital letters: BEG This Quarter This Year -SET &BEGTQTY = DATECVT(&BEGTQTY0,'YYMD','A8YYMD'); -TYPE &BEGTQTY DEFINE FILE EMPDATA -* Convert the YY/MM/DD to an I8 for date calcs HIREDATEI8/I8 = HIREDATE ; -* Add 20 YEARS to the HIREDATE to bring these dates current (just for testing) HIREDATE20X/I8 = DATEADD(HIREDATEI8, 'Y', +20); HIREDATE20/I8 = DATECVT(HIREDATE20X,'YYMD','I8YYMD'); -* GET THE BOQ (BEGINNING OF THE QUARTER) FOR THE HIREDATE. (SEE WHAT QTR IT'S IN) BEGTQTY0/I8 = DATEMOV(HIREDATEI8, 'BOQ'); -* CONVERT IT TO A YYYYMMDD FORMAT BEGTQTY/I8 = DATECVT(BEGTQTY0,'YYMD','I8YYMD'); END TABLE FILE EMPDATA HEADING "Report for hire dates from &BEGTQTY to &TODAYIS" PRINT HIREDATE20 AS 'HireDate' FIRSTNAME AS 'First Name' LASTNAME AS 'Last Name' -* Use the next line to verify without using the WHERE statement. COMPUTE InThisQTR/A1 = IF HIREDATE20 FROM '&BEGTQTY' TO '&TODAYIS' THEN 'Y' ELSE '-' ; AS '?' BY HIREDATE -* Select only dates from BOQ to &TODAYIS (there's only one as of 10/22/2010) -* Use the next line to limit these records. -*WHERE HIREDATE20B FROM '&BEGTQTY' TO '&TODAYIS' ON TABLE SET PAGE OFF END | |||
|
Virtuoso |
Something like this might work: -SET &CURYRMTH = EDIT(&YYMD,'999999$$'); -SET &RPTYRMTH = AYM(&CURYRMTH,-1,'I6YYM'); -SET &CURYEAR = EDIT(&YYMD,'9999$$$$'); -SET &CURMONTH = EDIT(&YYMD,'$$$$99$$'); -SET &FISCALYR = IF (&CURMONTH LE 6) THEN (&CURYEAR - 1) ELSE &CURYEAR ; -SET &COLYRMTH = &FISCALYR | '07'; -* TABLE FILE AGEDATA_11_A PRINT -* -REPEAT :ENDREPEAT1 13 TIMES -SET &COLNAME = EDIT(CHGDAT('YYM','MTY',&COLYRMTH,'A17'),'999$99'); &COLNAME.EVAL -SET &COLYRMTH = AYM(&COLYRMTH,1,'I6YYM'); -IF (&COLYRMTH GT &RPTYRMTH) GOTO :EXITREPEAT1 ; -:ENDREPEAT1 -:EXITREPEAT1 -* END WebFOCUS 7.7.05 | |||
|
Expert |
Thanks Dan... We're always open to alternative methods... Now, it's up to Tomsweb to pick one, or some of each merged in with his own expertise... | |||
|
Master |
Dan, I like your method, but in this case I need to sort by the first 2 fields in the master file, i.e.,
The confounding aspect of this is that I cannot be that I will have just one sort field or multiple sort fields in other requests which use this data. I have added the sort fieds like so:
Thanks, Tom Tomsweb WebFOCUS 8.1.05M, 8.2.x APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports Apache Tomcat/8.0.36 | |||
|
Powered by Social Strata |
![]() | Please Wait. Your request is being processed... |
|