Hierarchical Reporting

This feature works with Column Reports, Treegrid Reports, Summary Reports, Matrix Reports, Charts, Planning Reports and Calendar Reports.

Your administrator will have defined hierarchies for reporting. These allow you to select records at a parent level, and at up to seven child levels for reporting. You may choose to view different fields on a column report for each of the different hierarchy levels, and you may apply different filters to each of the hierarchy levels. In addition, there are functions available that allow you to aggregate the count of the number of issues at each level in the hierarchy.

Note: You must be in the Advanced Query Screen mode to create reports that use reporting hierarchies.

An example of a hierarchy report is that you may want to show a list of all issues by customer, showing details from the Customer business area for each customer, while showing details from the Customer Issues business area for each issue. The following screen shows how this may be set up.

Creating a hierarchical report

First, make certain you are in Advanced Query Mode. Next, you select the hierarchy from the available list of hierarchies within the options section of the report design screen. When you do select the hierarchy, the screen refreshes and you will see a field selection list for each level of the hierarchy, as well as a set of filters to be used for each level of the hierarchy.

The report output will show all the fields selected at all levels of the hierarchy, in the order they appear over all the selection lists.

For each level of the hierarchy, you can apply any filters required for that level in the hierarchy.

Hierarchical Options

The following options apply to the child levels of the report filters you select.  Consider the parents with their children in this diagram:

• Include parents without children – In this case, all parents (John, Mary and Sheila) and all the children (Alison, Oscar, Leslie, Julie and Sophie) will be selected and displayed on the results.  The default if this is not selected as an option is that only John and Sheila and their respective children will be selected to display within the results.  This option is not supported for Charts
• Exclude parents with children – John and Sheila both have children, so only Mary will appear within the results.  This option is not supported for Charts
• Perform aggregate function on results – This option allows the COUNT of the items at the child level.  Other aggregate functions are also available.  If we select the Include parents without children option and count the ID field on the child level, we will get the count of John = 2, Mary = 0 and Sheila = 3 on the report output.  See below for additional details.

After following these instructions, you may see a Column report similar to the next screenshot.

Sample output of Hierarchical Report

Column reports support a special type of hierarchical reporting, called side-by-side hierarchies.  Reporting hierarchies are defined with a chain of descending levels, such in ways such as parent, child, and grandchild levels.  With side-by-side hierarchies, the user can select multiple, independent hierarchies, all with the same base (top) level, and place the results side-by-side on the report output.  For more information, click here.

Aggregation

In the report editor, you can turn on aggregate counting functions for child levels within a hierarchical report. For example, you may want a count of Fixed issues for each customer. This implies that you need to display each customer from the Customers business area and then count the Fixed issues.

Aggregate functions

This may be achieved by a variant of the above report. First, you check the box against the prompt Perform aggregate functions on results. The list of fields at the child level of the query (in this case the Issues level) will clear. Select a field and you may then use your right-hand mouse button to access the output options for the field. In our example, we can count the ID’s with an additional filter of Status = Fixed to achieve the results we would like to see. This selection process may result in a report something like:

Aggregate report showing count of Fixed issues

The complete list of aggregate functions you can use is:

Function	Purpose
Count	This will count the number of child records within each parent record that match the selection filters
Sum	This will sum the total of the numeric value of the child records within each parent record that match the selection filters
Min	This will show the minimum numeric value of the child records within each parent record that match the selection filters
Max	This will show the maximum numeric value of the child records within each parent record that match the selection filters
Mean	This will show the mean of the numeric values of the child records within each parent record that match the selection filters