Prev
Next
Forums Performance Management Lawson Business Intelligence/Reporting/Crystal

Crystal and PRSAGDTL.EFFECT_DATE

 6 Replies
 0 Subscribed to this topic
 22 Subscribed to this forum
Sort:
Author
Messages
Maria
New Member
Posts: 3
New Member
5/20/2008 2:11 PM

    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
    Posts: 3353
    5/20/2008 2:25 PM
      In Crystal, are you reporting from tables or from a SQL command?
      Thanks for using the LawsonGuru.com forums!
      John
      Maria
      New Member
      Posts: 3
      New Member
      5/29/2008 2:06 PM
        I am using tables
        John Henley
        Posts: 3353
        5/29/2008 6: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.

          Attachments
          Thanks for using the LawsonGuru.com forums!
          John
          Maria
          New Member
          Posts: 3
          New Member
          5/30/2008 1:36 PM
            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
            Advanced Member
            Posts: 27
            Advanced Member
            3/21/2016 2:48 PM
              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
              Veteran Member
              Posts: 151
              Veteran Member
              3/21/2016 4: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