[{"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"}]}]
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.
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.
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 MESCIUS Demo OData API that contains the data for a fictitious company called AdventureWorks
that manufactures and sells bicycles worldwide.
You can think of the AdventureWorks OData API as the "database" that you can connect to using its root URL https://demodata.mescius.io/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 the NAME
field and https://demodata.mescius.io/adventureworks/odata/v1
in the ENDPOINT
field.
Click the Save Changes
button.
Expand to watch adding the data source in action
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 near AdventureWorks
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 the Json 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 the Parameters
section.
Type $select
in the Parameter
field of the newly added Parameter
item.
Type ProductNumber,Name,Color,Size
in the Value
field of the same Parameter
item.
By applying the $filter query option, we can further reduce the volume of data retrieved.
Click the +
icon on the right side of the Parameters
section.
Type $filter
in the Parameter
field of the newly added Parameter
item.
Type ProductId+gt+714+and+ProductSubCategoryId+eq+21
in the Value
field of the same Parameter
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 the Parameters
section.
Type $expand
in the Parameter
field of the newly added Parameter
item.
Type ProductListPriceHistories
in the Value
field of the same Parameter
item.
Click the Validate
button.
Ensure that the DataBase Fields
section displays [5 items]
text.
Ensure that the Nested DataSets
section displays the ProductListPriceHistories
text.
Click the ProductListPriceHistories
text to switch to the properties of the nested dataset.
Expand the DataBase Fields
section and set the DataField
property of the StartDate
and the EndDate
fields to StartDate[Date]
and EndDate[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)
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 the Products
data set name.
Select ProductNumber
, Name
, Color
, and Size
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.
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
to 2.5in
in the Properties panel
Click the second column handler and set its Width
to 2in
in the Properties panel
Click the third column handler and set its Width
to 1in
in the Properties panel
Click the fourth column handler and set its Width
to 2in
in the Properties panel
Expand to watch setting column widths in action
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 menu
In the same manner, insert two rows below the last row.
Expand to watch adding rows below the details in action
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 or Cmd
on Mac OS, and click the 2nd, 3rd, and 4th 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 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
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 to 0.5in
in the Property Inspector
Switch 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
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 |
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 |
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 |
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 to 0.35in
in the Property Inspector
Expand the toolbox using the Hamburger
menu located on the toolbar's left side
Drag 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 to ProductListPriceHistories
Expand to watch adding the nested list in action
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 |
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.