I'm trying to create one report that would show the count of students be college as a summary block/section, then list each student in that college. We have an ACCESS database that has a report in it that does that. We want to eliminate the ACCESS database. Whether the summary is at the beginning or end doen't matter.

So for example:

College Count
AH 100
AS 75
BU 225
Total 400

College AH
Student ID First Name Last Name
12345 Rick Smith
23456 Joe Jones

College AS
98765 Mary Smith
87654 Jim Walker

I can create 2 seperate reports but it seems like there should be a way to have this done in one.
Conceptually it is:

COUNT STU_ID
BY COLLEGE
PRINT
STU_ID FIRST_NAME LAST_NAME
BY COLLEGE

But this doesn't give me what I want.

Your help is appreciated.

Rick

This message has been edited. Last edited by: Rick Man,

The easiest thing to do would be to use the PDF Layout Painter to create a compound report.

Or you can try to use the MacGyver Technique to combine the two into one.

Which part isn't working? The following seems to get me what I think you want.

TABLE FILE CAR
SUM 
     'CNT.CAR.COMP.CAR'
BY 'CAR.ORIGIN.COUNTRY'
PRINT 
     'CAR.CARREC.MODEL'
     'CAR.SPECS.RPM'
BY 'CAR.ORIGIN.COUNTRY'
ON TABLE COLUMN-TOTAL AS 'TOTAL' CAR
ENDSTYLE
END

  

That isn't what he wants, Channy. He wants all the totals for all the colleges at the beginning followed by a detail report for each college.

This is more of what he wants but much prettier:

TABLE FILE CAR
SUM 
     'CNT.CAR.COMP.CAR'
BY 'CAR.ORIGIN.COUNTRY'
END

TABLE FILE CAR
PRINT 
     'CAR.CARREC.MODEL'
     'CAR.SPECS.RPM'
BY 'CAR.ORIGIN.COUNTRY'
ON TABLE COLUMN-TOTAL AS 'TOTAL' 
END

Got it. I misunderstood.

He could I guess also potentially do a drilldown sort of report but if he needs it all in one then that wouldn't suffice.

You're right Ginny that is what I want; however, that is 2 seperate reports. If I output to PDF or Excel to be printed I have to get into the compound report thing. But, it looks like that is the way it is, unless there is another idea.

Thanks to both of you for the quick response.

Well, here's a solution that is NOT dynamic (meaning that you would hard code for all the possible values) but you could do a hold file of possible values, -READ them into parameters, and then create a loop to generate dynamic code
OR just create FOCUS code on the first read, hold format alpha and -INCLUDE the hold file.

DEFINE FILE CAR
ENG_ST/I9=IF COUNTRY EQ 'ENGLAND' THEN 1 ELSE 0;
FRA_ST/I9=IF COUNTRY EQ 'FRANCE' THEN 1 ELSE 0;
ITA_ST/I9=IF COUNTRY EQ 'ITALY' THEN 1 ELSE 0;
JAP_ST/I9=IF COUNTRY EQ 'JAPAN' THEN 1 ELSE 0;
WGE_ST/I9=IF COUNTRY EQ 'W GERMANY' THEN 1 ELSE 0;
TOT_CNT/I9 WITH MODEL=1;
END
TABLE FILE CAR
PRINT CAR MODEL
BY COUNTRY NOPRINT
ON COUNTRY SUBHEAD
" "
"COUNTRY = <COUNTRY"
FOOTING
"TOTAL MODEL COUNT: "
"ENGLAND<10><TOT.ENG_ST"
"FRANCE<10><TOT.FRA_ST"
"ITALY<10><TOT.ITA_ST"
"JAPAN<10><TOT.JAP_ST"
"W GERMANY<10><TOT.WGE_ST"
"TOTAL<10><TOT.TOT_CNT"
ON TABLE NOTOTAL
ON TABLE SET ONLINE-FMT PDF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=10,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
$
ENDSTYLE
END

There are 4 cars in ENGLAND but only 3 are getting counted. Italy is like that too. If I run this it counts correctly:

TABLE FILE CAR
PRINT *
ON TABLE HOLD AS NEWCAR
END
TABLE FILE NEWCAR
SUM
CNT.CAR
BY COUNTRY
END

That's because there really ARE only 3 cars in ENGLAND, but there are two different MODELs for JAGUAR. When you PRINT * you get all the way to the bottom segment, but if you COUNT CAR BY COUNTRY you only go through the second segment. If you COUNT MODEL BY COUNTRY then you get 4 cars for ENGLAND.

Personally, I would create this in two separate requests as a compound report. It's not that difficult. AND it's completely dynamic.

TABLE FILE CAR
COUNT MODEL AND COLUMN-TOTAL
BY COUNTRY
ON TABLE PCHOLD FORMAT PDF OPEN NOBREAK
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=10,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
$
ENDSTYLE
END
TABLE FILE CAR
PRINT CAR MODEL
BY COUNTRY NOPRINT
ON COUNTRY SUBHEAD
" "
"COUNTRY: <COUNTRY"
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF CLOSE
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=10,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
$
ENDSTYLE
END

This message has been edited. Last edited by: Darin Lee,

Thanks. That's what I'll do.

For others looking at this this:

ON COUNTRY SUBHEAD
" "
"COUNTRY: ON TABLE NOTOTAL

Should be: the FIELD COUNTRY

  

ON COUNTRY SUBHEAD
" "
"COUNTRY: <COUNTRY "

This message has been edited. Last edited by: Rick Man,

Sorry - forgot my code markers. should be fixed now.