Who's On?

	Membership:
	Latest: Saef
	Past 24 Hours: 0
	Prev. 24 Hours: 0
	Overall: 5226

	People Online:
	Visitors: 195
	Members: 0
	Total: 195

Name	Points
Greg Moeller	4184
David Williams	3349
JonA	3291
Kat V	2984
Woozy	1973
Jimmy Chiu	1883
Kwane McNeal	1437
Ragu Raghavan	1372
Roger French	1315
mark.cook	1244

Crystal Reports - Employee and User Field Tables

6 Replies

0 Subscribed to this topic

68 Subscribed to this forum

Sort:

Author

Messages

martingibbs

Basic Member

Posts: 18

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

3/1/2010 2:10 PM

A sub report may work for you.

Jay Riddle

Veteran Member

Posts: 191

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

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

Posts: 3353

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'

martingibbs

Basic Member

Posts: 18

3/1/2010 4:35 PM

John, that worked perfectly. Thank you!!

Shane Jones

Veteran Member

Posts: 460

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