PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 03/21/2016 12:18 PM by  mikeP
Crystal and PRSAGDTL.EFFECT_DATE
 6 Replies
Sort:
You are not authorized to post a reply.
Author Messages
Maria
Private
Private
New Member
(7 points)
New Member
Posts:3


Send Message:

--
05/20/2008 10:11 AM

    I am tying to pull the max EFFECT_DATE frm the PRSAGDTL table in Crystal and cannot find a formula or command that will do this. Does anyone have any ideas or has had an issue with this that can help me out. I would appeciate any and all help.

    Thanks

    Maria

    John Henley
    Private
    Private
    Senior Member
    (9563 points)
    Senior Member
    Posts:3205


    Send Message:

    --
    05/20/2008 10:25 AM
    In Crystal, are you reporting from tables or from a SQL command?
    Thanks for using the LawsonGuru.com forums!
    John
    Maria
    Private
    Private
    New Member
    (7 points)
    New Member
    Posts:3


    Send Message:

    --
    05/29/2008 10:06 AM
    I am using tables
    John Henley
    Private
    Private
    Senior Member
    (9563 points)
    Senior Member
    Posts:3205


    Send Message:

    --
    05/29/2008 2:50 PM

    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.

    Thanks for using the LawsonGuru.com forums!
    John
    Attachments
    Maria
    Private
    Private
    New Member
    (7 points)
    New Member
    Posts:3


    Send Message:

    --
    05/30/2008 9:36 AM
    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.
    Katy Prince
    Systems Analyst
    Tompkins County
    Basic Member
    (46 points)
    Basic Member
    Posts:20


    Send Message:

    --
    03/21/2016 10:48 AM
    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!
    mikeP
    Private
    Private
    Veteran Member
    (407 points)
    Veteran Member
    Posts:151


    Send Message:

    --
    03/21/2016 12:18 PM
    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.

    ================
       
      
     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 
     
     
    You are not authorized to post a reply.