Selecting records on third field away...

[hallmg]

Using CR XI
Oracle 10g
Two tables linked on group:

N0 (employee data)
empid
group
reportsto (empid of the person this record reports to)

G0 (org data)
group
orgcode

The orgcode keeps the group heirarchy and is formatted as 01010100000000 where each two characters is a level in the heirarchy. If a person's orgcode is 010101, the people who report to him/her may be 01010102, 01010104, 0101010A.

I want the user to be able to select a person from a parameter list and return all the people that report to him/her and all their reports.

Since I have the reportsto, I can get the immediate reports (reportsto = ?empid) of the selected person. The orgcodes of those people would get me everyone else all the way to the bottom of the heirarchy. Basically, I need to be able to get the orgcode of the people who report to the selected person and select records based on those orgcodes.

I tried writing some convoluted record selection formula which returned an OR statement (this orgcode or this orgcode etc.), but couldn't get it to work.

Any suggestions?

 

[beltmanjr]

I remember reading something about crystal doing some extensive sql checking which would significantly delay the reporting speed.

Unfortunately I can't find it back in the forum anymore. But if such thing happens then after verifying the report once it could possibly be switched off for any future run and speed things up