[SOLVED] Running a report for Prior month

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,

jeanna.thompson@okdhs.org
OMES|ISD|RDW
405-522-1213

WebFOCUS 7.7.05
Windows, All Outputs

Posts: 9 | Registered: March 25, 2011

Twanette

Platinum Member

posted

Hide Post

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

Posts: 195 | Location: Johannesburg, South Africa | Registered: September 13, 2008

Ignored post by Twanette posted

Show Post

George Patton

Master

posted

Hide Post

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.

-SET &THISYEAR=EDIT(&YYMD, '9999$$$$');
-SET &THISMONTH=EDIT(&YYMD, '$$$$99$$');
-SET &LASTMONTH=IF &THISMONTH EQ 01 THEN 12 ELSE (&THISMONTH - 1);
-SET &LASTMONTHA = IF &LASTMONTH LT 10 THEN '0' || EDIT(&LASTMONTH) ELSE EDIT(&LASTMONTH);
-SET &YEAR=IF &THISMONTH EQ 01 THEN (&THISYEAR - 1) ELSE &THISYEAR;
-SET &YEARMTH=EDIT(EDIT(&YEAR) || EDIT(&LASTMONTHA));

WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP

Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010

Ignored post by George Patton posted

Show Post

JThompson

Member

posted

Hide Post

Thank you both!! I got it to work!!

jeanna.thompson@okdhs.org
OMES|ISD|RDW
405-522-1213

WebFOCUS 7.7.05
Windows, All Outputs

Posts: 9 | Registered: March 25, 2011

Ignored post by JThompson posted