Need a report that shows previous pay rate

Sort:
You are not authorized to post a reply.
Author
Messages
Frank Z
Advanced Member
Posts: 32
Advanced Member
    I am building a report that shows current pay rates, effective date, reason for the change, as well as the previous rate. I have everything except the previous rate working, and I keep hitting the wall. My logic has been to use a nthLargest=2 on the PRRATEHIST.BEG_DATE field. To pull the previous pay rate I then use the formula:

    if {PRRATEHIST.BEG_DATE} =
    NthLargest (2, {PRRATEHIST.BEG_DATE}, {EMPLOYEE.EMPLOYEE})
    then {@Pay Rate}

    Even though the {NthLargest (2, {PRRATEHIST.BEG_DATE}, {EMPLOYEE.EMPLOYEE})} field returns a value that is equal to the 2nd most recent begin date, it is returning values of 0. Can anyone lead me in the right direction to get this report up and running?

    TIA
    John Henley
    Senior Member
    Posts: 3348
    Senior Member
      In order to show "previous" values on the same detail line as the "current" value, you either need to create a subreport (passing it the company, employee, and NthLargest (2, {PRRATEHIST.BEG_DATE}, {EMPLOYEE.EMPLOYEE}) ) to retrieve the correct record, or use SQL a command that includes a subquery. I have found the latter option to be much more efficient as well as reliable (remember that multiple changes could exist for the same day, so you might have multiple seq-nbr values for the same date). Here are a couple of samples that do similar things:


      Thanks for using the LawsonGuru.com forums!
      John
      Shane Jones
      Veteran Member
      Posts: 460
      Veteran Member
        I built a database view that shows each pay record with the previous value. It shows everything on one line. When the associate is a new hire it just shows a null for the the previous value. This has been used for pay rates and other HRHistory table items. However, to John's point, the seq-nbr has caused some issues with the output and I am still trying to make sure that it works correctly everytime. (garbage-in garbage-out)... *the issue is more with corrections that do not display correctly.
        Shane Jones
        Tools: HR, Payroll, Benefits, PFI, Smart Office, BSI, Portal and Self-Service
        Systems: Lawson, Open Hire, Kronos, Crystal Reporting, SumTotal Learning
        ** Teach others to fish...
        You are not authorized to post a reply.