[{"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 drill-down report enables switching from a comprehensive data view down to a more detailed one. It assumes a hierarchical structure of the data visualization. Thus, a report reader can expand a parent item in the hierarchy so that its child items appear. In ActiveReportsJS you can build such a report using Table or Tablix data regions.
In this tutorial, we build the Category Sales 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 GraphQL API
Create a table data region
Group the data within the table to create a hierarchical data visualization
Display summary fields for multiple levels of the data hierarchy
Setup drill-down navigation for the data hierarchy
Use the Galley mode
of the report viewer
Upon completion of 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. This tutorial uses the MESCIUS Demo Data GraphQL API, which replicates the well-known Northwind database. It contains the sales data for a fictitious company called Northwind Traders, which imports and exports specialty foods worldwide.
You can think of the MESCIUS Demo GraphQL API
as the "database" that you can connect to using its root URL https://demodata.mescius.io/northwind/graphql
, and that accepts GraphQL queries and responds with the JSON data. You can create a Report DataSource that establishes such a "database connection" using the following steps.
Open the Data panel of the property inspector and click the Add
button
In the Data Source editor dialog, type Northwind
in the NAME field and https://demodata.mescius.io/northwind/graphql
in the ENDPOINT field
Click the +
icon on the right side of the HTTP Headers
section
Type Content-Type
in the Header
field of the newly added Header
item
Type application/json
in the Value
field of the same Header
item
Click the Save Changes
button
A GraphQL Data Set represents the query to the Data Source. For the Category Sales report, we need a single "Sales" query to select order details, including the product and its category information. GraphQL UI displays the prettified version of such a query:
{
orderdetails {
product {
productId
productName
category {
categoryId
categoryName
}
}
quantity
unitPrice
discount
}
}
Use the following steps to add the Sales
data set:
Click the +
icon near Northwind
in the Data panel
In the Data Set Editor dialog, type Sales
in the NAME field
Select the HTTP POST
in the Method
drop-down
Type {"query":"{orderdetails{product{productId, productName, category{categoryId, categoryName}}, quantity, unitPrice,discount}}"}
in the Post body
Type $.data.orderdetails.*
in the Json Path
field
Click the Validate
button
Ensure that the DataBase Fields
section displays [7 items]
text
Click the +
icon on the right side of the Calculated Fields
section
Type salesAmount
in the Field Name
field of the newly added item
Type {quantity * unitPrice * (1-discount)}
in the Value
field of the same item
Click the Save Changes
button
Expand to watch adding the Sales data set in action
In this tutorial we use the Table Data Region to create the drill down report. There are several ways to add a new table into the report. We will use the regular way and adjust the default table configuration by adding a new column and removing the detail row.
Expand the toolbox using the Hamburger
menu located on the left side of the toolbar
Drag and drop the Table
item from the toolbox to the top-left corner of the report page area
Click anywhere inside the table so that it displays the columns and rows handlers
Right-click the last column handler and select the Column > Insert Column Right
item in the context menu
Right-click the second row handler and select the Delete
item in the context menu
Expand to watch adding the Table Data Region in action
The newly created table has four columns, the header and the footer rows.
The report that we are building should display the sales total per product category on the topmost data hierarchy level. A report reader should expand this topmost level to display the sales total per product within the given category. To achieve that we group the table by the product.category.categoryId
and then by product.productId
fields of the Sales
data set.
Click anywhere inside the table so that it displays the group editor
Expand the "Sales" data set in the Data panel using the chevron-down icon
Drag and drop the product.category.categoryId
field in the group editor
Drag and drop the product.productId
fields in the group editor
The table now has the header and footer rows for each group. We don't use group footers for the report; therefore, we can remove them:
Press the Ctrl
button on Windows or Cmd
on Mac OS and click the 4th and the 5th row handler of the table
Right-click the 4th or 5th row handler and select the Row > Delete Row
item in the context menu
Expand to watch grouping Table Data Region in action
The table we are building should display the Product Category Name in the first column, the Product Name in the second column, and the totals for quantity and sales amount in the third and 4th columns. Based on this information, let's set up columns properties:
Click anywhere inside the table to display the columns and rows handlers
Click the first column handler and set its Width
to 2in
in the Property inspector
Click the second column handler and set its Width
to 2.5in
in the Property inspector
Click the 3rd column handler, press the Ctrl
button on Windows or Cmd
on Mac OS, and click the 4th column handlers
Set the width of the selected columns to 1.5in
in the Property inspector
Expand to watch setting last columns widths in action
Switch to the TEXTBOX
tab in the Property inspector. You can now modify the properties of all the table cells of a selected column.
Click the first column handler
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
In the same manner, set the following properties for other columns cells:
Property | 2nd column | 3rd column | 4th column |
---|---|---|---|
Padding | Left = 8pt | Right = 8pt | Right = 16pt |
Vertical Align | Middle | Middle | Middle |
Text Align | Default(Left) | Right | Right |
Format | Default(Empty) | Default(Empty) | Currency |
Expand to watch formatting of the last column in action
We use the table header row to display headers for displayed fields:
Double click the first cell of the first table row and type Category
Double click the second cell of the first table row and type Product
Double click the third cell of the first table row and type Quantity Sold
Double clock the 4th cell of the first table row and type Sales Amount
Click the first row handler and set its Height
to 0.5in
in the Property inspector
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 #f7f7f7
Expand the Border Style
editor using the icon on its right side and set the Top Border Style
and 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
Note: this step and the next three ones are very similar to formatting columns in the previous step, so we don't show the animation here. You can review the one on the last step to watch how rows and columns formatting in action.
The table will generate one group header row, marked with the [1
icon, per each product category. We now format this row and add summary values for quantity and sales amount per category.
Click anywhere inside the table to display the columns and rows handlers
Click the second row handler
Switch to the ROW
tab in the Property inspector
Set the Height
property to 0.4in
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 Border Width
property to 0.25pt
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 field selector of the first cell of the second row and choose the {[product.category.categoryName]}
item in the drop-down menu
Double click the third cell of the second row and type {Sum(quantity)}
Double click the 4th cell of the second row and type {Sum(salesAmount)}
The table will generate one group header row, marked with the [2
icon, per each product within a given category. We now format this row and add summary values for quantity and sales amount per product.
Click anywhere inside the table to display the columns and rows handlers
Click the third row handler
Switch to the ROW
tab in the Property inspector
Set the Height
property to 0.4in
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 Border Width
property to 0.25pt
Expand the Border Style
editor using the icon on its right side and set the Bottom Border Style
properties to Dotted
Set the Border Color
property to #e6e6e6
Double click the first cell of the third row and remove the existing text
Click the field selector of the second cell of the third row and choose the {[product.productName]}
item in the drop-down menu
Double click the third cell of the third row and type {Sum(quantity)}
Double click the 4th cell of the third row and type {Sum(salesAmount)}
We will use the table footer to show the grand total values for quantity and sales amount across the table.
Click anywhere inside the table to display the columns and rows handlers
Click the 4th row handler
Switch to the ROW
tab in the Property inspector
Set the Height
property to 0.5in
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 #f7f7f7
Expand the Border Style
editor using the icon on its right side and set the Top Border Style
properties to Solid
Set the Border Color
property to #e6e6e6
Set the Font Weight
property to Bold
Double click the third cell of the 4th row and type {Sum(quantity)}
Double click the 4th cell of the 4th row and type {Sum(salesAmount)}
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 the drop-down menu
Click the handler of the newly selected row and set its Height
property to 0.8pt
in the Property Inspector
Switch to the TEXTBOX
tab in the Property inspector
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, 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
Double click the merged cell and type Sales By Category
Expand to watch cell merging and setting the table header text in action
The report we are building should display sales by category by default and allow the report reader to expand the category to explore sales by product within the given category. We could achieve such an outcome by setting the dynamic visibility of the Product grouping:
Click anywhere inside the table to display the group editor
Click the [2 Table1_productproductId1
in the list of groups
Set the Hidden
property to True
Select the TextBox4
in the Toggle Item
drop-down. TextBox4
displays the category name. As the result of this setup, the Product group instances will dynamically show and hide if a report reader clicks the expand or collapse icon that appears near each category.
Expand to watch setting drill down in action
You can now preview the report output and expand collapse product categories using the +
icon on the left side of a category name. You can click the rightmost button, called Galley Mode
, on the report viewer toolbar. This button switches the viewer mode to display the unpaginated output that is suitable for reading drill-down reports.
Expand to watch report preview in Galley Mode in action
The final version of report is available at the demo web-site.