Need nulls in report

[loveyoursite]

CRV10. My report uses 2 tables: EMPLOYEE and JOBCODE. Purpose of report is to show average pay rate for each job code based upon the number of employees in that job. There is one group that is jobcode.
I have a left outer join from the JOBCODE table to the EMPLOYEE table. The joined fields are company and jobcode.
In the group footer is a sum field counting the nbr of employees in each job and a sum field averaging payrate.

Problem: Any job code that currently has no employees, does not show up on the report. I need ALL job codes to show up on the report even if no employees currently have that job. If no employees have the job, then the nbr of employees in that job count should be 0 and the average pay rate should be 0.00.

Here is the SQL:
SELECT "EMPLOYEE"."EMPLOYEE", "EMPLOYEE"."EMP_STATUS", "EMPLOYEE"."FIRST_NAME", "EMPLOYEE"."MIDDLE_INIT", "EMPLOYEE"."LAST_NAME", "EMPLOYEE"."PAY_RATE", "EMPLOYEE"."JOB_CODE", "JOBCODE"."DESCRIPTION"
FROM "LAWPROD"."JOBCODE" "JOBCODE" LEFT OUTER JOIN "LAWPROD"."EMPLOYEE" "EMPLOYEE" ON ("JOBCODE"."COMPANY"="EMPLOYEE"."COMPANY") AND ("JOBCODE"."JOB_CODE"="EMPLOYEE"."JOB_CODE")
WHERE NOT ("EMPLOYEE"."EMP_STATUS"='30' OR "EMPLOYEE"."EMP_STATUS"='31' OR "EMPLOYEE"."EMP_STATUS"='32' OR "EMPLOYEE"."EMP_STATUS"='33' OR "EMPLOYEE"."EMP_STATUS"='34')
ORDER BY "EMPLOYEE"."JOB_CODE"

Expected Result:
Job Code 0132 17 emps in job 12.34 Avg Pay rate
Job Code 0197 0 emps in job 0.00 Avg Pay Rate

Any help would be much appreciated.

 

[lbass]

You need to remove the selection criteria on the employee table, since it effectively undoes the left join, and instead use the criteria in formulas, as in {@count}:

If {EMPLOYEE.EMP_STATUS} in ['30','31','32','33','34'] then 0 else 1 //insert a sum on this formula for a count

For an average of payrate, use {@payrate}:

if {EMPLOYEE.EMP_STATUS} in ['30','31','32','33','34'] then 0 else {table.payrate}

Then create a formula {@avepayrate}:

sum({@payrate},{table.jobcode})/sum({@count},{{table.jobcode})

This assumes you have a group on {table.jobcode}.

-LB

 

[synapsevampire]

You might try adding to the Report->Edit Selection formula->Record

(
isnull({employee.jobcode})
)
or
(
<your current criteria>
)

Keep in mind that you have criteria on the child table(employee), so that criteria will overide the left outer join because you've explicity stated to only return those rows.

This is a common misunderstanding for SQL developers, you might check with your dba to see if they have a View which already properly presents this as that would be my approach.

-k

 

[lbass]

Oops,I should have had a null check in my formulas, too. They should be:

If isnull({EMPLOYEE.EMP_STATUS}) or
{EMPLOYEE.EMP_STATUS} in ['30','31','32','33','34'] then 0 else 1

If isnull({EMPLOYEE.EMP_STATUS}) or
{EMPLOYEE.EMP_STATUS} in ['30','31','32','33','34'] then 0 else {table.payrate}

The average formula would remain as it is.

-LB