Here's the formula:

if NthLargest (1, {REVIEW.ACTUAL_DATE}, {EMPLOYEE.EMPLOYEE}) = CDateTime (1700, 01, 01, 00, 00, 00) then totext
("N/A") else totext({REVIEW.ACTUAL_DATE},"MM/dd/yyyy")

It works, except it is not giving me the Most recent review date. Example: employee 1234 had a review done on 11/16/05 and 11/16/04 the report displays the 11/16/04 review??? WHy?? :afro:

probably has to do with the join - and/or any other selection criteria you have. Can you elaborate on how this report is defined from a DB perspective?

Hey George -

well basically this report is a summary of active employee's displaying their Last Review Date, Next Review Date with some parameters to specify a department. Because the manager only want one line per employee record, I had to place everything on a Group Header field. For the Last Review Date I did the above totext command so that bad date = 1/17/1700 does not display. Then, I tried to do get the most recent Last Review date with that formula and it doesn't work - gives me the oldest.

The problem is not with your formula - its with the query on the data. The EMPLOYEE to REVIEW join is a one to many so the record that is displaying is the first one that happens to rise to the top of a natural select. You only want the ONE record from REVIEW that is the greatest (most recent) review date. In basic SQL terms you need something like:

select max(REVIEW.ACTUAL_DATE), EMPLOYEE.LAST_NAME,EMPLOYEE.FIRST_NAME
from REVIEW
INNER JOIN EMPLOYEE on EMPLOYEE.EMPLOYEE = REVIEW.EMPLOYEE
Group By REVIEW.EMPLOYEE

There are several ways you could do this - depending upon how you use crystal. If you need any more info on how to do this let me know...

here's mine:


SELECT "EMPLOYEE"."EMPLOYEE", "EMPLOYEE"."LAST_NAME", "EMPLOYEE"."FIRST_NAME", "EMPLOYEE"."MIDDLE_INIT", "EMPLOYEE"."EMP_STATUS", "EMPLOYEE"."ADJ_HIRE_DATE", "PAEMPLOYEE"."NEXT_REVIEW", "PAEMPLOYEE"."NEXT_REV_CODE", "EMPLOYEE"."HM_ACCT_UNIT", "EMPLOYEE"."PROCESS_LEVEL", "REVIEW"."ACTUAL_DATE"
FROM "LAWSON"."EMPLOYEE" "EMPLOYEE", "LAWSON"."PAEMPLOYEE" "PAEMPLOYEE", "LAWSON"."REVIEW" "REVIEW"
WHERE (("EMPLOYEE"."COMPANY"="PAEMPLOYEE"."COMPANY") AND ("EMPLOYEE"."EMPLOYEE"="PAEMPLOYEE"."EMPLOYEE")) AND (("PAEMPLOYEE"."COMPANY"="REVIEW"."COMPANY" (+)) AND ("PAEMPLOYEE"."EMPLOYEE"="REVIEW"."EMPLOYEE" (+))) AND "EMPLOYEE"."HM_ACCT_UNIT"='7230' AND NOT ("EMPLOYEE"."EMP_STATUS"='RT' OR "EMPLOYEE"."EMP_STATUS"='UR' OR "EMPLOYEE"."EMP_STATUS"='US' OR "EMPLOYEE"."EMP_STATUS"='XF' OR "EMPLOYEE"."EMP_STATUS"='XI' OR "EMPLOYEE"."EMP_STATUS"='XP' OR "EMPLOYEE"."EMP_STATUS"='XR' OR "EMPLOYEE"."EMP_STATUS"='XU' OR "EMPLOYEE"."EMP_STATUS"='XV' OR "EMPLOYEE"."EMP_STATUS"='XZ') AND ("EMPLOYEE"."PROCESS_LEVEL"='1' OR "EMPLOYEE"."PROCESS_LEVEL"='2' OR "EMPLOYEE"."PROCESS_LEVEL"='3')
ORDER BY "EMPLOYEE"."HM_ACCT_UNIT"

