I have a procedure that will be run in Report Caster that pulls information based on the AddDate field. I want it to pull information based on whether the AddDate field is less than or equal to &YYMD (RunDate) or greater than or equal to &YYMD - 5 (RunDateMin5).

What is the best way to do this? Should I define the fields in the metadata and then apply the filter in the procedure? When I tried this I used &YYMD as RunDate and DTADD(&YYMD,DAY,-5) as RunDateMin5. But I get no values for RunDateMin5.

Is this the best way to apply this filter and if so why do I get no values for RunDateMin5?

This message has been edited. Last edited by: <Kathryn Henning>,

-SET &DT1 = DATEADD(&YYMD, 'D', -5);

What is the benefit from doing it as a SET value in the procedure as opposed to creating it as a defined item in the metadata. What if I want to use it continually in other queries? I can't seem to get the format correct for filtering on if I create the RunDateMin5 as a define.

It did work using the SET solution.

Defines are created to add columns to a table, where sets are more for console fex level type stuff.

Method is the same and will work on Defines as well.

DEFINE FILE CAR
MYDATE/M-D-YY = DATEADD('&YYMD', 'D', -5);
END

TABLE FILE CAR
PRINT MYDATE
BY COUNTRY
ON TABLE PCHOLD FORMAT HTML
ON TABLE NOTOTAL
ON TABLE SET PAGE-NUM NOLEAD
END

quote:

solution.

As Gavin: stated, Defines add a value for every row to your table, so there's overhead involved. If you are calculating a static value, use Dialogue Manager whenever possible.

TABLE FILE CAR
PRINT COMPUTE MYDATE/M-D-YY = DATEADD('&YYMD', 'D', -5);
BY COUNTRY
ON TABLE PCHOLD FORMAT HTML
ON TABLE NOTOTAL
ON TABLE SET PAGE-NUM NOLEAD
END

Doesn't

 "AddDate field is less than or equal to &YYMD (RunDate) or greater than or equal to &YYMD - 5 (RunDateMin5)" 

equate to

 "WHERE AddDate LE RunDate OR AddDate GE RunDateMin5"? 

It seems to me that this condition is always true. Maybe it should be AND in stead of OR.