Displaying Hierarchical Reports

A hierarchical report shows the relationship between records in the same table using a tree format. An example is an Organization Chart that uses the personnel table to display a list of its employees and their supervisors. Each employee has a record in the personnel table. But each employee also has a supervisor that they report to and that supervisor is in the same personnel table. Thus, there is a relationship between the employee and their supervisor and both entities come from the same personnel table. This is ideal for using a hierarchical report to show the relationship between employees and their supervisors.

There are three requirements for using hierarchical reports.

1. A record has two fields that represent the same data. In the Org Chart example the Employee ID is the key field for the record. Within the record is the Supervisor ID field and it points to a different record within the same table.
2. The report must be grouped on the child field (e.g. the Employee ID).
3. The parent and child fields must be the same data type.

Trying to figure out how to make an Organizational Chart using records that point back to themselves can get a little complicated. Rather than do the work yourself, let Crystal Reports do it for you. Open the Hierarchical Options dialog box by selecting Report > Hierarchical Grouping Options.

Figure 3-19. The Hierarchical Group Options dialog box.

This dialog box shows a list of all the groups within the report. The Available Groups list shows the current groups in your report. You can only create a hierarchical report using one of these existing groups. To use it for hierarchical grouping, click on the group that uses the child field (e.g. Employee ID) and check the Sort Data Hierarchically checkbox. In the Parent ID dropdown box below the checkbox, select the field that will be the parent field (e.g. Supervisor ID). This establishes the relationship between the two fields and Crystal Reports automatically groups the records so that the parent records are shown before the child records.

The last option in the dialog box is the Group Indent value. This sets the indentation level between a parent record and its child record. The default value is 0.25 inches.

You need to be careful when formatting a hierarchical report for the first time. As mentioned earlier, the default format for printing is a tree-view format. As you can see in Figure 3-20, the parent records start on the left-most side of the report and the child records are indented at each level.

Figure 3-20. The Hierarchical Grouping report with indenting.

Shifting the columns to the right is fine when there are only a few columns. But when a report has many columns, then there isn’t room for shifting and the data can be pushed into the adjacent column. If this causes a problem, you can either make the Group Indent value small or just set it to zero. Setting it to zero insures that the data in each column is lined up exactly with the column header above. This looks the same as a typical report, as you can see in the next figure.

Figure 3-21. The Hierarchical Grouping report with indentation set to 0.

Another option is to only indent the description fields and not indent the numeric values. This requires using advanced functions and the process for doing can be found in the Advanced Tutorials at the end of this chapter.