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?

 

[CoSpringsGuy]

someone else will have to chime in on this but it looks to me like if you are linking the above table on group... then every empid will take on the groups orgcode. I dont think that is the desired result.... ?????

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[hallmg]

If I don't filter the report, I get the correct orgcode for each employee record. So, I think the linking is working correctly.

empid   repto   group   orgcode
1               top     01
2       1       sales   0101
3       2       mktg    010101
4       3       cad     01010101
5       2       sales2  010102
6       3       pbs     01010102

so, say the user picks 3, I would want 4 and 6. Everyone under 4 will have an orgcode that starts with 01010101 and everyone under 6 01010102. But I can't just use 010101 because everyone that reports to 2 (as 3 does) will have that orgcode.

Maybe that helps. Maybe it doesn't. I can't think of a better way to say it.

 

[CoSpringsGuy]

if you were to use a parameter field and set your record selection criteria to

table.repto = {?paramfield}

that should only return
4 3 cad 01010101
6 3 pbs 01010102
if the user input 3


_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[beltmanjr]

CoSpringsGuy,
I dont get the problem either.

Linking the tables NO & GO on group will give you the required orgcodes for the people that report to the selected EmpId ({NO.repto} = {?parameter}.

Link any further tables on the orgcodes...

 

[hallmg]

CoSpringsGuy,

It's the "should only" that's getting me. You made me think to create the parameter so it shows the person's name but returns the reportsto instead of the empid. I thought I was on a roll, but it only nets me the people who report directly to the selected person.

So, I thought I could have the parameter display the person's name but return the orgcode. Then I'm back to square one where I get more than I want.

So, back to what you said:

if you were to use a parameter field and set your record selection criteria to

table.repto = {?paramfield}

that should only return
4 3 cad 01010101
6 3 pbs 01010102
if the user input 3

Since the parameter returns 4 and 6 (not the orgcode of 4 and 6), how do I get/use 4 and 6's orgcode in the record selection in order to get everyone below them?

 

[lbass]

I think you need to add the employee table twice, linking it from the 'employee ID' in the first table to the 'reports to' field in the alias table. This might mean you need to add the other table twice, too, but I'm not sure how you're using it.

-LB

 

[beltmanjr]

The whole thing strikes me as odd and I must admit that I'm not certain what your objective is.

Do you wish to get everyone reporting to EmpID 3 and everyone reporting to those reporting to EmpID 3? So in essence you get the complete hierarchy of people reporting to 3 and his subordinates?

please explain your objective clearly and I'm certain we'll be able to figure it out. Also try to explain the link with the orgCode more clearly? What does it do? How does it occur for those below these people 4 & 6.

Whilst typing this I'm getting the idea of what you actually need.

I'll try to work it out tomorrow morning. But please do explain a bit more anyway.

In short I think you want to find the people that report to (say) empId 3. From these people you wanna take the orgCode. This orgCode you will then want to use to find anyone under them.

This can be done by using an instr function. This comparing the found orgCode(s) with every orgCode in your table. Keeping in mind that you should look at the length of the orgcode found, truncate all orgcodes in the database to that length and then compare otherwise you might pick up some incorrect ones.

I'm thinking that using a command with parameter might be the best way of doing this, but commands should be avoided at all costs as they slow the report down like hell...

A real challange!!!

 

[CoSpringsGuy]

I am more inclined to agree with the approach LBass mentioned. But I do agee with your first sentence...

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[hallmg]

The whole thing strikes me as odd..."

-- I agree!

"In short I think you want to find the people that report to (say) empId 3. From these people you wanna take the orgCode. This orgCode you will then want to use to find anyone under them."

-- Exactly! Only because it's the only way I can think of to get everyone all the way down.

"This can be done by using an instr function."

-- I tried this yesterday but didn't quite get there.

Let me try to explain it this way.

Each group has a unique orgcode based on its reporting relationship to the group above it.
Each person belongs to a group, therefore has a unique orgcode.

Let's say my boss has 5 people reporting to him. Previously, each of those people would have belonged to a unique group. When it was like that, we could find me, get my orgcode and know that everyone that worked for me would have that orgcode.

Now, HR has changed it so that all of those 5 people may belong to the same group (and thus have the same orgcode). So, if I look up me and use my orgcode, I may get people who report to 3 out of 5 of those people.

Therefore, I need to know the personal reporting relationship to get the orcode for only those people that report to me and use only their orgcodes.

I know that still sounds convoluted. (What can I say?)

I did try instr yesterday, too, to compare it against the truncated orgcode of the selected person. But I need the orgcode of the people that report to the selected person. (I think)

if {Command.ORGCODE} like "*00"
  then mid({Command.ORGCODE},1 ,instr(1,{Command.ORGCODE},"00")-1 )
  else {Command.ORGCODE}

gives me the truncated orgcode just fine.

I thought I had it by defining the parameter value as the orgcode and the parameter description as the person's name. Then using

?OrgCode_Repto in @OrgCode_trunc

as the record selection criteria, which gave me more than I want.

I've got some other things to try today, hopefully.

Thanks for your help! (and patience!)

Oh, and it looks like my dynamic parameter list is being truncated and leaving people off the list. Is there a way to filter that list before presenting it to the user and still have it be dynamic?

 

[beltmanjr]

could you perhaps confirm what I think you are trying to accomplish? I'm gonna try to recreate the two tables and play with them.

NO TABLE
------------------------
empid   repto   orgcode
1               01
2       1       0101
3       2       010101
4       3       01010101
5       2       010102
6       3       01010102

GO TABLE
---------------
group   orgcode
top     01
sales   0101
mktg    010101
cad     01010101
sales2  010102
pbs     01010102

In the report for empid 2 you now wish to see:
all people in group 010101 (empid 3 leads this group)
all people in group 010102 (empid 5 leads this group)

In the report we display:
empid 4 & 6 as their orgcode starts with 010101
no others, as no one has an orgcode starting with 010102

Question: do you want group leaders empid 3 & 5 themselves displayed?

And is this indeed what you are after?

 

[hallmg]

Actually, it looks like I mislead you in a previous posting by not separating the data into the two tables. You have it almost right, but there is no orgcode in N0 as it resides solely in G0.

NO TABLE
------------------------
empid   repto   group   name
1               top     John
2       1       sales   Betty
3       2       mktg    Denny
4       3       cad     Mighty Al
5       2       sales2  Penny Pretty
6       3       pbs     George

GO TABLE
---------------
group   orgcode
top     01
sales   0101
mktg    010101
cad     01010101
sales2  010102
pbs     01010102

Remember that I said that N0 and G0 are linked by their group.

If user selects Betty (2) the report should return everyone except John (1), including everyone who reports to all those folks.

If the user selects Denny (3), the report should return only Denny and Mighty Al (4) and George (6) and everyone that reports to them (Denny and orgcodes 01010101 and 01010102), but not Penny Pretty and her reports, since she reports to Betty (2).

Thanks!

 

[lbass]

Did you try my suggestion?

-LB

 

[beltmanjr]

There are two options you could use.
I'm not keen to suggest using a command because it is slow, especially if the number of records are huge. But the command required would be like:

select * from dbo.NO join dbo.GO on NO.groupname = GO.groupname where orgcode like (select GO.orgcode from dbo.NO join dbo.GO on NO.groupname = GO.groupname where empid = {?empid})+'%'

You need to put in the correct references to your tables and in the create command screen create the empid parameter.

You could also do this using a subreport, but that will give issues exporting to csv, xls etc.

For the subreport idea you first use both tables in the main report and join the on the groupname. Then create a parameter for the empid, go to the select expert and put down {table.empid} = {?empid}

Now create the subreport. Both tables go in the subreport and are linked on groupname again. Put all required fields in there (all you wanna display)

Now goto 'edit subreport links' and select as subreport link {table.orgcode} and untick 'select data in subreport based on field:'

Now edit the subreport and in the select expert get something down like this

{GO.orgcode} like {?Pm-GO.orgcode}+'*'

Now run the report and it should work.

I was thinking about other ways with some grouping or formulea, but that wouldnt guarantee that you'd get the correct records. I'd use one of the solutions above

 

[hallmg]

LB,

No, not yet, haven't had a chance. (I've been pulled several other directions since my initial post...) I thought it a good one, though. I've used that technique before, but it hadn't occurred to me to use it here for some reason. I appreciate you bringing it up.

