Most Recent Date for a Specific Class 2

[gentrya]

I am trying to build a non-compliance report for a class we are required to take every year. I am using CR 8.5. I already have a report that has the most recent date for each person by using groupings. Can this report be modified to meet this need?

I have the following data.

Person Class Date
John Quality 3/31/03
Equip1 7/31/03
Quality 2/28/04
Mary Quality 1/5/03
Equip2 11/7/03
Steve Quality 2/28/03
Quality 3/31/04
Frank Quality 11/5/03

I want a report that shows everyone who has not taken the Quality class by today's date. The person is required to take the class in the same month as they did last year. The only person who should show up on the report should be Mary. I've tried to use maximum, but I get a maximum date of today's date for everyone.

Thanks!
age

 

[lbass]

Is there a table for employee (or student?) and a separate table for class and participation?

-LB

 

[gentrya]

Yes, there is a table for the students and another that contains all the classes that students have taken.

age

 

[MJRBIM]

There's probably a better way to do this, but these three formulas will do the trick...

Name : {@Retest}
Formula : IF Sum ({@QualityCount}, {Class_txt.Person}) = Sum ({@QualityDue}, {Class_txt.Person}) THEN "RETEST" ELSE ""


Name : {@QualityCount}
Formula : IF {Class_txt.Class} = "Quality" THEN 1
ELSE 0


Name : {@QualityDue}
Formula : IF {Class_txt.Class} = "Quality" AND
((Date (Year (CurrentDate), Month (CurrentDate), 1)) - (Date (Year ({Class_txt.Date}), Month ({Class_txt.Date}), 1))) >= 365
THEN 1
ELSE 0

I added an extra record to Mary to make sure it worked, and I Grouped on the PERSON.

The record set returned like this...

Person Class Date QualityCount QualityDue
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Frank Quality 05/2003 1 0
FRANK 1 0


John Quality 31/2003 1 1
John Equip1 31/2003 0 0
John Quality 28/2004 1 0
JOHN 2 1


Mary Quality 05/2002 1 1
Mary Quality 05/2003 1 1
Mary Equip2 07/2003 0 0
MARY 2 2 RETEST


Steve Quality 28/2003 1 1
Steve Quality 31/2004 1 0
STEVE 2 1
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

You can supress the other records based on the RETEST formula.

 

[lbass]

MJRBIM's solution should work fine as long as you only want to evaluate whether people who have already taken the class need to take it again (which is what you indicated), but I wonder whether you might also want to display people who have never yet taken the class. If so, you would need to modify the approach. Let us know if that is the case.

Since it appears you want to use your existing report, you could choose to highlight records that meet your criterion. If you are able to, you could insert a group on {class.classname} and then go to the section expert->highlight the section your records are in->color tab->background->x+2 and enter:

if {class.classname} = "Quality" and
{class.date} = maximum({class.date},{class.classname}) and
datediff("m",{class.date}, currentdate) > 12
then cryellow else crnocolor

-LB

 

[gentrya]

Thanks! I combined both of solutions, and it is what was wanted.

age