Change your formula to take out the "nthLargest" testing abd try this instead:

SELECT "EMPLOYEE"."EMPLOYEE", "EMPLOYEE"."LAST_NAME", "EMPLOYEE"."FIRST_NAME", "EMPLOYEE"."MIDDLE_INIT", "EMPLOYEE"."EMP_STATUS", "EMPLOYEE"."ADJ_HIRE_DATE", "PAEMPLOYEE"."NEXT_REVIEW", "PAEMPLOYEE"."NEXT_REV_CODE", "EMPLOYEE"."HM_ACCT_UNIT", "EMPLOYEE"."PROCESS_LEVEL", MAX("REVIEW"."ACTUAL_DATE")
FROM "LAWSON"."EMPLOYEE" "EMPLOYEE", "LAWSON"."PAEMPLOYEE" "PAEMPLOYEE", "LAWSON"."REVIEW" "REVIEW"
WHERE (("EMPLOYEE"."COMPANY"="PAEMPLOYEE"."COMPANY") AND ("EMPLOYEE"."EMPLOYEE"="PAEMPLOYEE"."EMPLOYEE")) AND (("PAEMPLOYEE"."COMPANY"="REVIEW"."COMPANY" (+)) AND ("PAEMPLOYEE"."EMPLOYEE"="REVIEW"."EMPLOYEE" (+))) AND "EMPLOYEE"."HM_ACCT_UNIT"='7230' AND NOT ("EMPLOYEE"."EMP_STATUS"='RT' OR "EMPLOYEE"."EMP_STATUS"='UR' OR "EMPLOYEE"."EMP_STATUS"='US' OR "EMPLOYEE"."EMP_STATUS"='XF' OR "EMPLOYEE"."EMP_STATUS"='XI' OR "EMPLOYEE"."EMP_STATUS"='XP' OR "EMPLOYEE"."EMP_STATUS"='XR' OR "EMPLOYEE"."EMP_STATUS"='XU' OR "EMPLOYEE"."EMP_STATUS"='XV' OR "EMPLOYEE"."EMP_STATUS"='XZ') AND ("EMPLOYEE"."PROCESS_LEVEL"='1' OR "EMPLOYEE"."PROCESS_LEVEL"='2' OR "EMPLOYEE"."PROCESS_LEVEL"='3')
GROUP BY "EMPLOYEE"."EMPLOYEE", "EMPLOYEE"."LAST_NAME", "EMPLOYEE"."FIRST_NAME", "EMPLOYEE"."MIDDLE_INIT", "EMPLOYEE"."EMP_STATUS", "EMPLOYEE"."ADJ_HIRE_DATE", "PAEMPLOYEE"."NEXT_REVIEW", "PAEMPLOYEE"."NEXT_REV_CODE", "EMPLOYEE"."HM_ACCT_UNIT", "EMPLOYEE"."PROCESS_LEVEL", "REVIEW"."ACTUAL_DATE"
ORDER BY "EMPLOYEE"."HM_ACCT_UNIT"

Sorry - meant to color the GROUP BY as well - that is critical.

I did a new report - and the above returned 44 records....?

Will something like this work - In Formula Workshop - using crystal functions:

if {REVIEW.ACTUAL_DATE}= CDateTime (1700, 01, 01, 00, 00, 00) then
("N/A") else
(Maximum),({REVIEW.ACTUAL_DATE})

See I am getting the Max date to return just fine; It's just that DAMN 01/01/1700 date that shows up for anyone who hasn't had a review yet!!!! I want to make this "N/A" ??????

Thanks for everything George

Does

if {REVIEW.ACTUAL_DATE}= "01/01/1700" then...

work?

You COULD use the maximum function in a formula to group by it - but getting it done at the database level is MUCH more efficient. I suspect that originally that your max formula wasn't working because you were not sorting by this date.

Is 44 records accurate?

WOOO-HOOO got it too work!

Thanks a lot George!!