[{"id":"a20f26ac-cd31-4e4d-9454-1e035b0f1587","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"3604b8ce-1da0-413f-8274-7df0f7173573","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"7fce7040-1bb1-4a61-9fb7-2e19d569de50","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"0eb72dd3-8f8f-4039-93a8-500bbde2301e","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"ac046cee-82b9-4edf-a642-ae6d7cb21ebf","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"75b66fd4-b740-443d-bc6e-1782048b60a6","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"dde44832-bb5c-45f3-8ebf-3badbc613cd9","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"43de3664-261b-4cf9-9945-fb519251f4c7","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"2f064d57-b088-4bc5-a628-4e0d7e1a0c65","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"f290406b-ced1-4c52-abad-979160a79f6b","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"1864834b-08ce-43f1-891b-c3cca6e04e95","tags":[{"product":null,"links":null,"id":"4d7b6a40-ab32-4c71-a381-58f3ffd2653e","name":"new","color":"#ed7422","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"f96cfec1-420c-4ca7-ab0a-cab482731073","tags":[{"product":null,"links":null,"id":"4d7b6a40-ab32-4c71-a381-58f3ffd2653e","name":"new","color":"#ed7422","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"3f2ea860-c764-43d8-ae3f-6e3b0314ce67","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"81eb70e8-f770-4384-9167-bc7c755ad052","tags":[{"product":null,"links":null,"id":"4d7b6a40-ab32-4c71-a381-58f3ffd2653e","name":"new","color":"#ed7422","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"d0dff477-9f56-483a-a9fc-cab648905b41","tags":[{"product":null,"links":null,"id":"4d7b6a40-ab32-4c71-a381-58f3ffd2653e","name":"new","color":"#ed7422","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"793a62d7-460d-4699-9304-edb2941268ac","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"2d6ebfad-f7d1-4cc5-8ba1-fdace3472056","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"c8c7b5d5-f5bd-4b68-a12f-3aae9804a844","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"577a09da-dea1-42db-812f-4eeaa9274ece","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"8ddd80d5-886f-41b8-8e3f-f73aa8146c8d","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"e0909540-8bde-4587-bb4d-02445746db2a","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"464b8e03-462d-4aed-8818-05d32ec3bf00","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"f235557b-9781-471f-bf03-38bae6e3e953","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"fd5d021d-0210-4d74-ba42-1a39ef14a385","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"548568fe-dbd1-4da5-a489-d9b3edf75329","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"a0c823ed-e22b-493b-aded-4d201c0fa480","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"d19bc9cd-ff53-4b0d-ab7e-a68c1497c4c6","tags":[{"product":null,"links":null,"id":"4d7b6a40-ab32-4c71-a381-58f3ffd2653e","name":"new","color":"#ed7422","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"94759fce-692f-4779-b5f3-fce5c58f242c","tags":[{"product":null,"links":null,"id":"4d7b6a40-ab32-4c71-a381-58f3ffd2653e","name":"new","color":"#ed7422","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"27658163-a479-432f-a192-dbed286beb3e","tags":[{"product":null,"links":null,"id":"4d7b6a40-ab32-4c71-a381-58f3ffd2653e","name":"new","color":"#ed7422","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]}]
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.
The following content assumes that you have the Standalone Report Designer
running. Visit the tutorial for detailed information.
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 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.
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
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
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
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
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 |
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
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
.
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
.
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.