Subreport or Subquery

[ChiTownDiva]

I'm trying to run a report that will tell me who HAS NOT taken a class.

My logic is to find everybody on a history table and subtract them from the general population:

person - contains everybody in the company
person_course - contains everybody's training history.

If I did this in the form of a query, I would get:

SELECT P.PERS_LAST_NAME
FROM PERSON P
WHERE P.PERS_ID NOT IN
(SELECT PC.PERS_ID
FROM
PERSON_COURSE PC
WHERE
PC.LOCAL_CRSE_CD LIKE '{?Course}'
AND
PC.STATUS_CD = 'P' OR
PC.STATUS_CD = 'L' OR
PC.STATUS_CD = 'X' OR
PC.STATUS_CD = 'CS' OR
PC.STATUS_CD = 'CX' OR
PC.STATUS_CD = 'EQ' OR
PC.STATUS_CD = 'CM' OR
PC.STATUS_CD = 'CE' OR
PC.STATUS_CD = 'CC' OR
PC.STATUS_CD = 'PP' OR
PC.STATUS_CD = 'PM' OR
PC.STATUS_CD = 'PD' AND
TO_Date(PC.END_DT, 'MM/DD/YYYY')>='{?Start Date}' AND
TO_Date(PC.START_DT, 'MM/DD/YYYY')<='{?End Date}')
AND
P.LOCAL_PERS_ID LIKE '{?RC Code/HR}' AND
P.COMPANY LIKE '{?Company}' AND
P.ADDRESS_STATE LIKE '{?State}' AND
(P.PERSON_STATUS = 'A' OR
P.PERSON_STATUS = 'L') AND
P.MGT_LEVEL = '{?MGT Level}' AND
P.PERS_COST_CD LIKE '{?Cost Code}' AND
P.JOB_TITLE LIKE '{?Job Title}'

I want to put the inner query in the form of a subreport and pull in only the records on the main report that do not appear on the subreport.

I figure I need to pass variables back and forth, but how do I do that?

Thanks.

ChiTownDiva

 

[synapsevampire]

Why not just create a SP or View from this and use that as the data source? It'll be faster and it's already done!

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com