How to Create a Report that Displays Hierarchical Flat Data

Environment

Product

Progress® Telerik® Reporting

Description

The requirement is to represent hierarchical data with an unknown number of hierarchical levels in a report recursively. The data is flat as if coming from a SQL Query. For example here is a sample JSON data:

[{
"ChildItem":"Grand Parent",
"ParentItem":null},
{
"ChildItem":"Parent 1",
"ParentItem":"Grand Parent"},
{
"ChildItem":"Parent 2",
"ParentItem":"Grand Parent"},
...,
{
"ChildItem":"Child 1",
"ParentItem":"Parent 1"},
{
"ChildItem":"Child 2",
"ParentItem":"Parent 1"},
...,
{
"ChildItem":"Child 5",
"ParentItem":"Parent 2"},
{
"ChildItem":"Child 6",
"ParentItem":"Parent 2"},
...
]

Solution

The general idea is the main report to reference itself in a SubReport item. The data of each subreport gets filtered based on the value of a Report Parameter. This way, the report structure will be reproduced hierarchically based on the data, and the bottom of the recursion will be hit when all the data is filtered out. The solution is demonstrted in the HierarchyFromFlatData.trdp demo report. Next I have outlined the main points when designing the report.

The report has a JsonDataSource assigned and displays the field ChildItem, and a SubReport item that references the same report document, i.e HierarchyFromFlatData.trdp. The Report has a Parameter with default value set to Null. The SubReport item passes as ReportSource->Parameters value the field ChildItem of the parent report. This value will filter the subreport data based on the parameter, hence will display only those items from the data source that have as ParentItem the current ChildItem in order to structure the correct hierarchy. Here is the Report Filter:

= IsNull(Fields.ParentItem, '1') = = IsNull(Parameters.Parent.Value, '1')

The purpose of the IsNull conditional function above is to allow the comparison when the current ParentItem doesn't have a parent (it is Null), like in the case of the first data item - see the JSON data. The constant '1' may be any valid constand value and should be the same in both Expressions.

The main Report displays all ChildItem fields from the records with ParentItem = Null as separate detail section instances, i.e. on the first level of the hierarchy. The SubReport item is displaced rightside compared to the TextBox with ChildItem to form the second level. It contains all the records which ParentItem is the ChildItem from the previous level, and so on.

See Also

How to Create a Report that Displays Hierarchical Nested Data