I have a report that is ran monthly. In each version of the report I have multiple sets of columns (1 for the previous month and one for the month prior to the previous month in each set). For example Cost 5/13 and Cost 6/13. Is there a way to dynamically set variables to declare the column titles in the AS 'Cost "date"' syntax?

Thanks,

Dan

Dan,

try something like this:

-SET &YY = SUBSTR(8,&YYMD,3,4,'99','A2');
-SET &PYY = &YY-1;
-SET &MON = CHGDAT('&YYMD','MT',&YYMD,'A3');
-SET &CURR_DT = &MON |','| &YY;
-SET &PRIOR_DT = &MON |','| &PYY;

TABLE FILE CAR
PRINT
COUNTRY
CAR
RETAIL_COST AS 'RETAIL_COST : &CURR_DT'
END

Dan,
Could you be a bit more specific?
How are you generating your columns? with an ACROSS? do you have in fact 3 fields (this month, last month, the one before last)?

I was able to resolve my issue with just setting variables as the month/year that I wanted and then appending to to the end of my existing AS declarations.

AS 'Loading % <= 2 Hrs &prevMonth'

Thanks for your help!

Dan

I'll assume you have three separate fields (COSTA,B,C).

You can compute the column titles in dialog manager variables, and insert them into the AS phrase, as shown below.

The parameter &ASOF is a yyyymmdd (or just yyyymm) value. The -DEFAULT line makes the run date the default value, but allows it to be overridden (to produce back-issues of the report). EDIT is used to isolate the year and month portion of the date; AYM to obtain those of the prior two months; the remaining edits insert the slash, and drop leading zero where applicable.

-DEFAULT &ASOF = &YYMD.EVAL;

-SET &YYM0 = EDIT(&ASOF,'999999');
-SET &YYM1 = AYM(&YYM0,-1,'I6');
-SET &YYM2 = AYM(&YYM0,-2,'I6');

-SET &MY0 = EDIT(&YYM0,'$$$$99/') | EDIT(&YYM0,'$$99') ;
-SET &MY1 = EDIT(&YYM1,'$$$$99/') | EDIT(&YYM1,'$$99') ;
-SET &MY2 = EDIT(&YYM2,'$$$$99/') | EDIT(&YYM2,'$$99') ;

-SET &Current    = IF EDIT(&MY0,'9') EQ '0' THEN EDIT(&MY0,'$9999') ELSE &MY0 ;
-SET &Penult     = IF EDIT(&MY1,'9') EQ '0' THEN EDIT(&MY1,'$9999') ELSE &MY1 ;
-SET &Antepenult = IF EDIT(&MY2,'9') EQ '0' THEN EDIT(&MY2,'$9999') ELSE &MY2 ;

TABLE 
. . .
COSTC AS 'Cost &Antepenult'
COSTB AS 'Cost &Penult'
COSTA AS 'Cost &Current'
. . .