Crystal and PRSAGDTL.EFFECT_DATE

In Crystal, are you reporting from tables or from a SQL command?

I am using tables

I'm assuming you are joining from EMPLOYEE to PRSAGDTL, and want to report the most recent step-and-grade pay rate for the employee? Off the top of my head, I can think of two ways to do this in Crystal:
1) Using a sub report, and passing from the employee to the subreport the schedule, grade and step. In the subreport, select for the passed schedule, grade and step. Then suppress the details and use 'insert group' on one of those fields, and summarizes based on max() of the effective date. Put the fields into the group footer.
2) You can also do something similar without the sub report, using insert group for employee, schedule, grade, and step. suppress the details, as well as the group header/footer for schedule and grade. Insert a summary for effective date, putting the maximum into the group footer for step. Then, use the 'Group Sort Expert' to create a 'TOP N', where N = 1 based on the max of the effective date. What this will do is only show the latest effective date for each schedule/grade/step.

I was hoping there would be a formula or something that I caould utilize but I will go ahead and try this to see if it will work. This was a report that was created already and I am trying to modify it to fit the company's needs. Thanks for the help.

This has been a "thorn in my side" for a while now. What I need is to pull from a date within these tables. It would be GREAT if Infor would put in this table and end date so when a new schedule with the same name was added, it would put an end date on the old schedule!

I understand you not wanting to change a report that's already using tables. If you want, it is possible to add a SQL command to a table based report and link it to the existing tables on the Database Expert Links tab.

Here's an expression we use in SQL based reports that find the PRSAGDTL recs current as of a specific date. (The example show GETDATE(), but we'd normally use a parameter.)

I put it in a command, then linked the command to a simple report that contains the PAEMPOS and EMPLOYEE tables.

================
[code] SELECT SCHEDULE, PAY_GRADE, PAY_STEP, PAY_RATE, EFFECT_DATE FROM ( SELECT SCHEDULE, PAY_GRADE, PAY_STEP, PAY_RATE, EFFECT_DATE, ROW_NUMBER() OVER (PARTITION BY SCHEDULE, PAY_GRADE, PAY_STEP ORDER BY EFFECT_DATE DESC) AS RowCnt FROM PRSAGDTL WHERE EFFECT_DATE<=GETDATE() ) S WHERE RowCnt =1 [/code]