Thanks!

 

[CoSpringsGuy]

Belt -

could you clarify your statement about commands being slow? In what way do you mean. I have always found using a command table increases the speed of my reports. Especially if I use server side grouping and sorting commands in the SQL. I also read an FAQ on here supporting that. I must be confused as to what you mean.


_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[beltmanjr]

CoSpringsGuy,
I guess it depends on how you use them or on what size of table.

Commands are very similar to views hence I love them, but the times I have used them (we're talking about millions of records in these tables) they have been far slower then subreports or just creating a view on the database server itself.

If I do use them they have extensive joins in them and are joined to other tables in the report.

I'm not certain why they are so slow, perhaps anyone can inform me on how to speed things up? Is it something Crystal does that could be disabled? I've seen something about sql checks?

 

[lbass]

If you join commands to other tables, the joins will occur locally and thus slow the report. It is best practice to create the command so that it is your sole datasource. I generally find that they speed up a report significantly.

-LB

 

[beltmanjr]

Would creating a command be faster than linking individual tables?

And there was something about crystal verifying the sql which could be switched off?

 

[lbass]

I think it is faster, although it depends on many factors, I guess. In my case, we have an inefficient, complex database with millions of records and hundreds of tables, and hundreds of users tapping the same server. I'm not sure what your last question refers to. Whether or not you use a command, I believe CR will attempt to verify the database before running the report (at least in later versions), but why would you want to shut off a feature which probably is designed to ensure accurate data?

-LB