I developed a report listing all the employee review dates (spoke about it earlier) - but, have a question abou the data. I am pulling the ACTUAL_DATE from the REVIEW table (this is where we have our Last Review Dates stored) in my report and it lists all the reviews for that employee - EXCEPT I only want to see the most recent date. Any ideas. I need to keep it in the detail section, so Nth Largest = 1 on the Group Header/Footer wont work....any formulas I can use?? :banned:

Using a command object in Crystal, your sql can contain a nested select that limits the records to only an employee's most recent review date.

Currently I am linking 3 tables - will adding this command jeopardize the report at all?

Also,

the select (select max(r2.actual_date)
from review r2
where r2.employee = r.employee)


r2? is this correct = (SELECT MAX(REVIEW.ACTUAL_DATE)
FROM LAWSON.REVIEW REVIEW
WHERE REVEIW.EMPLOYEE = REVIEW.EMPLOYEE

??

r2 is just a table alias to distinguish the review table in the primary query from the review table in the nested query.

Was the report developed by adding tables through the Database Expert or are you using a command object? What's the third table, PAEMPLOYEE?

I added the tables through DataBase Expert.

PAEMPLOYEE provides next review date, and type.

This is the query I have:
SELECT REVIEW.EMPLOYEE, REVIEW.ACTUAL_DATE,
FROM LAWSON.REVIEW REVIEW,
WHERE REVIEW.ACTUAL_DATE = (SELECT MAX(LAWSON.REVIEW ACTUAL_DATE)
FROM LAWSON.REVIEW REVIEW
WHERE LAWSON.REVIEW EMPLOYEE = REVIEW.EMPLOYEE)

Losing me on the r2? Does this look ok? :werd:

Since the solution I am suggesting (which I'm sure there are many other ways to accomplish this) requires a nested query, you'll need to use the command object INSTEAD OF the database expert.

Fyi - I didn't ask, but I assumed the other table is EMPLOYEE.

It doesn't matter if the table alias for review in the nested query is r2 or xyz or whatever, but it can't be the same alias as you are using for the review table in the primary query.

Thanks! I got it to work by placing an Nth Largest = 1 field in the group header (w/ all the detail - and suppressed the detail. )

Thanks for everything.

:afro:

Glad to hear you resolved it in your own way.

As I said, there are many ways to accomplish this. You are choosing to utilize Crystal functionality. I tend to favor leveraging database functionality, as it usually allows for better runtime performance. Since you are just dealing with EMPLOYEE/PAEMPLOYEE/REVIEW tables, performance shouldn't be much of an issue. If you have report needs that involve large tables (ie HRHISTORY, GLTRANS, etc), I highly recommend leveraging database functionality (including stored procedures/packages). Although, I think you mentioned that you are working on a Siemens-hosted environment, which will likely limit the flexibility you have.