company heirarchy report

[retygh45]

Using CR11 and MSSQL, I'm trying to create an organization listing of the entire company.

My HR table lists fname, lname, userID, title, and Manager. Everyone reports to someone up until the CEO.

I have the userID in the details section of my report, and the report is grouped on Manager. This just gives me 1 level of who reports to who. So I added the same table again to the report and linked the two tables together with an inner join from {HRtable.Manager} to {HRtable_1.userID}
and then added another grouping level to the report on {HRtable_1.Manager}

I thought I could just keep doing this until I got to the top, but when I add the manager group again, it breaks the grouping into 2 sections, the CEO and his direct reports and again for his direct reports and then their direct reports, etc.

Any suggestions on how I may be able to correct this? I'd like to just have 1 top group: CEO, then under him should just be the COO and the CFO, etc. I've confirmed the table data is correct, each person's manager is the userID of their manager.

Any help is greatly appreciated, thanks!

 

[dgillz]

Have you looked at hierarchecal grouping in crystal?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[retygh45]

Thanks! That works well. I'm new to CR11 and don't remember that feature in CR10.

One question: Is there a way to limit the results to just a certain userID?

My report is grouped on UserID, then the heirarchical grouping is set to userID and the parent ID field is set to their managers' ID. This grouping works well, but if I want to restrict the report to just the person's direct reports, I set the criteria to be:

({table1.userID}={?UserID} OR {table1.manager}={?UserID})

This works for managers to show just themselves or their direct reports. But one level above managers, is Director, and this only shows the Director and their direct reports. It excludes the people who report to the directors' managers.

Is there a way to include more levels?

Thanks!!