Get Started with Master-Detail Reports for Nested DataSets
A master-detail report is a report that displays data structures that are related to each other by hierarchical relationships. For example, you can use such a report to visualize a list of orders where each order contains the header(master data) and line items(detail data). ActiveReportsJS offers multiple ways to create master-detail reports. This tutorial will show you how to create a master-detail report from a single hierarchical data set that contains objects like the following:
{
"Name": "Long-Sleeve Logo Jersey, L",
"ProductNumber": "LJ-0192-L",
"Color": "Multi",
"Size": "L",
"ProductListPriceHistories": [
{
"StartDate": "2011-05-31T00:00:00Z",
"EndDate": "2012-05-29",
"ListPrice": 48.0673,
},
{
"StartDate": "2012-05-30T00:00:00Z",
"EndDate": "2013-05-29",
"ListPrice": 48.0673,
},
{
"StartDate": "2013-05-30T00:00:00Z",
"EndDate": null,
"ListPrice": 49.99,
}
]
}
This tutorial builds the Product Inventory
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 the OData API
Use the OData $select and $filter query options to reduce the volume of retrieved data
Use the OData $expand query option to retrieve hierarchical data
Use field tags to define the data type of a field.
Create a Table data region to display the
Master
data.Format rows, columns, and cells of the table.
Add a nested List data region to display the
Detail
data.Use the Format function to display formatted data.
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. A stable internet connection is also required to access the Demo Data Hub.
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 GrapeCity Demo OData API that contains the data for a fictitious company called AdventureWorks
that manufactures and sells bicycles worldwide.
Adding Data Source
You can think of the AdventureWorks OData API as the "database" that you can connect to using its root URL https://demodata.grapecity.com/adventureworks/odata/v1
, and that has multiple "tables" which presented with endpoints such as /Customers, /Stores, and /Products. Using the following steps, you can create a Report DataSource that establishes the connection to such a "database."
Open the Data panel of the property inspector and click the
Add
button.In the Data Source editor dialog, type
AdventureWorks
in theNAME
field andhttps://demodata.grapecity.com/adventureworks/odata/v1
in theENDPOINT
field.Click the
Save Changes
button.
Expand to watch adding the data source in action
Adding Data Set
Data Sets represent one or more endpoints of the Data Source. For the Product Inventory
Report, we need to access the data from the /Products and /ProductListPriceHistories endpoints of the AdventureWorks
Data Source that we added in the previous section. These end-points are related by the ProductId
field, and we can use this relationship to select the hierarchical data with the single query.
Use the following steps to add the Products
data set:
Click the
+
icon nearAdventureWorks
in the Data panel.In the Data Set Editor dialog, type
Products
in the NAME field,/Products
in the Uri/Path field, and$.value.*
in theJson Path
field.By default, each
Product
record includes 25 fields, but we don't need them all. Therefore we limit the retrieved fields by applying the OData $select query option via a query parameter.Click the
+
icon on the right side of theParameters
section.Type
$select
in theParameter
field of the newly addedParameter
item.Type
ProductNumber,Name,Color,Size
in theValue
field of the sameParameter
item.
By applying the $filter query option, we can further reduce the volume of data retrieved.
Click the
+
icon on the right side of theParameters
section.Type
$filter
in theParameter
field of the newly addedParameter
item.Type
ProductId+gt+714+and+ProductSubCategoryId+eq+21
in theValue
field of the sameParameter
item.
By applying the $expand query option, we request the hierarchical data where each product data contains its price history.
Click the
+
icon on the right side of theParameters
section.Type
$expand
in theParameter
field of the newly addedParameter
item.Type
ProductListPriceHistories
in theValue
field of the sameParameter
item.
Click the
Validate
button.Ensure that the
DataBase Fields
section displays[5 items]
text.Ensure that the
Nested DataSets
section displays theProductListPriceHistories
text.Click the
ProductListPriceHistories
text to switch to the properties of the nested dataset.Expand the
DataBase Fields
section and set theDataField
property of theStartDate
and theEndDate
fields toStartDate[Date]
andEndDate[Date]
, respectively.Click the
Save Changes
button.
The created dataset returns the list of products with the price history for each product. You can see the data set tree reflects this structure.
Expand to watch adding the data set in action(partially)
Adding Table Data Region
To display the product information(master data) we will use the Table Data Region. There are several ways to add a new table into the report. We will use the quick way for this tutorial:
Expand the
Products
data set in the Data panel using the chevron-down icon.Click the
Select fields
icon near theProducts
data set name.Select
ProductNumber
,Name
,Color
, andSize
fields.Drag and Drop selected fields into a top left corner of a report layout.
Expand to watch adding the table in action
The newly created table has four columns, one per each field of the dataset, the header row that displays the field names, and the details row that shows the values of the fields.
Formatting Table Columns
All the columns of the newly created table have the same width, and it needs to be adjusted according to the data displayed in each column. To do this, click anywhere inside the table so that it shows the columns and rows handlers and follow the next steps:
Click the first column handler and set its
Width
to2.5in
in the Properties panelClick the second column handler and set its
Width
to2in
in the Properties panelClick the third column handler and set its
Width
to1in
in the Properties panelClick the fourth column handler and set its
Width
to2in
in the Properties panel
Expand to watch setting column widths in action
Adding Table Rows
The table currently has only two rows. The following steps add one more row for the table header to show the report title and two more rows for the table details to display the product price history.
Right-click the first row handler and select the
Row > Insert Row Above
item the drop-down menuIn the same manner, insert two rows below the last row.
Expand to watch adding rows below the details in action
Merging Table Cells
The first row of the table will display the report title. Therefore, it is convenient to merge the cells of the first row:
Click the first cell of the first row, press the
Ctrl
button on Windows orCmd
on Mac OS, and click the 2nd, 3rd, and 4th cells on the same rowRight 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 four columns
Repeat the same procedure for the last two rows of the table - they will host the title and the nested List for the detail data.
Expand to watch merging cells of the first row in action
Formatting the Report Title
The first row of the table should display the report title. Follow the next steps to format the row accordingly.
Click the handler of the first row and set its
Height
property to0.5in
in the Property InspectorSwitch to the
TextBox
tab in the Property inspector and set the following properties for the text of the report title
Property | Value |
---|---|
Value | Product Inventory |
Color | #3da7a8 |
Font Size | 24pt |
Font Weight | Bold |
Vertical Align | Middle |
Expand to watch formatting the title in action
Formatting Column Headers
The second row of the table contains the column headers. Using the same steps as for the report title, set the following properties for the row height and the text displayed in the row's cells.
Property | Value |
---|---|
Row Height | 0.5in |
Border Width | 2pt |
Border Style | None None None Solid |
Bolder Color | #40cddf |
Font Weight | Bold |
Vertical Align | Middle |
Formatting Master Data
The third row of the table repeats for each data record of the Products
dataset. Set the following properties for the row height and the text displayed in the cells of the row.
Property | Value |
---|---|
Row Height | 0.4in |
Border Width | 1pt |
Border Style | None Dotted None None |
Border Color | #ccc |
Vertical Align | Middle |
Formatting Detail Data Title
The 4th row of the table should display the title of the nested List for the detail data. Set the following properties for the row height and the text displayed in the cells of the row.
Property | Value |
---|---|
Row Height | 0.4in |
Value | Price History |
Font Size | 12pt |
Font Weight | Bold |
Vertical Align | Middle |
Adding nested data region
The last row of the table should display the price history for the current product. To do that, add the nested List to the row using the following steps:
Click the handler of the last row and set its
Height
property to0.35in
in the Property InspectorExpand the toolbox using the
Hamburger
menu located on the toolbar's left sideDrag and drop the List item from the toolbox into the last row of the table
In the Property Inspector of the newly added List set the
DataSet Name
property toProductListPriceHistories
Expand to watch adding the nested list in action
Displaying the detail data
Drag and drop two TextBox items from the toolbox into the List
and set the following properties for newly added textboxes:
Property | 1st textbox | 2nd textbox |
---|---|---|
Value | {Format(StartDate, "d")} - {IIF(EndDate, Format(EndDate, "d"), Format(Now(), "d"))} | {Format(ListPrice, "c2")} |
Color | #0096aa | #0096aa |
Text Align | Leave as Default | Right |
Left | 0 | 6.25in |
Top | 0 | 0 |
Width | 3.25in | 1.25in |
Height | 0.35in | 0.35in |
Preview the report output
You can now preview the report output and notice that the following information is displayed for each product:
Product Name, Number, Size, and Color(Master data)
Product Price History(Detail data)
The final version of the report is available at the demo website.
Feedback
Submit and view feedback for