Creating Recursive Org Hierarchy with SubReports

An organizational hierarchy represents roles, responsibilities, and authority within an organization in a structured way. It represents both the chain of command (who reports to whom) and the mechanism by which decisions flow through the different organizational levels.

This article is a step-by-step tutorial on building a report representing an Org Hierarchy structure similar to the one below:

CEO
 ├── CTO
 │    ├── VP of Engineering
 │    │    ├── Engineering Manager
 │    │    │    ├── Engineers
 ├── CFO
 │    ├── Finance Manager
 │    │    ├── Accountants

The approach demonstrated here uses a recursive SubReport to construct a hierarchy from flat data. The main report references itself in a SubReport item, and each SubReport filters its data using a report parameter passed from its parent. This process continues recursively, building the hierarchy until no more child items remain:

To create the Organizational Hierarchy, follow the steps:

1. Create a new report (OrgStructure.trdp), which you will use as a report to show the employees.

2. Delete the Header and Footer. Leave only the Detail Section.

3. Create a CSV data source item using the self-referencing flat data for the employees. Every data record contains own EmployeeID and the ID of the manager this person reports to, forming the chain-of-command up to the company CEO:

   CSV

   EmployeeID,Name,JobTitle,ManagerID,Department,Location
   1,Alice Johnson,CEO,0,Executive,New York
   2,Bob Smith,CTO,1,Technology,New York
   3,Carol White,CFO,1,Finance,New York
   4,David Brown,VP of Engineering,2,Technology,San Francisco
   5,Eva Green,VP of Product,2,Product,San Francisco
   6,Frank Black,Engineering Manager,4,Technology,Austin
   7,Grace Lee,Engineering Manager,4,Technology,Austin
   8,Henry Adams,Product Manager,5,Product,Seattle
   9,Ivy Clark,Senior Engineer,6,Technology,Austin
   10,Jack Davis,Senior Engineer,7,Technology,Austin
   11,Karen Lewis,Engineer,6,Technology,Austin
   12,Leo Martin,Engineer,7,Technology,Austin
   13,Mia Nelson,Finance Manager,3,Finance,Chicago
   14,Noah Perez,Accountant,13,Finance,Chicago
   15,Olivia Quinn,Accountant,13,Finance,Chicago
   16,Paul Roberts,HR Director,1,Human Resources,New York
   17,Quinn Taylor,HR Manager,16,Human Resources,Chicago
   18,Rachel Scott,Recruiter,17,Human Resources,Chicago
   19,Sam Turner,Legal Counsel,1,Legal,New York
   20,Tina Walker,Compliance Officer,19,Legal,New York
   

   

4. Toggle the option This CSV has header row.

5. For the EmployeeID and ManagerID columns, use the Integer type.

   

6. Set the Report's data source to the just created CSV data source.

7. Create a report parameter (reportParameterManagerID) of type Integer with Value 0.

   

8. Add two TextBox items and bind their values to the Name and JobTitle fields respectively:

   

9. Now, filter the CSV data based on the ManagerID. Add a Filter based on the ManagerID field using the report parameter's value:

   = Fields.ManagerID = = Parameters.reportParameterManagerID.Value

10. Click the Preview button. You will see only the CEO (Alice Johnson) because the ManagerID=0 and only the CEO meets the filter criterion.

11. Add a SubReport item and set the Report source Type to UriReportSource using the same OrgStructure.trdp as Uri:

    

12. Create a parameter (reportParameterManagerID) whose value is bound to the EmployeeID. Thus, the current EmployeeID will be passed as a value for the ManagerID for the SubReport.

    

13. Preview the report to see the organization hierarchy:

    

See Also

• Web Report Designer
• Data Sources