[{"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 tabular report is the most straightforward way to visualize your data with ActiveReportsJS. The most basic tabular report is organized in a multicolumn, multirow fashion, with an ordered set of fields in columns and each data record displayed in a row. In addition, a tabular report can group, sort, and filter the data based on pre-defined conditions or user input. Examples of tabular reports are inventory lists, product catalogs, activity reports, and many others.
In this tutorial, we build the Product List 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 REST API with multiple endpoints
Create a table data region to display the subset of the data
Apply formatting to the table columns
Display the general report information in the table header
Group the data within the table
Use the Lookup function to display the data from multiple data sets
Display column headers and summary fields in the group header and the group footer, respectively
Filter the data within the table
Apply conditional formatting to the table rows
Add interactive sorting for the table columns
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. In this tutorial, we use the MESCIUS Demo REST API that 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 Northwind REST API as the "database" that you can connect to using its root URL https://demodata.mescius.io/northwind/api/v1
, and that has multiple "tables" which presented with endpoints such as /Products, /Customers, and /Orders. You can create a Report DataSource that establishes the connection to such a "database" 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/api/v1
in the ENDPOINT field.
Click the Save Changes
button
Data Sets represent one or more endpoints of the Data Source. For the Product List report, we need to have access to the data from the /Products and /Categories endpoints of the Northwind Data Source that we added in the previous section. Use the following steps to add the Products
data set:
Click the +
icon near Northwind
in the Data panel.
In the Data Set Editor dialog, type Products
in the NAME field, /Products
in the Uri/Path field, and $.*
in the Json Path field.
Click the Validate
button.
Ensure that the DataBase Fields
section displays [10 items]
text.
Click the Save Changes
button
Expand to watch adding the Products data set in action
Next, add the Categories
data set with the same approach:
Click the +
icon near Northwind
in the Data panel.
In the Data Set Editor dialog, type Categories
in the NAME field, /Categories
in the Uri/Path field, and $.*
in the Json Path field.
Click the Validate
button.
Ensure that the DataBase Fields
section displays [3 items]
text.
Click the Save Changes
button
The simplest way to visualize the data in a tabular report is to use Table Data Region. There 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 productName
, unitPrice
, unitsInStock
, unitsOnOrder
, and reorderLevel
fields
Drag and Drop selected fields into a top left corner of a report layout
Expand to watch adding the Table Data Region in action
The newly created table has five columns, one per each selected field, the header row that displays the field names, and the detail row that shows the values of the fields. You can preview the report output and notice that detail rows display the selected field values for all the products from the data sets, and the table header repeats on each page and show field names.
Go back to the report design and click anywhere inside the table to display the columns and rows handlers and the group editor.
Click the first column's handler and set its width to 2.5in
in the Properties panel. Similarly, set the second column's width to 1.1in
and to 1.3in
for the rest of the columns. Thus, the table width will be equal to 7.5in
and fit the report's printable area defined by the report page size and margins.
Click the table cell that displays the {unitPrice}
text and set its Format
property to Currency
in the properties panel.
Expand to watch setting the last column's width and the ```{unitPrice}``` formatting in action
You can preview the report output and notice that product names in the first column fit into a single line and the unit price displays as a currency due to column formatting.
The default table header row shows the column headers. We will replace them with the report title and add column headers later.
Go back to the report design and click anywhere inside the table to display the columns and rows handlers.
Click the first row handler and set its Height
to 0.6in
in the Property inspector.
Switch to the TEXTBOX
tab in the Property inspector. You can now modify the properties of all the table cells of the selected row.
Click the green box on the right side of the Value
property and select Reset
. This action removes the default text of the table cells.
Set the Vertical Align
to Middle
, Text Color
to #3da7a8
, Font Size
to 22pt
in the Properties panel.
Double click the top-left cell of the table and type Product List
Expand to watch modifying the table header in action
You can preview the report output and notice that each page now displays the Product List
report title.
There are multiple product categories in the Northwind database - Beverages, Seafood, Meat, etc. Each record on the Products
dataset has the categoryId
field. Thus, we can group the products by their category. There are two ways to add a grouping into the table data region. We use the quick way for this tutorial:
Go back to the report design and expand the "Products" data set in the Data panel using the chevron-down icon
Click anywhere inside the table to display the Group editor.
Drag and drop the categoryId
field to the Group Editor's <New Group>
highlighted area.
Select the newly appeared Table1_categoryId
item in the group editor
Switch to the Properties
tab of the Property Inspector
Set the Page Break
property of the grouping to Between
so that each group starts on the new page
Expand to watch adding grouping in action
Notice that the table now has two additional rows - the group header and the group footer. The former displays the {categoryId}
value in its first column, the latter is empty for now, but we will use it later for summary values.
You can preview the report output and notice that it produces eight pages. Each page represents the product category, prints its Id in the beginning, and follows with its products.
Displaying the category Id is not very informative, so let's display the category name instead. The Products
data set does not contain the categoryName
field, but the Categories
data set does. We will use the Lookup function to resolve the category name by its Id:
Go back to the report design and click anywhere inside the table to display the columns and rows handlers.
Click the second row handler(group header) and set its Height
to 0.6in
in the Property inspector.
Switch to the TEXTBOX
tab in the Property inspector. You can now modify the properties of all the table cells of the selected row.
Click the green box on the right side of the Value
property and select Reset
. This action removes the default text of the table cells.
Set the Font Size
property to 16pt
, Font Weight
to Bold
, and Vertical Align
to Middle
Note: steps above are very similar to those we used for Displaying Report Title in the Table Header. You can check the animated guideline in that section.
Click the first table cell of the group header row. Press the Ctrl
(Windows) or Cmd
(Mac OS) key and click four following cells on the right side to apply multi-selection.
Right-click the selection and choose the Cells/Merge Cells
menu. See merging cells in a table for more information.
Double click the merged cell and type {Lookup(categoryId, categoryId, categoryName, "Categories")}
. This function takes the value of the first argument in the current scope - the categoryId
of the current group - finds the row with the same categoryId
value in the Categories
data set and returns the corresponding categoryName
value.
Expand to watch setting merging cells and using the Lookup function in action.
You can preview the report output and notice that each page now displays the category name, and it visually differs from product rows.
Previously, we removed the default table header, and currently, the table does not display the column headers. Now, we will use the additional group header row for column headers.
Go back to the report design and click anywhere inside the table to display the columns and rows handlers.
Right-click the second row handler(group header) and select the Row/Insert Row Below
in the context menu. The group header now has two rows.
Click the newly added row handler and set its Height
property 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 table 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
property to Solid
Set the Border Color
property to Gainsboro
Set the Text Color
property to #3da7a8
, the Font Weight
to Bold
, and the Vertical Align
to Middle
Double click the first cell of the newly added row, type Product Name
Double click the second cell of the newly added row, type Unit Price
, then click the Right Text Align
button on the toolbar
Double click the third cell of the newly added row, type Units In Stock
, then click the Right Text Align
button on the toolbar
Double click the 4th cell of the newly added row, type Units On Order
, then click the Right Text Align
button on the toolbar
Double click the 5th cell of the newly added row, type Reorder Point
, then click the Right Text Align
button on the toolbar
You can preview the report output and notice that each page now displays accurately aligned column headers.
Table Data Region supports a wide variety of field summaries. The product list report will display totals for Units In Stock
and Units On Order
fields in the group footer.
Go back to the report design and click anywhere inside the table so that it displays the columns and rows handlers
Click the last row's handler and set its Height
property 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 table 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 Top Border Style
property to Solid
Set the Border Color
property to Gainsboro
Set the Font Weight
property to Bold
, and the Vertical Align
property to Middle
Double click the first cell of the group footer row, type Total
Double click the third cell of the newly added row, type {Sum(unitsInStock)}
, then click the Right Text Align
button on the toolbar
Double click the 4th cell of the newly added row, type {Sum(unitsOnOrder)}
, then click the Right Text Align
button on the toolbar
You can preview the report output and notice that each group now displays totals of units in stock and units on order in the footer.
Some products are marked discontinued
and the Products
dataset has the corresponding flag for each Product record in the Northwind database. We will filter out the discontinued products so that the table would not display them.
Go back to the report design and click anywhere inside the table so that it displays the root handler
Click the root handler to load the table's properties into the property inspector
Click the +Add...
link in the Filters Editor
Click the +Add...
button and select discontinued
in the popup menu
Click the text area on the right side of the =
sign and type False
Expand to watch adding filters in action
You can preview the report output and notice that some products do not appear anymore.
In the Northwind database, product records have the reorderLevel
field that indicates the number of product units in stock required to replenish that particular product. This last column of the table that we built so far displays the value of the reorderLevel
for each product. We will now highlight the products that reached the reorder point with a background color that associates with warning
Go back to the report design and click anywhere inside the table so that it displays the columns and rows handlers
Click the fourth row's handler and set its Height
property to 0.4in
in the property inspector
Switch to the TEXTBOX
tab in the Property inspector. You can now modify the properties of all the table cells of the selected row.
Set the Background Color
property to {IIF(unitsInStock <= reorderLevel, "MistyRose", "Transparent")}
- this expression is using the IIF function to conditionally apply the background color to all the table cells of the selected row.
Set the Vertical Align
to Middle
You can preview the report output and notice that products that reached the reorder point are now highlighted with the MistyRose
color.
Table Data Region supports two types of data sorting:
Design-time sorting - the report author, decides on how the data should be sorted
Interactive sorting - the report readers determine how to sort the data at preview time
We will add the interactive sorting for all but Reorder Point
table columns that we built so far for the product list report.
Go back to the report design
Click the first cell of the third table row that displays the Product Name
text, find the User Sort
section in the Properties panel, and set the Sort Expression
to {productName}
using the selector on the right side of the editor.
Similarly, set the Sort Expression
to {unitPrice}
, {unitsInStock}
, {unitsOnOrder}
for the second, third, and fourth cells of the third table row, respectively.
Expand to watch adding the interactive sort for the Product Name column
You can preview the report output and notice that the column headers now display the Sort
icon. You can click this icon to sort the product by their name, unit price, amount of units in stock, and units on order. Sorting data within each group is independent of other groups.
The final version of report is available at the demo web-site.