I am trying to create a report that runs monthly a week after our data loads into the warehouse. Our load is on the 2nd saturday of every month for the prior month (ie rp_end_dt = 7/30/2013 was loaded on 8/10/2013)
I have tried and tried to figure out how to make the report run for the prior period without having to make rp_end_dt be a parameter that the user has to select from.
I have tried something like IF TODAY THEN DATEMOV(rp_end_dt, 'EOM-'); but TODAY is A10 and needs to be in YYMD format and I tried to convert it but it only prints blank spaces? Sooo If I am not rambling to much and somebody happens to know what I am talking about I would be very much obliged for a little help or a hint!
Thank you!!This message has been edited. Last edited by: JThompson,
Hi Jeanna, I'm sure there are 101 ways to do this. But lets start by doing it the "Dialogue Manager" way:
-* Get beginning of current month
-SET &THIS_BOM = EDIT(&YYMD,'99999901') ;
-* Set end of last month
-SET &LAST_EOM = AYMD(&THIS_BOM,-1,'I8') ;
-TYPE &YYMD &THIS_BOM &LAST_EOM
The above creates the parameter in YYMD format.
In e.g. a DEFINE:
DEFINE FILE CAR
TODAYS_DATE/MDYY = '&MDYY' ;
THIS_BOM/MDYY = DATEMOV(TODAYS_DAT,'BOM') ;
rp_end_dt/MDYY = THIS_BOM - 1 ;
END
TABLE FILE CAR
PRINT COUNTRY TODAYS_DATE THIS_BOM rp_end_dt
END
Here rp_end_dt is in MDYY format.
You can obviously extend this even further e.g. if they run the report prior to the 2nd Saturday of the month, I'd imagine you'd need to pick up the end of the month prior to last month.
Hopefully this will give you some ideas to work from.
WebFOCUS 8.2.06 mostly Windows Server
August 31, 2013, 06:36 PM
George Patton
This works for me. It was written before the BOM EOM functions were available and gives the year and month as YYYYMM.
If you DEFINE the date field as PREVMONTH/YYM=datefield then you can test directly without worrying about calculating the beginning of the previous month and its end.