Home General
New Blog Posts: Merging Reports - Part 1 and Part 2

What is the best way to create this report?

edited August 2001 in General
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

Comments

  • edited August 2001
    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


This discussion has been closed.