I have a project database that holds in each row a project startdate and the total scheduled number of days (and several other fields)

date         days
20100519       7
20100602      12
etc

I want to build a report in calender format that gives me an overview for the scheduled and the free days.
something like

may
week    m       t       w      t      f    
...
19      0       0       1      1      1    
20      1       1       1      1      0    
21      0       
june
21              0       1      1      1

So the idea is that the number of days are spread over the week, automatical, as if each project has the number of rows in the database for each day.
It sounds easy, but I can not get it so far.
(and extra challange might be filling out the empty days too.

Frank, here's some code that might help to START solving the problem. These fexes generate a hold file that contains two segments, one is the date and one is a flag for the scheduled number of days. The first example outputs flags for only the number of days in the project.

-* dutch1.fex

-SET &ECHO='ALL';

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
SET HOLDFORMAT=ALPHA
-RUN

-*-- Set up intput file ----------------------------------------------
FILEDEF MASTER DISK temp/dutch1.mas
-RUN

-WRITE MASTER FILENAME=DUTCH1, SUFFIX=FIX, $
-WRITE MASTER SEGNAME=DUTCH1, SEGTYPE=S0, $
-WRITE MASTER FIELDNAME=PROJECT_DT  , USAGE=A8YYMD, ACTUAL=A8, $
-WRITE MASTER FIELDNAME=PROJECT_DAYS, USAGE=I4    , ACTUAL=A4, $

FILEDEF DUTCH1 DISK temp/dutch1.txt
-RUN

-WRITE DUTCH1 201005190007
-WRITE DUTCH1 201006020012
-WRITE DUTCH1 201006080001
-WRITE DUTCH1 201007120003

-*-- Create Alpha Hold file ------------------------------------------
TABLE FILE DUTCH1
PRINT *
ON TABLE HOLD AS HDUTCH1
END
-RUN

-*-- Create Master for new Alpha file -------------

FILEDEF MASTER DISK temp/hdutch2.mas
-RUN

-WRITE MASTER FILENAME=HDUTCH2, SUFFIX=FIX, $
-WRITE MASTER SEGNAME=ONE, SEGTYPE=S0, $
-WRITE MASTER FIELDNAME=PROJECT_DT  , USAGE=A8YYMD, ACTUAL=A8, $
-WRITE MASTER SEGNAME=TWO, PARENT=ONE, OCCURS=VARIABLE, SEGTYPE=S0, $
-WRITE MASTER FIELDNAME=PROJECT_FLAG, USAGE=A1    , ACTUAL=A1, $
-RUN


-*-- Create new Alpha file -------------

-SET &DUMMYSTRING =
- '1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111';

FILEDEF HDUTCH2 DISK temp/hdutch2.txt
-RUN

-READ HDUTCH1 NOCLOSE &PROJECT_DT.A8. &PROJECT_DAYS.I4.

-REPEAT END_LOOP1 WHILE &IORETURN EQ 0

-*                   SUBSTR(inlength, parent, start, end, sublength, outfield)

-SET &PROJECT_FLAG = SUBSTR(100, &DUMMYSTRING, 1, &PROJECT_DAYS, &PROJECT_DAYS, 'A&PROJECT_DAYS.EVAL');

-SET &OUTDATA = &PROJECT_DT || &PROJECT_FLAG;

-WRITE HDUTCH2 NOCLOSE &OUTDATA

-READ HDUTCH1 NOCLOSE &PROJECT_DT.A8. &PROJECT_DAYS.I4.
-END_LOOP1

-CLOSE HDUTCH2

TABLE FILE HDUTCH2
PRINT
PROJECT_DT
PROJECT_FLAG
END
-RUN

The second fex outputs 100 flags per project date, with number of project days indicated as 1, the rest indicated as 0.

-* dutch2.fex

-SET &ECHO='ALL';

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
SET HOLDFORMAT=ALPHA
-RUN

-*-- Set up intput file ----------------------------------------------
FILEDEF MASTER DISK temp/dutch1.mas
-RUN

-WRITE MASTER FILENAME=DUTCH1, SUFFIX=FIX, $
-WRITE MASTER SEGNAME=DUTCH1, SEGTYPE=S0, $
-WRITE MASTER FIELDNAME=PROJECT_DT  , USAGE=A8YYMD, ACTUAL=A8, $
-WRITE MASTER FIELDNAME=PROJECT_DAYS, USAGE=I4    , ACTUAL=A4, $

FILEDEF DUTCH1 DISK temp/dutch1.txt
-RUN

-WRITE DUTCH1 201005190007
-WRITE DUTCH1 201006020012
-WRITE DUTCH1 201006080001
-WRITE DUTCH1 201007120003

-*-- Create Alpha Hold file ------------------------------------------
TABLE FILE DUTCH1
PRINT *
ON TABLE HOLD AS HDUTCH1
END
-RUN

-*-- Create Master for new Alpha file -------------

FILEDEF MASTER DISK temp/hdutch2.mas
-RUN

-WRITE MASTER FILENAME=HDUTCH2, SUFFIX=FIX, $
-WRITE MASTER SEGNAME=ONE, SEGTYPE=S0, $
-WRITE MASTER FIELDNAME=PROJECT_DT  , USAGE=A8YYMD, ACTUAL=A8, $
-WRITE MASTER SEGNAME=TWO, PARENT=ONE, OCCURS=VARIABLE, SEGTYPE=S0, $
-WRITE MASTER FIELDNAME=PROJECT_FLAG, USAGE=A1    , ACTUAL=A1, $
-RUN


-*-- Create new Alpha file -------------

-SET &DUMMYSTRING1 =
- '1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111';
-SET &DUMMYSTRING2 =
- '0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000';

FILEDEF HDUTCH2 DISK temp/hdutch2.txt
-RUN

-READ HDUTCH1 NOCLOSE &PROJECT_DT.A8. &PROJECT_DAYS.I4.

-REPEAT END_LOOP1 WHILE &IORETURN EQ 0

-SET &PROJECT_NODAYS = 100 - &PROJECT_DAYS;

-SET &PROJECT_FLAG1 = SUBSTR(100, &DUMMYSTRING1, 1, &PROJECT_DAYS  , &PROJECT_DAYS  , 'A&PROJECT_DAYS.EVAL');
-SET &PROJECT_FLAG2 = SUBSTR(100, &DUMMYSTRING2, 1, &PROJECT_NODAYS, &PROJECT_NODAYS, 'A&PROJECT_NODAYS.EVAL');

-SET &OUTDATA = &PROJECT_DT || &PROJECT_FLAG1 || &PROJECT_FLAG2;

-WRITE HDUTCH2 NOCLOSE &OUTDATA

-READ HDUTCH1 NOCLOSE &PROJECT_DT.A8. &PROJECT_DAYS.I4.
-END_LOOP1

-CLOSE HDUTCH2

TABLE FILE HDUTCH2
PRINT
PROJECT_DT
PROJECT_FLAG
END
-RUN

Hopefully this helps in creating the calendar format report...

This is one approach. I skipped weekends when assigning project dates, but did not exclude holidays.

SET ASNAMES  = ON
SET HOLDLIST = PRINTONLY
-*
-* First section creates a master and some data to work/test with.
APP FILEDEF DUTCHMAS DISK dutch.mas
-RUN
-*
-WRITE DUTCHMAS FILENAME=DUTCH, SUFFIX=FIX, $
-WRITE DUTCHMAS SEGNAME=DUTCH, SEGTYPE=S0, $
-WRITE DUTCHMAS FIELDNAME=PROJECT_START, USAGE=YYMD, ACTUAL=A8, $
-WRITE DUTCHMAS FIELDNAME=PROJECT_DAYS , USAGE=I4  , ACTUAL=A4, $
-*
FILEDEF DUTCH DISK dutch.txt
-RUN
-*
-WRITE DUTCH 201005190007
-WRITE DUTCH 201006020012
-WRITE DUTCH 201006080023
-WRITE DUTCH 201007120035
-RUN
-*
DEFINE FILE DUTCH
 BEGIN_MONTH/YYM  = PROJECT_START ;
 PROJECT_END/YYMD = PROJECT_START + PROJECT_DAYS ;
 END_MONTH/YYM    = PROJECT_END ;
END
-*
-* Save the project start dates and number of project days to SAVE file.
TABLE FILE DUTCH
 PRINT PROJECT_DAYS
 BY PROJECT_START
 ON TABLE SAVE AS PROJSAVE
END
-*
-RUN
-SET &NUM_OF_PROJECTS = &LINES ;
-*
FILEDEF PROJCAL DISK projcal.txt
-RUN
-*
-* Write a calendar of project dates for each project, skipping Saturdays and Sundays.
-REPEAT ENDREPEAT1 &NUM_OF_PROJECTS TIMES
-READ PROJSAVE &PROJECT_START.8 &PROJECT_DAYS.4
-SET &PROJECT_CNT = &PROJECT_DAYS - 1 ;
-IF &PROJECT_CNT LT 0 GOTO ENDREPEAT1 ;
-SET &P      = 0 ;
-SET &ADJUST = 0 ;
-*
-REPEAT ENDREPEAT2 FOR &I FROM 0 TO &PROJECT_CNT
-SET &P            = &I + &ADJUST ;
-SET &PROJECT_DATE = AYMD(&PROJECT_START,&P,'I8');
-SET &DAY_NAME     = DOWK(&PROJECT_DATE,'A3');
-SET &ADJUST       = IF &DAY_NAME EQ 'SUN' THEN (&ADJUST + 1) ELSE
-                    IF &DAY_NAME EQ 'SAT' THEN (&ADJUST + 2) ELSE &ADJUST ;
-SET &P            = &I + &ADJUST ;
-SET &PROJECT_DATE = AYMD(&PROJECT_START,&P,'I8');
-WRITE PROJCAL &PROJECT_DATE..1
-ENDREPEAT2
-ENDREPEAT1
-*
-* Create a master for the project date calendar generated above.
APP FILEDEF PROJMAST DISK projcal.mas
-RUN
-*
-WRITE PROJMAST FILENAME=PROJCAL, SUFFIX=FIX, $
-WRITE PROJMAST SEGNAME=PROJCAL, SEGTYPE=S0, $
-WRITE PROJMAST FIELDNAME=PROJECT_DATE, USAGE=YYMD, ACTUAL=A8, $
-WRITE PROJMAST FIELDNAME=CAL_COUNT   , USAGE=I1  , ACTUAL=A1, $
-RUN
-*
-* Write the minimum and maximum project calendar dates to a SAVE file.
TABLE FILE DUTCH
 SUM MIN.BEGIN_MONTH
     MAX.END_MONTH
 ON TABLE SAVE AS MINMAX
END
-*
-* Calculate beginning and ending months for all project calendar dates.
-RUN
-READ MINMAX &BEGIN_MONTH.6 &END_MONTH.6
-SET &BEGIN_DATE = &BEGIN_MONTH || '01';
-SET &XMONTH     = AYM(&END_MONTH,1,'I6');
-SET &XDATE      = &XMONTH || '01';
-SET &END_DATE   = AYMD(&XDATE,-1,'I8');
-SET &CALDAYS    = YMD(&BEGIN_DATE,&END_DATE);
-*
FILEDEF CALENDAR DISK calendar.txt
-RUN
-SET &WEEK_NUMBER = 0 ;
-*
-* Write a calendar of dates from beginning to ending months.
-REPEAT ENDREPEAT3 FOR &I FROM 0 TO &CALDAYS
-SET &CAL_DATE    = AYMD(&BEGIN_DATE,&I,'I8');
-SET &DAY_NAME    = DOWK(&CAL_DATE,'A3');
-SET &DAY_NUMBER  = DECODE &DAY_NAME ('SUN' 1 'MON' 2 'TUE' 3 'WED' 4 'THU' 5 'FRI' 6 'SAT' 7 ELSE 0 );
-SET &WEEK_NUMBER = IF &DAY_NUMBER EQ 1 THEN (&WEEK_NUMBER + 1) ELSE &WEEK_NUMBER ;
-WRITE CALENDAR &CAL_DATE..&DAY_NAME..&DAY_NUMBER..&WEEK_NUMBER
-ENDREPEAT3
-*
APP FILEDEF CALMAST DISK calendar.mas
-RUN
-*
-* Create a master for the date calendar.
-WRITE CALMAST FILENAME=CALENDAR, SUFFIX=FIX, $
-WRITE CALMAST SEGNAME=CALENDAR, SEGTYPE=S0, $
-WRITE CALMAST FIELDNAME=CALENDAR_DATE, USAGE=YYMD, ACTUAL=A8, $
-WRITE CALMAST FIELDNAME=DAY_NAME     , USAGE=A3  , ACTUAL=A3, $
-WRITE CALMAST FIELDNAME=DAY_NUMBER   , USAGE=I1  , ACTUAL=A1, $
-WRITE CALMAST FIELDNAME=WEEK_NUMBER  , USAGE=I2  , ACTUAL=A2, $
-RUN
-*
-* Combine the date calendar with the project calendar.
MATCH FILE CALENDAR
 PRINT DAY_NAME
       DAY_NUMBER
       WEEK_NUMBER
 BY CALENDAR_DATE AS 'CAL_DATE'
RUN
FILE PROJCAL
 SUM CAL_COUNT
 BY PROJECT_DATE AS 'CAL_DATE'
AFTER MATCH HOLD AS PROJHOLD OLD
END
-*
-* Produce the final report.
DEFINE FILE PROJHOLD
 MONTH_NUM/M      = CAL_DATE ;
 MONTH_NAME/MtrYY = CAL_DATE ;
 CAL_DATEX/A8YYMD = CAL_DATE ;
 PRINT_TEXT/A8    = EDIT(CAL_DATEX,'$$$$99/99: ') | EDIT(CAL_COUNT);
END
-*
TABLE FILE PROJHOLD
 SUM PRINT_TEXT
 BY MONTH_NUM NOPRINT
 BY MONTH_NAME NOPRINT
 BY WEEK_NUMBER NOPRINT
 ACROSS DAY_NUMBER NOPRINT
 ACROSS DAY_NAME AS ''
 ON MONTH_NAME SUBHEAD
  "<MONTH_NAME "
END

This second edition of my earlier post removes the date from each cell and places a start-of-week date at the front of each row.

SET ASNAMES  = ON
SET HOLDLIST = PRINTONLY
-*
-* First section creates a master and some data to work/test with.
APP FILEDEF DUTCHMAS DISK dutch.mas
-RUN
-*
-WRITE DUTCHMAS FILENAME=DUTCH, SUFFIX=FIX, $
-WRITE DUTCHMAS SEGNAME=DUTCH, SEGTYPE=S0, $
-WRITE DUTCHMAS FIELDNAME=PROJECT_START, USAGE=YYMD, ACTUAL=A8, $
-WRITE DUTCHMAS FIELDNAME=PROJECT_DAYS , USAGE=I4  , ACTUAL=A4, $
-*
APP FILEDEF DUTCH DISK dutch.txt
-RUN
-*
-WRITE DUTCH 201005190007
-WRITE DUTCH 201006020012
-WRITE DUTCH 201006080023
-WRITE DUTCH 201007120035
-RUN
-*
-* Save the project start dates and number of project days to SAVE file.
TABLE FILE DUTCH
 PRINT PROJECT_DAYS
 BY PROJECT_START
 ON TABLE SAVE AS PROJSAVE
END
-*
-RUN
-SET &NUM_OF_PROJECTS = &LINES ;
-*
APP FILEDEF PROJCAL DISK projcal.txt
-RUN
-*
-* Write a calendar of project dates for each project, skipping Saturdays and Sundays.
-REPEAT ENDREPEAT1 &NUM_OF_PROJECTS TIMES
-READ PROJSAVE &PROJECT_START.8 &PROJECT_DAYS.4
-SET &PROJECT_CNT = &PROJECT_DAYS - 1 ;
-IF &PROJECT_CNT LT 0 GOTO ENDREPEAT1 ;
-SET &P      = 0 ;
-SET &ADJUST = 0 ;
-*
-REPEAT ENDREPEAT2 FOR &I FROM 0 TO &PROJECT_CNT
-SET &P            = &I + &ADJUST ;
-SET &PROJECT_DATE = AYMD(&PROJECT_START,&P,'I8');
-SET &DAY_NAME     = DOWK(&PROJECT_DATE,'A3');
-SET &ADJUST       = IF &DAY_NAME EQ 'SUN' THEN (&ADJUST + 1) ELSE
-                    IF &DAY_NAME EQ 'SAT' THEN (&ADJUST + 2) ELSE &ADJUST ;
-SET &P            = &I + &ADJUST ;
-SET &PROJECT_DATE = AYMD(&PROJECT_START,&P,'I8');
-WRITE PROJCAL &PROJECT_DATE..1
-ENDREPEAT2
-ENDREPEAT1
-*
-* Create a master for the project date calendar generated above.
APP FILEDEF PROJMAST DISK projcal.mas
-RUN
-*
-WRITE PROJMAST FILENAME=PROJCAL, SUFFIX=FIX, $
-WRITE PROJMAST SEGNAME=PROJCAL, SEGTYPE=S0, $
-WRITE PROJMAST FIELDNAME=PROJECT_DATE, USAGE=YYMD, ACTUAL=A8, $
-WRITE PROJMAST FIELDNAME=PROJ_COUNT  , USAGE=I1  , ACTUAL=A1, $
-RUN
-*
-* Write the minimum and maximum project calendar months to a SAVE file.
DEFINE FILE DUTCH
 BEGIN_MONTH/YYM  = PROJECT_START ;
 PROJECT_END/YYMD = PROJECT_START + PROJECT_DAYS ;
 END_MONTH/YYM    = PROJECT_END ;
END
-*
TABLE FILE DUTCH
 SUM MIN.BEGIN_MONTH
     MAX.END_MONTH
 ON TABLE SAVE AS MINMAX
END
-*
-* Calculate beginning and ending dates for calendar months, and the number of days between the two.
-RUN
-READ MINMAX &BEGIN_MONTH.6 &END_MONTH.6
-SET &BEGIN_DATE = &BEGIN_MONTH || '01';
-SET &XMONTH     = AYM(&END_MONTH,1,'I6');
-SET &XDATE      = &XMONTH || '01';
-SET &END_DATE   = AYMD(&XDATE,-1,'I8');
-SET &CALDAYS    = YMD(&BEGIN_DATE,&END_DATE);
-*
APP FILEDEF CALENDAR DISK calendar.txt
-RUN
-*
-* Write a calendar of all dates from beginning to ending dates.
-REPEAT ENDREPEAT3 FOR &I FROM 0 TO &CALDAYS
-SET &CAL_DATE    = AYMD(&BEGIN_DATE,&I,'I8');
-SET &DAY_NAME    = DOWK(&CAL_DATE,'A3');
-SET &DAY_NUMBER  = DECODE &DAY_NAME ('SUN' 1 'MON' 2 'TUE' 3 'WED' 4 'THU' 5 'FRI' 6 'SAT' 7 ELSE 0 );
-SET &WEEK_ADJUST = &DAY_NUMBER - 1 ;
-SET &WEEK_DATE   = EDIT(AYMD(&CAL_DATE,-&WEEK_ADJUST,'I8'),'$$$$99/99');
-WRITE CALENDAR &CAL_DATE..&DAY_NAME..&DAY_NUMBER..&WEEK_DATE
-ENDREPEAT3
-*
APP FILEDEF CALMAST DISK calendar.mas
-RUN
-*
-* Create a master for the date calendar.
-WRITE CALMAST FILENAME=CALENDAR, SUFFIX=FIX, $
-WRITE CALMAST SEGNAME=CALENDAR, SEGTYPE=S0, $
-WRITE CALMAST FIELDNAME=CALENDAR_DATE, USAGE=YYMD, ACTUAL=A8, $
-WRITE CALMAST FIELDNAME=DAY_NAME     , USAGE=A3  , ACTUAL=A3, $
-WRITE CALMAST FIELDNAME=DAY_NUMBER   , USAGE=I1  , ACTUAL=A1, $
-WRITE CALMAST FIELDNAME=WEEK_DATE    , USAGE=A5  , ACTUAL=A5, $
-RUN
-*
-* Combine the date calendar with the project calendar.
MATCH FILE CALENDAR
 PRINT DAY_NAME
       DAY_NUMBER
       WEEK_DATE
 BY CALENDAR_DATE AS 'CAL_DATE'
RUN
FILE PROJCAL
 SUM PROJ_COUNT
 BY PROJECT_DATE AS 'CAL_DATE'
AFTER MATCH HOLD AS PROJHOLD OLD
END
-*
-* Produce the final report.
DEFINE FILE PROJHOLD
 MONTH_NUM/M      = CAL_DATE ;
 MONTH_NAME/MtrYY = CAL_DATE ;
END
-*
TABLE FILE PROJHOLD
 SUM PROJ_COUNT
 BY MONTH_NUM      NOPRINT
 BY MONTH_NAME     NOPRINT
 BY WEEK_DATE      AS ''
 ACROSS DAY_NUMBER NOPRINT
 ACROSS DAY_NAME   AS ''
 ON MONTH_NAME SUBHEAD
  "<MONTH_NAME "
END

EDIT: I made a couple of corrections.

This message has been edited. Last edited by: Dan Satchell,

FrankDutch must be sipping on a Grolsch right about now...

Francis, Dan
thanks for your solution
I will work this out and nalyse what you did

and I was not sipping Grolsch....it was a red wine...

Frank,
Calendar output is something I've been asking IBI for for about three years now. We are about to undertake a scheduling project where we have scheduled data in a database and want to place it on a calendar.
I worked with the idea of building tables in a fex and building the schedule that way however that just seemed tidious. Seeing that time is an important variable in my life, I then thought about a second much easier method. I went out on the internet and found a html/xml calendar that had more functionality than I could ever use. I call it in my htmlform and pass the data to the calendar in xml format and everything is hunky dory.

Please do tell us where you found this hunky dory html/xml calendar.

I've used a couple different ones but the one I like the best was scripcalendar. They have a site scriptcalendar.com however it looks like they have really upgraded their calendar from the version that I have.