Grouping

[HRISUser]

Hi All,

I am on Crystal 11 and have a question about getting the date that an employee attended a training course.
The training courses are on a different table than the employee and there is a one to many relationship between employee and training history. This is what the data looks like:

(Report Header)
Employee# Name Class 1 Class 2 Class 3 Class 4
(Details)
1 Sally 1/15/2000
1 Sally 3/1/2001
1 Sally 4/1/2002
1 Sally 6/1/2006
1 Sally 5/1/2005

(Group Footer)
1 Sally 5/1/2005

I have created some formulas where if the class=class 1 then return the date that is how I got the info to appear under the columns. I then tried to group the data by employee and put all of the info in the group footer, and then put the following formula in the group footer

IF Cstr((({EMPLOYEE.EMPLOYEE}))) =Cstr((({TRAINING.EMPLOYEE})))
AND {TRAINING.COURSE} = 'Class 1'
THEN {TRAINING.DATE_COMPLETED}

It worked for class 1 so then i added another formula where
IF Cstr((({EMPLOYEE.EMPLOYEE}))) =Cstr((({TRAINING.EMPLOYEE})))
AND {TRAINING.COURSE} = 'Class 2'
THEN {TRAINING.DATE_COMPLETED}
This is where the problem is. For some strange reason, the class 2 info is not showing up in the group footer even though I added it to the Group Footer.

I was wondering if anyone had any ideas as to why this would be happening.

Any help would be greatly appreciated.
Thanks

 

[lbass]

Place your formulas in the detail section and then insert maximums on them. Then suppress the detail section.

-LB

 

[HRISUser]

Thanks for the quick response so that worked, but when I add in the Maximum (date) in the group footer it is giving me the maximum date that any employee took the course, not the maximum date that the specific employee took the course. So I tried adding this

IF ({EMPLOYEE.EMPLOYEE}={TRAINING.EMPLOYEE})
THEN
Maximum({@CLASS 1})

and I'm still getting the maximum class 1 date in the database instead of the employee's maximum date. Any thoughts?

 

[lbass]

You don't need a formula. Just right click on the formula and insert a maximum at the group level. I'm assuming you have a group on {employee.employee}.

Also, if you linked the tables to begin with on the employee field, you would not need to reference both fields in the formula. You would use a left join FROM the employee table to the training table. Then your formulas could be:

if {TRAINING.COURSE} = 'Class 1'
THEN {TRAINING.DATE_COMPLETED}

Then you would right click on this and insert a maximum at the {employee.employee} group level.

-LB

 

[HRISUser]

It worked perfectly thanks for the help!!!