What is the best way to create this report?

rbuser

I have a table with the following structure:

Staff Code : string;
Period Code : string;
Course Code : string;
Nbr Enrolled : integer;

I need to generate a report as shown below... The staff listed vertically,
the periods creating columns ... and the Course Code and the Number Enrolled
listed in the columns. I can create a crosstab report showing the staff and
periods the way I need them; however, the way I understand the crosstab
report I can only total the number enrolled... I also need to know what the
course code is for that staff/period. What is the best way to generate a
report such as this?... to make matters a little harder... a staff member is
allowed to have more than one course in a given period... see example below.
The number of periods can be anything from 2 to 20... and the number of
staff can be from 1 to1000.... Thanks in advance for any suggestions.

Joe Hutchins


Report Needed:

Staff Per= 1st 2nd 3rd
4th Totals
============================================================================
====
AA CrsCdeX:25 CrsCdeX:17 CrsCdeX:18 CrsCdeX:20 Total:80
AB CrsCdeX:15 CrsCdeX:20 CrsCdeX:30 CrsCdeX:20 Total:85
BC CrsCdeX:25 CrsCdeX:17 CrsCdeX:18 CrsCdeX:20
CrsCdeX:15
Total:95
BD CrsCdeX:15 CrsCdeX:20 CrsCdeX:30 CrsCdeX:20 Total:85
Totals: 95 74 96
80 345

digitalmetaphors

I would order the data by StaffCode and use 1 column. Create a group on
StaffCode in the report. Then use the group footer to calculate a group
total if there are more than one period records. Place the dbTexts across
the detail band to create columns. Use a dbCalc to sum the value of the
periods. Then use a TppVariable to sum all of these values. The dbCalcs will
all be in the group footer. You can set the group footer to visible false.
Then, the trick is going to be to move this calculation up into the last
detail band for the group, to display the total(as I understand it). There
is a demo which displays the group total for a group in the last detail line
for that group:

http://www.digital-metaphors.com/tips/GroupTotalOnLastLineOfGroup.zip


Cheers,

Jim Bennett
Digital Metaphors