Get Started With Hierarchical Drill Down Reports

Introduction

Hierarchical Drill-Down Reports allow readers to progressively expand branches of a hierarchical tree to drill down into more granular details. Examples of such reports are an organizational hierarchy and a chain store hierarchy.

This tutorial builds the Chain Store Report from the ground up, providing experience with the typical report designing process. By the end, you will be able to do the following:

Create a new report
Bind the report to an embedded JSON data
Create a Table data region and bind it to the data
Configure a hierarchical grouping for the Table
Use recursive summary functions
Use the Level function

Upon completing all tutorial steps, the final report will look like this: live demo.

Prerequisites

The following content assumes that you have the Standalone Report Designer running. Visit the tutorial for detailed information.

Creating a New Report

In the standalone report designer, click the File menu and select the Continuous Page Layout template for a newly created report. Click anywhere on the gray area around the report layout to load the report properties in the properties panel. In the Margins section, set the Style property to Narrow to change the report page margins size.

Expand to watch creating a new report in action

Data Binding

Data Binding establishes a connection between a report and the data it displays. For this demo, we use the JSON file that contains the data describing the store chain of a fictitious company called Contoso that is a manufacturing, sales, and support organization with more than 100,000 products. You can download the file into a folder of your choice on your machine.

Adding a Data Source

Using the following steps, you can create a Report Data Source that establishes the connection to the aforementioned JSON file.

Open the Data panel of the property inspector and click the Add button.
In the Data Source editor dialog, type Contoso in the NAME field.
Select the Embedded JSON for the Data Provider.
Click the Load From File section and select the downloaded ContosoStores.json file.
Click the Save Changes button

Expand to watch adding a data source in action

Adding a Data Set

For static JSON data, a Data Set identifies a JSON path that extracts the array of data objects. In a simple scenario, as in the data this article uses, the data itself is an array of objects.

Use the following steps to add the Stores data set:

Click the + icon near Contoso in the Data panel.
In the Data Set Editor dialog, type Stores in the NAME field, and $.* in the Json Path field.
Click the Validate button.
Ensure that the DataBase Fields section displays the [5 items] text.

Expand to watch adding a dataset in action

Adding a Table Data Region

A Table is a data region containing a ton of features, and it allows building a hierarchical drill-down as well. There are several ways to add a new table into the report. We will use the quick way for this tutorial:

Expand the Stores data set in the Data panel using the chevron-down icon.
Click the Select fields icon on the right side of the Stores data set name.
Select the Name, the EmployeeCount, and the SellingAreaSize fields.
Drag and Drop selected fields into a top-left corner of a report layout.

Expand to watch adding a table in action

Configuring Hierarchical Drill-Down

Each record in the Stores dataset has the Id and the ParentId fields that establish the hierarchical relation between parent and child entities. Follow the next steps to configure the newly added table so that it renders in a hierarchical drill-down manner.

Click anywhere inside the table so that it displays the group editor.
Click the <Detail Group> item within the group editor.
In the properties panel set the Name property to GroupById.
Click the + sign on the right side of the Group Expression property and choose the Id field in the drop-down list on the newly added item.
Expand the Parent property drop-down and select the ParentId field.
Set the Hidden flag in the Visibility section.
Expand the Toggle Item drop-down and pick the TextBox4 item.

Expand to watch configuring hierarchical drill-down in action

Formatting Table Columns

The table we are building displays the Store/City/Region/Country Name in the first column, the Total Employee Count in the second column, and the Total Selling Area Size in the third column. Based on this information, let's configure columns properties:

Click anywhere inside the table to display the columns and rows handlers.
Click the first column handler and set its Width to 3.5in in the Properties panel.
Switch to the TEXTBOX tab in the Properties panel. You can now modify the properties of all the table cells of a selected column.
Set the Text Align property to Left.
Set the Vertical Align property to Middle.
Expand the Padding editor using the icon on its right side and set the Left Padding property to 16pt.

Expand to watch formatting columns in action

In the same manner, set the following properties for the 2nd and 3rd column

Property	Value
Column Width	2in
Text Align	Right
Vertical Align	Middle
Right Padding	16pt

Configuring Column Headers

We use the table header row to display headers for displayed fields:

Click anywhere inside the table to display the columns and rows handlers.
Click the 2nd cell of the first table row and type Total Employees.
Click the 3rd cell of the first table row and type Total Selling Area(ft2).
Click the first row handler and set its Height to 0.5in in the Properties panel
Switch to the TEXTBOX tab in the Property inspector. You can now modify the properties of all the cells of the selected row.
Set the Background Color property to #f8f8f8.
Expand the Border Style editor using the icon on its right side and set the Bottom Border Style properties to Solid.
Set the Border Color property to #e6e6e6.
Set the Text Color property to #3da7a8, and the Font Weight to Bold

Configuring Table Detail Row

The Detail Row of the table repeats for each store with an unique Id. We configure it so that it displays hierarchical drill-down properly:

Click anywhere inside the table to display the columns and rows handlers.
Click the second row handler and set its Height to 0.4in in the Properties panel.
Switch to the TEXTBOX tab in the Property inspector. You can now modify the properties of all the cells of the selected row.
Expand the Border Style editor using the icon on its right side and set the Bottom Border Style properties to Solid.
Set the Border Color property to #e6e6e6.
Click the first cell of the second table row, expand the Padding editor using the icon on its right side and set the Left Padding.property to {Level() * 12 + 12}pt. The Level function returns the index in the drill-down hierarchy.
Click the second cell of the second table row, and set the Value property to {Sum(EmployeeCount, "GroupById", "Recursive")} and the Format property to n0.
Click the third cell of the second table row, and set the Value property to {Sum(SellingAreaSize, "GroupById", "Recursive")} the Format property to n0.

Adding the Table Header

We now add one more table header row to display a heading text.

Click anywhere inside the table to display the columns and rows handlers
Right-click the first row handler and select the Row > Insert Row Above item in the drop-down menu
Click the handler of the newly selected row and set its Height property to 0.8in in the Properties panel.
Switch to the TEXTBOX tab in the Properties panel.
Set the Text Color property to #3da7a8, Font Size to 22pt, Font Weight to Bold, and the Vertical Align to Middle
Expand the Padding editor using the icon on its right side and set the Left Padding property to 16pt
Click the first cell of the first row, press the Ctrl button on Windows or Cmd on Mac OS, and click the 2nd and 3rd cells on the same row.
Right-click on the selection and select the Cells > Merge Cells item in the drop-down menu. The row now has a single cell that occupies three columns.
Double-click the merged cell and type Stores Report.

Preview the report

You can now preview the report output and expand collapse product entities using the + icon on the left side of an entity name. Note that summary values of parent nodes include values of child nodes.

The final version of the report is available at the demo web-site.