I need to schedule a report to run daily, but I will need for it to report the prior day’s activity. I have a date field called VER_DATE on the table; I just need help with populating this date field with the prior date.

Example: When the report runs tomorrow morning at 8 am, it will be reporting activity that was done the day before.

Any help will be appreciated

-SET &YESTERDAY = AYMD ( &YYMD , -1 , 'I8YYMD');
TABLE FILE ...
IF VER_DATE EQ &YESTERDAY
..
this will work if VER_DATE is either I8yymd or a focus smartdate.

get a copy of the USING FUNCTIONS manual, downloadable from the IBI documentation site.
It will have all this stuff in it.

Jennifer,

Since we never could get the Holiday file to work, here's how to do it if you want the previous business day.

-SET &W1=DOWK((&YYMD+0), '');


-*---------------------------------------------------------*-
-*                        YESTERCLM
-*
-* INCLUDE TO USED TO DETERMINE THE PREVIOUS WORKING DAY
-* TAKES INTO ACCOUNT HOLIDAYS
T1/MDYY=&MDYY;
T3/I2=IF '&W1' EQ 'MON' THEN 3 ELSE 1;
T4/MDYY=T1-T3;
T5/I8YYMD=T4;
T6/I8=T5;
-*
-*Looks at previous day to determine if that day was a holiday
-*If so, force it to look at the most recent work day
-*
TDATEI/I8 = DECODE T6(20060102 20051230
    20060414 20060413
    20060529 20060526
    20060704 20060703
    20060904 20060901
    20061123 20061122
    20061124 20061122
    20061225 20061222
    20061226 20061222
    20070707 20071229
    20070406 20070405
    20070528 20070525
    20070704 20070703
    20070903 20070831
    20071122 20071121
    20071123 20071121
    20071224 20071221
    20071225 20071221
    20080101 20071231
    20080321 20080320
    20080526 20080523
    20080704 20080703
    20080901 20080829
    20081127 20081126
    20081128 20081126
    20081225 20081224
    20081226 20081224);
TDATE/I8 = IF TDATEI EQ 0 THEN T6 ELSE TDATEI;

We simply update the include file (yestrclm) each year with our company holidays and closings.

Hope this helps.

Jennifer

Please update your signature with some info about your platform and application version.

There is a very good book that might help you a lot with all kind of date manipulations.
It gives you examples on things like smartdates, legacydates and all kind of formulas..

Expanding on susannah's example, if you are using a database where the date has time-of-day in the field as well you will need the following:

TABLE FILE ...
WHERE VER_DATE GE '&YESTERDAY'
  AND VER_DATE LT '&YYMD'
...

Glenda, what is your platform? It took us a while to find the correct place for the holiday file but once we got it where the Reporting Server wanted it, it works great.

Our platform is AIX 5.2 operating system.

Glenda,

The holiday file in Windows is in the ibi\srv\wfs\bin directory. Each record has one holiday date, yyyymmdd characters. After each date one must have a space. Anything after that space is treated as a comment. The file name must be HDAYxxxx and the err extension. xxxx can be any 4 characters.

In order to find yesterday's date, you have to declare the name of the holiday file:
SET HDAY=xxxx
and the business days, usually:
SET BUSDAYS=_MTWTF_

Then you define yesterdays date in DEFINE:

  
DEFINE FILE ...
TODAY/YYMD='&DATEYYMD';
YESTERDAY/YYMD=DATEADD(TODAY, 'BD', -1);
END

And in TABLE:

TABLE FILE ...
...
WHERE VERDATE EQ YESTERDAY
...
END

Good luck!