Good Morning/Evening,

I am stuck on this project and cannot get the clear idea of how can I do it. Ok here is what i am getting form SQL 2005.



And the user wants to see report something like this



Where M = Male and F= Female. A couple (M & F) will have one case id.

User wants to track couples attendance by class and if class was makeup or not. With number of total people attending a class.

Can someone please help me with this?

Thank you on advance.

To little information to give a good answer
The input data from the first table is for me not found back in the output file

Is there any relation between the two.?

If so can you explain it a bit with database description.

Seems to me that this is a simple TABLE FILE

Sorting on ismakeup, class and CaseID

Define Class/Makeup Attendance

Define or Compute Married/Unmarried

You will need to workout the counts as well, this would probably be a define.

I can't see how you are stuck. It's not really that complicated.
Your data is straightforward. You just need to define fields to add the missing pieces.
1. Group the data based on if it is a regular class or a makeup class.
2. Determine the count of people for each Session.

  
EX -LINES 14 EDAPUT MASTER,class_data,CV,FILE
FILENAME=CLASS_DATA, SUFFIX=DFIX,$
SEGNAME=CLASS_DATA,$
  FIELD=CASE_ID,   ALIAS=CLASS_ID,  A4, A4,$ 
  FIELD=ISMAKEUP,  ALIAS=ISMAKEUP,  A6, A6,$
  FIELD=CLASS,     ALIAS=CLASS,     A7, A7,$
  FIELD=ISMARRIED, ALIAS=CLASS,     A6, A6,$
  FIELD=SESSION_1, ALIAS=SESSION_1, A2, A2,$
  FIELD=SESSION_2, ALIAS=SESSION_2, A2, A2,$
  FIELD=SESSION_3, ALIAS=SESSION_3, A2, A2,$
  FIELD=SESSION_4, ALIAS=SESSION_4, A2, A2,$
  FIELD=SESSION_5, ALIAS=SESSION_5, A2, A2,$
  FIELD=SESSION_6, ALIAS=SESSION_6, A2, A2,$
  FIELD=DELIMITER, ALIAS=',',       A1, A1,$
 
 
FILEDEF CLASS_DATA DISK class_data.txt
-RUN
 
-WRITE CLASS_DATA 1111,TRUE,Class 1,FALSE,M,MF,M,M,F,MF
-WRITE CLASS_DATA 2222,TRUE,Class 2,FALSE,MF,M,MF,M,M,F
-WRITE CLASS_DATA 3333,TRUE,Class 3,FALSE,MF,F,F,M,M,MF
-WRITE CLASS_DATA 4444,FALSE,Class 4,FALSE,F,MF,F,M,F,MF
-WRITE CLASS_DATA 5555,FALSE,Class 5,FALSE,MF,MF,F,M,MF,F
-RUN
 
DEFINE FILE CLASS_DATA
MARRIED/A20 = IF (ISMARRIED EQ 'FALSE') THEN 'Unmarried' ELSE IF (ISMARRIED EQ 'TRUE') THEN 'Married' ELSE 'Unknown';
CLASS_DESC/A50 = IF (ISMAKEUP EQ 'FALSE') THEN 'Class Attendance' ELSE IF (ISMAKEUP EQ 'TRUE') THEN 'Makeup Attendance' ELSE 'Unknown';
CLASS_TITLE/A10 = IF (ISMAKEUP EQ 'FALSE') THEN 'Class' ELSE IF (ISMAKEUP EQ 'TRUE') THEN 'Makeup' ELSE 'Unknown';
SESSION_TITLE/A10 = IF (CLASS_DESC EQ 'Class Attendance') THEN 'Session' ELSE ' ';
SESSION_1_CNT/I8 = DECODE SESSION_1 ( 'MF' 2 'M' 1 'F' 1 ELSE 0);
SESSION_2_CNT/I8 = DECODE SESSION_2 ( 'MF' 2 'M' 1 'F' 1 ELSE 0);
SESSION_3_CNT/I8 = DECODE SESSION_3 ( 'MF' 2 'M' 1 'F' 1 ELSE 0);
SESSION_4_CNT/I8 = DECODE SESSION_4 ( 'MF' 2 'M' 1 'F' 1 ELSE 0);
SESSION_5_CNT/I8 = DECODE SESSION_5 ( 'MF' 2 'M' 1 'F' 1 ELSE 0);
SESSION_6_CNT/I8 = DECODE SESSION_6 ( 'MF' 2 'M' 1 'F' 1 ELSE 0);
DUMMY/A1 = '';
END
 
TABLE FILE CLASS_DATA
PRINT
   CLASS     AS ''
   CASE_ID   AS ''
   MARRIED   AS ''
   SESSION_1 AS ''
   SESSION_2 AS ''
   SESSION_3 AS ''
   SESSION_4 AS ''
   SESSION_5 AS ''
   SESSION_6 AS ''

BY CLASS_DESC NOPRINT
BY DUMMY NOPRINT 

ON CLASS_DESC SUBHEAD
"<DUMMY<+0><DUMMY<+0><DUMMY<+0><SESSION_TITLE"

ON DUMMY SUBHEAD
"<CLASS_TITLE<+0>CaseID<+0>Married/Unmarried<+0>1<+0>2<+0>3<+0>4<+0>5<+0>6"

ON CLASS_DESC SUBFOOT
"<CLASS_DESC<+0><ST.SESSION_1_CNT<+0><ST.SESSION_2_CNT<+0><ST.SESSION_3_CNT<+0><ST.SESSION_4_CNT<+0><ST.SESSION_5_CNT<+0><ST.SESSION_6_CNT"
 
