Hi,

 

Each employee on this report is set up with a “Schedule”, “Pay Grade”, and “Pay Step” in a SCHED_Ttable. Tables is joined to another table that contains  these 3 data fields. It is the combination of these three data fields that determine their Pay Rate in the SCHED_Table. Here is a sample of the data in the table:

COMPANY	SCHEDULE	EFFECT_DATE	PAY_GRADE	PAY_STEP	PAY_RATE
9999	AB20ABC	1/1/1950	331	1	10
9999	AB20ABC	1/1/1950	331	3	12
9999	AB20ABC	1/1/1950	331	2	11
9999	AB20ABC	1/1/1950	333	2	21
9999	AB20ABC	1/1/1950	333	1	20
9999	AB20ABC	5/1/2010	333	2	23
9999	AB20ABC	5/1/2010	333	1	22
9999	AB20ABC	5/1/2010	331	3	12.5
9999	AB20ABC	5/1/2010	331	2	11.5
9999	AB20ABC	5/1/2010	331	1	10.5

 

Some rules I must follow in this challenge are:

1.       I can only use the Maximum Effect Date in the Effect Date column per each Schedule/Pay Grade/Pay Step combination.

2.       If there is no higher step for that Schedule/Pay Grade/Step combo, then the employee does not qualify for a salary increase, and needs to be excluded from the report.

 

Example1: Bob's current schedule/pay grade/step and pay rate are in line 7. He qualifies for the next step and next pay rate, line 6. He is at Step 1, pay rate $22. The next Pay Step for his Schedule/Pay Grade is 2 and the next Pay Rate is $23. In the report, I would like to show his “current” Pay Step and Pay Rate, as well as his “Next” Pay Step and Pay Rate.

 

Example 2: Bob's current Schedule/Pay Grade/Pay Step and Pay Rate are in line 8. He qualifies for the next Pay step and next pay rate, except, he is at the highest step within his Schedule/Pay Grade. Therefore, he does not qualify for a salary increase, and should be excluded from the report.

 

Note: the table above does not store the Schedule/Pay Grades/Steps in any particular order.

Edited by thummel1 - 24 May 2012 at 4:40am

Very interesting and good to know. I was able to execute this in Excel, but required the tables to be "cleaned up" before I could execute a workable formula. Thanks again!

I thought I would follow up on this question. I was able to get this to work! In summary, I needed to create a sub-report. Before I did that, I created a formula to my main report  to add 1 to each Step for each employee (called "StepPlus1"). Then, in my sub-report, I joined on Schedule, Pay Grade, and Pay Step from the PRSAGTL table (Table that stores all the Pay Step Data) to the "StepPlus1" field in my main report.  Then I was able to pull in the Next Step and Next Pay Rate for each employee. And because of the joins, for any employee at their max step the sub report data is blank, thereby identifying anyone at their max. Now I can just suppress that data if I do not want to display it in the report.