Prev
Next
Forums Lawson S3 HR/Payroll/Benefits

Crystal Reports - Employee and User Field Tables

Sort:
You are not authorized to post a reply.
Author
Messages
martingibbs
Basic Member
Posts: 18
Basic Member
3/1/2010 1:54 PM
    Hello, I am trying to run a Crystal report directly against Lawson tables and link up EMPLOYEE and HREMPUSF. It works fine, as long as an employee has a value in the user field I'm looking at.

    I've tried Left Outer joins but to no avail.... any ideas in getting user fields that are null?

    SELECT "EMPLOYEE"."EMPLOYEE", "EMPLOYEE"."ADJ_HIRE_DATE", "EMPLOYEE"."PROCESS_LEVEL", "HREMPUSF"."A_FIELD", "HREMPUSF"."FIELD_KEY"
    FROM "LWNDBA11"."EMPLOYEE" "EMPLOYEE" RIGHT OUTER JOIN "LWNDBA11"."HREMPUSF" "HREMPUSF" ON ("EMPLOYEE"."EMPLOYEE"="HREMPUSF"."EMPLOYEE") AND ("EMPLOYEE"."COMPANY"="HREMPUSF"."COMPANY")
    WHERE "EMPLOYEE"."EMPLOYEE"=999999
    AND "EMPLOYEE"."ADJ_HIRE_DATE"<={d '2009-12-31'} AND "HREMPUSF"."FIELD_KEY"='99'
    Margie Gyurisin
    Veteran Member
    Posts: 538
    Veteran Member
    3/1/2010 2:10 PM
      A sub report may work for you.
      Jay Riddle
      Veteran Member
      Posts: 191
      Veteran Member
      3/1/2010 2:15 PM
        It should be a left outer join and the "HREMPUSF.FIELD_KEY" = '99' should be moved up so that it is part of the FROM clause.
        Chris Martin
        Veteran Member
        Posts: 277
        Veteran Member
        3/1/2010 2:36 PM
          A subreport would work, but in my opinion the easiest and most efficient way to accomplish this from a report performance perspective is via nested queries in your SELECT statement. This will require you use a SQL Command as the data source of your report.
          John Henley
          Senior Member
          Posts: 3348
          Senior Member
          3/1/2010 2:58 PM
            This is the approach I use--it will return employee as well as present and non-present user field values.

            SELECT EMP.COMPANY, EMP.PROCESS_LEVEL, EMP.EMPLOYEE, EMP.ADJ_HIRE_DATE, HRU.FIELD_KEY, HEU.A_FIELD
            FROM EMPLOYEE EMP
            LEFT OUTER JOIN HRUSERFLDS HRU
            ON (HRU.FIELD_KEY = '91')
            LEFT OUTER JOIN lawson.HREMPUSF HEU
            ON (HEU.COMPANY = EMP.COMPANY)
            AND (HEU.EMP_APP = 0)
            AND (HEU.EMPLOYEE = EMP.EMPLOYEE)
            AND (HEU.FIELD_KEY = HRU.FIELD_KEY)
            WHERE EMP.EMPLOYEE = 999999
            AND EMP.ADJ_HIRE_DATE <= '2009-12-31'
            Thanks for using the LawsonGuru.com forums!
            John
            martingibbs
            Basic Member
            Posts: 18
            Basic Member
            3/1/2010 4:35 PM
              John, that worked perfectly. Thank you!!
              Shane Jones
              Veteran Member
              Posts: 460
              Veteran Member
              3/2/2010 1:56 AM
                I think this is a much better long-term versatile solution for you. It is great because it makes ONE view that includes ALL of your user fields by name. If the field is empty it will NOT mess up your report.

                https://www.lawsonguru.co...ft/6856/Default.aspx

                (Just have a database view created like this for YOUR user fields... Anyone that knows databases will be able to use the SQL in the attached forum to create an SQL for YOUR user fields)

                :D
                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.