ON TABLE SUBFOOT
"Total Class Attendance<+0><ST.SESSION_1_CNT<+0><ST.SESSION_2_CNT<+0><ST.SESSION_3_CNT<+0><ST.SESSION_4_CNT<+0><ST.SESSION_5_CNT<+0><ST.SESSION_6_CNT"
 
ON TABLE NOTOTAL
ON TABLE SET PAGE-NUM OFF
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,PAGESIZE='Letter',LEFTMARGIN=0.250000,RIGHTMARGIN=0.250000,TOPMARGIN=0.25000,BOTTOMMARGIN=0.250000,SQUEEZE=ON,ORIENTATION=PORTRAIT,$
TYPE=REPORT,FONT='ARIAL',SIZE=10,COLOR='BLACK',STYLE=NORMAL,GRID=OFF,$
TYPE=SUBHEAD,BY=CLASS_DESC,HEADALIGN=BODY,STYLE=BOLD,$
TYPE=SUBHEAD,BY=CLASS_DESC,LINE=1,ITEM=4,OBJECT=FIELD,COLSPAN=6,JUSTIFY=CENTER,$
TYPE=SUBHEAD,BY=DUMMY,HEADALIGN=BODY,STYLE=BOLD,BORDER-TOP=LIGHT,BORDER-BOTTOM=LIGHT,BORDER-BOTTOM-STYLE=DOUBLE,$
TYPE=SUBFOOT,BY=CLASS_DESC,HEADALIGN=BODY,STYLE=BOLD,BORDER-TOP=LIGHT,BORDER-BOTTOM=LIGHT,BORDER-BOTTOM-STYLE=DOUBLE,$
TYPE=SUBFOOT,BY=CLASS_DESC,LINE=1,ITEM=1,OBJECT=FIELD,COLSPAN=3,$
TYPE=TABFOOTING,HEADALIGN=BODY,STYLE=BOLD,BORDER=MEDIUM,COLOR=RED,$
TYPE=TABFOOTING,LINE=1,ITEM=1,OBJECT=TEXT,COLSPAN=3,$
ENDSTYLE
END

Thanks Mighty_Max,

It was really great of you to give me the code.

But my only one question is I really don't know how many session will a class have, some class might have 20 session where other might have 2 sessions. Is there a way to auto generate session.

You should have provided sample data that is in the same format as your data source. What is your data source? Is it a SQL table?
Please provide the master file and proper sample data.

Thanks for your respond Might_Max.

I am getting data from SQL 2005, its a view. I wrote SQL query so I do have control over it and I can change it (if needed.

My master file looks like this :-

  FILENAME= ****CLASS_TRACKING**, SUFFIX=SQLMSS  , $
  SEGMENT=***CLASS_TRACKING****, SEGTYPE=S0, $
    FIELDNAME=EVENTID, ALIAS=eventid, USAGE=I11, ACTUAL=I4, FIELDTYPE=R, $
    FIELDNAME=CASEID, ALIAS=caseid, USAGE=I11, ACTUAL=I4, $
    FIELDNAME=TITLE, ALIAS=title, USAGE=A100V, ACTUAL=A100V,
      MISSING=ON, $
    FIELDNAME=MARRIEDIND, ALIAS=marriedind, USAGE=I11, ACTUAL=I4,
      MISSING=ON, $
    FIELDNAME=ISMAKEUP, ALIAS=isMakeup, USAGE=A1V, ACTUAL=A1V, $
    FIELDNAME=1, ALIAS=1, USAGE=A2V, ACTUAL=A2V, $
    FIELDNAME=2, ALIAS=2, USAGE=A2V, ACTUAL=A2V, $
    FIELDNAME=3, ALIAS=3, USAGE=A2V, ACTUAL=A2V, $
    FIELDNAME=4, ALIAS=4, USAGE=A2V, ACTUAL=A2V, $
    FIELDNAME=5, ALIAS=5, USAGE=A2V, ACTUAL=A2V, $
    FIELDNAME=6, ALIAS=6, USAGE=A2V, ACTUAL=A2V, $
    FIELDNAME=7, ALIAS=7, USAGE=A2V, ACTUAL=A2V, $
    FIELDNAME=8, ALIAS=8, USAGE=A2V, ACTUAL=A2V, $
    FIELDNAME=9, ALIAS=9, USAGE=A2V, ACTUAL=A2V, $
    FIELDNAME=10, ALIAS=10, USAGE=A2V, ACTUAL=A2V, $
    FIELDNAME=11, ALIAS=11, USAGE=A2V, ACTUAL=A2V,$
    FIELDNAME=12, ALIAS=12, USAGE=A2V, ACTUAL=A2V, $
    FIELDNAME=13, ALIAS=13, USAGE=A2V, ACTUAL=A2V, $
    FIELDNAME=14, ALIAS=14, USAGE=A2V, ACTUAL=A2V, $
    FIELDNAME=15, ALIAS=15, USAGE=A2V, ACTUAL=A2V, $
    FIELDNAME=16, ALIAS=16, USAGE=A2V, ACTUAL=A2V, $
    FIELDNAME=17, ALIAS=17, USAGE=A2V, ACTUAL=A2V, $
    FIELDNAME=18, ALIAS=18, USAGE=A2V, ACTUAL=A2V, $
    FIELDNAME=19, ALIAS=19, USAGE=A2V, ACTUAL=A2V, $
    FIELDNAME=20, ALIAS=20, USAGE=A2V, ACTUAL=A2V, $
    FIELDNAME=21, ALIAS=21, USAGE=A2V, ACTUAL=A2V,$

And correct data form is :-


Where 1 = true and 0 = false.