Have started to venture into a area of creating more complex WebFocus reports than I'm accustomed to and need advice, suggestions on how I might proceed given the following requirements.

Have a simple database comprising of 3 fields
Name char(64)
Training_Start_Date MDYY
Training_End_Date MDYY

Requirements.

Report that prints out a daily total of # of students in training for a selected period of time.

Report would prompt user to enter a Start and End date and would print out daily totals for that particular period

As Example. Reports Prompts:
Start: 05/01/2009 End: 05/31/2009

Report

Date # Stds in Training
05/01/2009 24
05/02/2009 24
05/03/2009 58
.
.
.
05/31/2009 96

Would I be looking at using a "looping Function" of some kind? If so can you direct me to which commands/functions/logic I should study up on?

Thanks,
Jeff

This message has been edited. Last edited by: Jeff_Rowland,

How about something like this?

  
TABLE FILE CAR
SUM
     CNT.CAR
BY COUNTRY
WHERE (COUNTRY EQ 'ENGLAND') OR (COUNTRY EQ 'FRANCE');
END

Jeff,

You say that you are looking for a "looping function". You are thinking of creating a list of dates between the start and the end dates and then checking for each generated date if it falls between the training dates. If it does count the occurrence. If it doesn't, don't count.

The beauty of Focus is that you don't think "loop", you think "set".

In order to have a set of dates between the start and end, the best way is to use a McGuyver file. Here is a file and its master.
The file (notice that the first character of this string is a blank!)

  
 FILEFORMCGUYVERFILEFORMCGUYVERFILEFORMCGUYVERFILEFORMCGUYVERFILEFORMCGUYVER

The Master:

  
FILENAME=FSEQ,SUFFIX=FIX,
    DATASET=C:\IBI\APPS\FOCALPOINT\FSEQ.DAT
  SEGNAME=CHAR1,SEGTYPE=S0
   FIELDNAME=BLANK,BLANK,A1,A1,$
  SEGNAME=CHARS,SEGTYPE=S0,PARENT=CHAR1,OCCURS=VARIABLE
   FIELDNAME=CHAR,CHARS,A1,A1,$
   FIELDNAME=COUNTER,ORDER,I2,I4,$

And here is the code to generate your report.

-* File Jeff.fex
-* Generate a HOLD file that mimics Jeff's 3 field file. 
-*It has 2 dates, the first is within 100 days after 1 jan 2009; the second is within 30 days after.
DEFINE FILE CAR
ADATE/DMYY WITH MODEL='01/01/2009';
F_DATE/DMYY=ADATE + 100*RDUNIF('D6.5');
T_DATE/DMYY=F_DATE + 30*RDUNIF('D6.5');
END
TABLE FILE CAR
PRINT F_DATE T_DATE
BY MODEL
ON TABLE HOLD
END
-RUN
-* The 2 variables hold the start and end dates as the user would have entered them
-SET &F_DATE='01/02/2009';
-SET &T_DATE='28/02/2009';
-RUN
-* The McGuyver file is called FSEQ
-* The JOIN between the HOLD and the FSEQ files will generate the dates between start and end.

JOIN BLANK WITH MODEL IN HOLD TO BLANK IN FSEQ AS M_
DEFINE FILE HOLD
BLANK/A1 WITH MODEL=' ';
BDATE/DMYY='&F_DATE';
ZDATE/DMYY='&T_DATE';
RDATE/DMYY=BDATE + COUNTER - 1;
END
-* The report extracts all the records where the generated date is between the training dates

TABLE FILE HOLD
COUNT MODEL
BY RDATE
WHERE RDATE GE F_DATE
WHERE RDATE LE T_DATE
WHERE RDATE LE ZDATE
END

Try something like this code

 
TABLE FILE CASHFLOW
SUM 
     CNT.SHRT_TERM_DEBT
BY CASH_DATE
HEADING
""
FOOTING
""
WHERE ( CASH_DATE GE '&CASH_DATE1' ) AND ( CASH_DATE LE '&CASH_DATE2' );
ON TABLE SET PAGE-NUM OFF 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON

 

That would work great if there is an entry for each date, but from the explanation, I don't think there is - it's just a date range and you have to determine if the course date falls within that range.

Since the dates are &variables, you could create a loop like the following to define each day and then see if your a course falls on that day. Here's an example from the TRAINING file:

-SET &ECHO=ALL;
-SET &BEGDATE='19910601';
-SET &ENDDATE='19910612';
-SET &NUMDAYS=DATEDIF(&BEGDATE,&ENDDATE,'D');
-TYPE &NUMDAYS
-SET &DAY=0;
DEFINE FILE TRAINING
COURSEEND/MDYY=COURSESTART+10;
BEGDATE/YYMD=&BEGDATE;
-REPEAT THISLOOP &NUMDAYS TIMES
DAY&DAY/YYMD=DATEADD('&BEGDATE','D',&DAY);
DAYCNT&DAY/I9=IF DAY&DAY GE COURSESTART AND DAY&DAY LE COURSEEND THEN 1 ELSE 0;
-SET &DAY=&DAY+1;
-THISLOOP
END
TABLE FILE TRAINING
SUM 
-SET &DAY=0;
-REPEAT PRINTLOOP &NUMDAYS TIMES
DAYCNT&DAY.EVAL AS 'DAY&DAY'
-SET &DAY=&DAY+1;
-PRINTLOOP
END

You'll probably have to print * from the training file to see that corresponding data

This gives me some good information on where to start. Wasn't familiar with using the MATCH command or EVAL.

Darian,
Any suggestion how I can get the totals to print vertically, i.e. seperate line for each date/total?

Thanks to all who have replied.

Just amend the lower portion of Darin's code to become -

DAYCNT&DAY.EVAL AS 'DAY&DAY' OVER
-SET &DAY=&DAY+1;
-PRINTLOOP
BEGDATE NOPRINT
END

T