hiearchy report. runs ok for ALL but no hierarchy when run for one ID

[yehong]

CR8.5.
I have a hierarchy report. When I run the report for ALL employees, it builds correct hierarchy and also shows correct group counts/sums. But when I pass a parameter for one Supervisor ID, say 001, it just shows all employees who work under that ID and also incorrect couts/sums. I expect it to not only show all employees under 001 but also show employees who work under the direct reports/emploees of ID 001.
Any idea why is crystal dropping the hierarchy in this case?

 

[synapsevampire]

It's probably you record selection formula.

Was this the Stored Procedure (SP) based report?

If so, it's probably either the report record selection formula, or the SP.

-k

 

[lyanch]

The heirarchical report only puts *the data you select* into the format. It doesn't *go get the rest of the data that would fill up the heirarchy*.

So, if the data you want doesn't show up if just ran the SQL statement in a SQL window.. it won't show up on your report.

Heirarchy is a grouping method, not a data selection method.

Lisa

 

[yehong]

Data is being pulled from a SQL Server 2K view. The record selection formula is:
{Table_1.SupervisorID} = '001'
Lisa, this is a point that when we run the SQL it does not pull the hierarchy just records where SupID=001.
Having said, is there any solution to this? We have limitations that we can not use store proc directly into the report.
I am thinking that me be pull all records in the report and supress records conditionally where ID <>001?

 

[lyanch]

yehong,

We need a better idea of what your data looks like and what you are trying to accomplish. If you could give us an example of data in and another of how you want it look, we can probably help you.

Lisa

 

[yehong]

Please copy and paste this into Notepad for better viewing.
This result is when the report is run for ALL.

6/15/200:41:49PM
Home Phone Salary

Fuller (206)555-9482 0,000.00

Buchanan (715)554-8485 0,000.00
Davolio (206)555-9857 0,000.00
Leverling (206)555-3412 3,000.00
Peacock (206)555-8122 5,000.00
Suyama (715)557-7737 0,000.00
King (715)555-5985 7,000.00
Callahan (206)555-1189 5,000.00
Dodsworth (715)554-4442 5,000.00

Hellstern (206)555-4869 0,000.00
Smith (206)555-3857 8,000.00
Patterson (206)555-3480 5,000.00

Brid (808)383-1662 5,000.00
Martin (886)243-5389 0,000.00
Pereira (880)901-6822 5,000.00


And this result is when the report is run for one ID, ie Fuller.

6/15/200:41:49PM
Home Phone Salary

Fuller (206)555-9482 0,000.00

Buchanan (715)554-8485 0,000.00
Hellstern (206)555-4869 0,000.00
Brid (808)383-1662 5,000.00

 

[synapsevampire]

It would be useful to know about the View design and example data.

I'm not sure of the intent, but for a recursive relationship, I add the table in a second time and join the approriate fields, such as:

PersonID ParentID Field1 Field2

So add in the table a second time and left outer join the ParentID to the PersonID

Now the Report->Edit Selection Formula->Record might contain:

{table.parentid} = "001"

Using the data from the second table will demonstrate the employees, the first table will give you the supervisor.

-k

 

[yehong]

Here is the table structure.
ID
SupID
Name
Title
DOB
HireDate
Phone
Salary

Also, I tried adding and linking same table again but still same results when run for one ID.

 

[lyanch]

You can't get what you are looking for using the heirarchy grouping method. You will need to manually create it and recursively get your data yourself.

1st add the table again, linking on mainTable.ID-> recursiveTable.SupID.

You then need to create groups for superviser, and employee (or however you want the groups to look like). The data won't be hierarchical because it will all be on one line.. you have to do the grouping yourself.

Lisa

 

[lyanch]

Ack... alternately you could try using "ors" in your selection formula.. ie

ID = 100 or SupID = 100 or xxxx

Lisa

 

[rosemaryl]

Dear Yehong,

First, I aplogize for the length of this post.

The problem that you have is that there is no "key" to indicate that an x level employee somehow reports up the line to ID 100, in other words there is no Root Parent key, and that's where the hierarchical grouping option in Crystal came in handy for you. When you say, I only want those related to Employee ID 100, there is nothing to select those records that may be related in a Parent/grandchild/great grandchild ... relationship.

When you left outer join from Table 1 to copy of table 1 aliased as Table 2, you will see all the direct reports per supervisor Id, but you would not see grouped with that employee id those who report to someone who reports to id 100.

Here is a visual example of what I mean

Employee A ID 100 Sup ID Null
Employee B Id 101 Sup ID 100
Employee C Id 102 Sup ID 100
Employee D Id 103 Sup ID 102

Now, hierarchically you would like to see that presented as:

Employee A ID 100 Sup ID Null
Employee B Id 101 Sup ID 100
Employee C Id 102 Sup ID 101
Employee D Id 103 Sup ID 102

However, with a left outer join from Table A to Table B on Employee ID to Sup ID you would see:

Employee A ID 100 Sup ID Null
Employee B Id 101 Sup ID 100
Employee B Id 101 Sup ID 100
Employee C Id 102 Sup ID 101
Employee C Id 102 Sup ID 101
Employee D Id 103 Sup ID 102

Now, when you go to do your selection criteria, what you are trying to do is slect all employees who may be somehow (child, grandchild, great grandchild and so on) related to the Supervisor.

So, in your parent child relationship as indicated in your fields, there is no field that exists that indicates this relationship above from employee d to employee a, by a simple selection criteria.

If you know the maximum number of levels down the tree in your org, you could alias that number of tables doing left outer joins from Employee id to Sup Id to each one on down the line...

I believe the only way to do what you need to do is to create a stored procedure that "walks the hierachical tree" and stores that data in a temp table from which you then select those that are related. Which is what I have done when a similar need arose for a Subject to Parent Subject relationships for a Call Center application.

I know that this doesn't exactly help you, but it may help you to understand the issue and to explain it to your dba to get what is needed. Here is a link that helped me to understand and code my stored procedure to accomodate this need.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro03/html/sql03b8.asp

A google on SQL +"Hierarchical Tree" will give you many more references.

Regards,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.