Displaying Hierarchical Reports

A hierarchical report displays data to show relationships between records in the same table using a tree format. This is similar to having a self-join SQL statement that needs to join a table to itself using a common field. For example, this can be used to show employees that are subordinate to another employee. When printing a supervisor list, you will show which employee reports to which supervisor. Ideally, you would link the Supervisor table to the Employee table. But in this circumstance, a supervisor is an employee as well. So you have to link the Employee table to the Employee table, thus creating a self-join SQL statement. Rather than do this work, open the Hierarchical Options dialog box. In this dialog box pecify which field is the parent and it will do all the linking for you. In this case, the Supervisor ID field is the parent of the Employee Id field. This is shown in Figure 4-8.

Figure 4-8. The Hierarchical Options dialog box.

The Available Groups listbox shows the current groups in your report. To use it for hierarchical grouping, click on it and then check the Sort Data Hierarchically checkbox. The field for that group will be the subordinate field. In the dropdown box below the checkbox, select the field that will be the parent field. Since the parent field and employee field are going to be linked together, and the parent field will be listed in the group header field, both of these fields must be the same data type. The default indentation is 0.25 inches. You can change that if you wish.

Figure 4-9 shows what the supervisor report looks like. This is the Hierarchical Grouping report that is installed in the Samples directory with Crystal Reports.

Figure 4-9. The Hierarchical Grouping report.

You might notice one small problem with hierarchical reports: when the rows are shifted to the right to represent a subordinate level, the columns no longer line up with the column header. Unfortunately, this can’t be prevented and you must compensate for it with wider than normal column header widths.