To run a business today, you need to have access to your order, inventory, and financial data wherever you find yourself working. To support that, on-screen reports must be not only flexible and interactive but also available in a variety of screen resolutions.
With ActiveReportsJS, you can build responsive web applications with highly interactive reporting capabilities that work in any browser, on desktops, and mobile devices.
In this article, we’ll show you how to build a responsive app for order tracking reports with Node.js and ActiveReportsJS. This app will let you drill-down from tables or charts of month-to-month order volumes to individual order and product details.
Building a Web-Based Sales Report
Let’s say a client named ACME Inc. contracted you to build a sales report. The client’s request comes with an attachment, which is a mockup of the real report, and it looks like this:
To create enterprise-level reports like the one above, GrapeCity offers ActiveReportsJS Designer, an intuitive tool that helps you configure your report to fetch data and render it using multiple visual element types, including tables, bar charts, pie charts, and others.
ActiveReportsJS Designer stores report definitions in a special JSON format and saves them in files with the .rdlx-json extension.
Install ActiveReportsJS with the Windows, macOS, or Linux installer.
Design the Report Title
To create a new report, in the Designer, open the File menu. Select the RDL option (standard language for data-driven reports), then save the new report as MonthlyOrders.rdlx-json.
The report title consists of a container that holds a logo image and a label. Notice the control toolbox on the left-hand side.
You can drag report controls from the toolbox and drop them onto the report canvas. To create a title with an image and a heading, you’ll need a container to hold these controls. Containers are especially useful for grouping controls that you want to move together.
Drag a container control and drop it onto the top of the report.
Drag an image control and drop it onto the container. The image can be pulled from an external source, such as a URL or a database, or be embedded.
Add a textbox to the container to display the title text. Customize the title font, size, and color as you see fit.
The title should look like this:
Click the Explorer icon on the toolbar.
From the control tree that opens, select the Report node. The properties panel opens at the right-hand side. On the properties tab, change the report margin sizes to 1 cm.
Create a title for the report.
Container
Background Color: #f1f1f1
Items:
Image
Source: #551e5f
#551e5f: FitProportional
Textbox
Value: "Monthly Orders"
You can use Explorer to select controls and view their properties:
Define Data Access in Node.js
ActiveReportsJS Designer manages data through two types of entities: data sources and data sets.
A data source works as a connection to an external data storage, like a URI or a JSON file, or a JSON document that can be embedded in the report itself.
A data set is an intermediate element that fetches data from the data source and binds it to fields in the report data model. You can also define calculated fields, which use expressions to transform the source data. The data set fields then can be used by the report components.
Your report will access data through an external API located at an HTTP service, which will return results in JSON format. The report will show a variety of order information based on “live” data from that service.
To define access to customer data in your report, start by selecting the Data Sources tab.
On the tab, click Add.
In the New Data Source dialog that opens, enter “Customers” in the Name field.
To define the Customers data source, in the Content URI field, enter the URI of some HTTP Rest service endpoint that returns a collection of customers in JSON format:
https://[some-api-service]/Customers
Note: that this data source is just a connection configuration, and no request has been made yet.
Click the plus sign to add a new data set using the Customers data source connection:
The Edit Data Set dialog opens. Name the new data set “Customers.”
Modify the Query field value to read:
$.[*]
This field is a JsonPath expression required to infer the report fields from the JSON-formatted data coming from the URI you have defined.
Click the Validate button and expand the Bound Fields section to see the report fields created from the data set:
Note that those fields were created automatically from the data source’s JSON result.
Now create a new Products data source and the corresponding data set. Use the process described for the Customers data access above. In the Content URI field, enter the URI of an endpoint that returns a collection of products, for example:
https://[some-api-service]/Products
Create the Orders data source and the corresponding data set using the above process with the following field values.
For the Content URI, use the URL of some HTTP Rest service endpoint that returns a collection of orders along with their order items:
https://[some-api-service]/Orders
For the Query, use:
$.value[*]
Using Calculated Fields
Occasionally, you need to transform one or more source fields into a new calculated field to be used in the report. ActiveReportsJS offers an expression language with a rich set of built-in functions to meet this need.
In this example, let’s transform the orderDate field into a “month/year” format. To achieve that, you need to add custom field to the list of automatically bound fields:
Expand the Calculated Fields section.
Add the MonthYear field.
Enter the following formula as a value of the MonthYear field:
=DateTime.Parse(Fields!orderDate.Value).ToString("MM/yyyy")
Create a new OrderDetails data set based on the existing Orders data source, but with the Query field value:
$.value.[*].orderDetails[*]
Note that the OrderDetails data set does not include a field for a total value per item. This value is likely to be required more than once in your report. Therefore, you’ll either have to calculate this value every time it’s needed, or create a new calculated field. The latter option is way more efficient.
Expand the Calculated Fields section.
Add the Subtotal field.
Enter the following formula as the Subtotal field’s value:
=Round(100 * (Fields!unitPrice.Value - (Fields!unitPrice.Value*Fields!discount.Value))*Fields!quantity.Value) / 100.0
Adding a Chart
To graphically present the retrieved sales data to the user:
Add a Chart Control at the top of the report, right below the title:
Modify the chart to use data coming from the OrderDetails data set. Once inserted, the chart will appear on the Explorer tab as a tree of elements that are easy to configure:
Define the chart properties as follows:
Element | Relevant Values |
Chart | Data Set Name: “OrderDetails” |
Header | Title: “Sales Volume by Month” |
X Axis | Title: “Month” |
Y Axis | Title: “Volume ($)” Major Interval: 20,000 Min: 0 Max: 150,000 |
Plot | Field Value: “=Fields!Subtotal.Value” Aggregate: Sum Category: “=Lookup(Fields!orderId.Value, Fields!orderId.Value, Fields!MonthYear.Value, “Orders”)” Category Sort Expression: “=Fields!orderDate.Value” Label Text Template: “=Value Field Value” Tooltip Template: “=Value Field Value” |
The chart consists of a set of bars representing the sales volume per month. The Y axis values are calculated based on the Subtotal field of the OrderDetails data set. The X axis runs from January to December, month by month. The chart categories are sorted by the value of the orderDate field.
Click Preview to display the chart with the OrderDetails data.
Add a Table
In addition to the chart, let’s create a tabular view of the month-by-month order volumes. Let’s position the new table right below the chart.
By default, the new table is created with three columns and three rows. You have the tools to resize the table, add or remove rows and columns, as well as define multiple headers, footer rows, and so on.
Now configure the table as follows.
Element | Value |
Table | Name: “tableMonths” Data Set Name: “Orders” |
Then remove the header and footer rows, as they aren’t needed:
Then merge cells for the details row.
Name the merged cells textbox to txtMonth and set its value to an expression =MonthName(Month(Fields!orderDate.Value)) & “/” & Year(Fields!orderDate.Value).
Finally, you have to define the table grouping.
Set the first level of the group with the formula =Year(Fields!orderDate.Value) and the second level as =Month(Fields!orderDate.Value).
The new table displays the first level of data in the tabular section:
To add another level of data, modify tableMonths and add a new detail row.
Increase the height of the newly created row, then create a new table named tableOrders inside that row.
Configure the new table as follows:
Element | Value |
Table | Name: “tableOrders” Data Set Name: “Orders” |
Then remove the header and footer rows, as they aren’t needed:
Next, merge cells 1, 2, and 3 for the details row, and set the merged cell value to an expression =”Order ID: ” & [orderId].
Click Preview to see the results.
Notice that tableOrders is at the second level in your report: it details the orders placed within a particular month.
More Details
Now display the order totals. To show the total amount for each order, create a new table named tableOrderTotal inside tableOrders:
Create a new detail row below the current tableOrders row and insert the new table there.
Configure the new table as follows:
Element | Value |
Table | Name: “tableOrderTotal” Data Set Name: “OrderDetails” |
Again, remove the Details Row and the Footer Row, as they’re not needed.
Then merge only the center and right cells for the details row. Define the left cell formula as “Total:”. Configure the merged cell alignment to Left, and its format to Currency. Then define the merged cell formula as =SUM(Fields!Subtotal.Value).
Now, select the tableOrderTotal table in the Explorer and expand the Filters property:
Click Add Item and add new filter criteria with the following fields:
- Filter Expression: “=Fields!orderId.Value”
- Operator: “Equal”
- Value: “=Fields!orderId.Value”
As we can see, the new tableOrderTotals table is based on the OrderDetails data set. The total value is obtained by filtering the order details by the order ID of the tableOrder row, and by summing up the Subtotal field values.
Click Preview and check out the results.
Finally, let’s create a table that will represent order items.
Create a new detail row in tableOrders, then create the tableOrderDetails table inside that row. Configure the table as follows:
Element | Value |
Table | Name: “tableOrderDetails” Data Set Name: “OrderDetails” |
Header Row | Column labels: 1. “Item” |
Details Row | Cell 1 formula: “=Lookup([productId], [productId], [productName], “Products”)” Cell 2 formula: “=[quantity]” Cell 3 formula: “=[unitPrice]” Cell 4 formula: “=[discount]” Cell 5 formula: “=[unitPrice] – ([unitPrice][discount])” Cell 6 formula: “=[Subtotal]” |
Footer Row | Cell 1 formula: “TOTAL INVOICE VALUE:” Cell 2 formula: “=SUM(([unitPrice] – ([unitPrice][discount]))*[quantity])” |
Filter | Filter Expression: “=Fields!orderId.Value” Operator: Equal Value: “=Fields!orderId.Value” |
This table will have 6 columns. Add new columns, then define their header row labels as follows:
- “Item”
- “Qty”
- “Unit Price”
- “Discount”
- “Discounted Price”
- “Total Price”
For the row details, configure the cell formulas so that they display the order items:
- Cell 1 formula: “=Lookup([productId], [productId], [productName], “Products”)”
- Cell 2 formula: “=[quantity]”
- Cell 3 formula: “=[unitPrice]”
- Cell 4 formula: “=[discount]”
- Cell 5 formula: “=[unitPrice] – ([unitPrice]*[discount])”
- Cell 6 formula: “=[Subtotal]”
Now, configure the footer row so that it represents the order summary. Merge the two right-most cells, then define their formulas as follows:
- Cell 1 formula: “TOTAL INVOICE VALUE:”
- Cell 2 formula: “=SUM(([unitPrice] – ([unitPrice][discount]))[quantity])”
Finally, you must create a relationship between the OrderDetails table and the data coming from the Orders table. Select the tableOrderDetails table in the Explorer and expand the Filters property:
Click Add Item and add new filter criteria with the following fields:
- Filter Expression: “=Fields!orderId.Value”
- Operator: “Equal”
- Value: “=Fields!orderId.Value”
Click Preview to see the result.
Drill-Down
Now let’s add a drill-down feature, which will keep tableOrders and tableOrderDetails collapsed until the user expands them:
Rename the following labels:
Field (as shown in the report) | New Name |
“July 1996” | txtMonth |
“Order ID: 10248” | txtOrderId |
Select tableOrders, and change the Hidden and Toggle Item properties as follows:
Select tableOrderDetails and make it collapsible, too:
Now we can expand/collapse detail levels by clicking the “+/-” symbol:
We can first expand the month:
We can then expand the order:
Next, let’s add some more details to the order information:
Add four new rows above the existing header row, then enter the following formulas in the new cells:
- Contact Name: “=Lookup([customerId], [customerID], [contactName], “Customers”).ToUpper()”
- Order Date: “=DateTime.Parse(Fields!orderDate.Value).ToString(“MM/dd/yyyy”)”
Notice that we have used the Lookup function in several places. Similar to Lookup in Excel spreadsheets, this function allows you to create a simple query that navigates from one data set to another based on a relationship between two fields, and then retrieves the value of a third field.
Select a cell inside the header row of tableOrderDetails and insert four new rows above the header.
Create two new fields to contain the customer name and order date. For the customer name, we’ll use the Lookup function twice to navigate from the OrderDetails to the Customer data set, using the Orders data set as a springboard:
- Customer: “=Lookup(Lookup(Fields!orderId.Value, Fields!orderId.Value, Fields!customerId.Value, “Orders”), Fields!customerId.Value, Fields!companyName.Value, “Customers”)”
For the order date, we’ll navigate from the OrderDetails data set to the Orders data set:
- Order Date: “=DateTime.Parse(Lookup(Fields!orderId.Value, Fields!orderId.Value, Fields!orderDate.Value, “Orders”)).ToString(“dd/MM/yyyy”)”
Click Preview to see the customer name and order date:
Sorting Columns
Finally, let’s make the order details’ columns sortable.
Select each header cell and fill out the Sort Expression field under the User Sort section with the following formulas:
Column Header | Sort Expression |
Item | “=Lookup(Fields!productId.Value, Fields!productId.Value, Fields!productName.Value, “Products”)” |
Qty | “=Fields!quantity.Value” |
Unit Price | “=Fields!unitPrice.Value” |
Discount | “=Fields!discount.Value” |
Discounted Price | “=Fields!unitPrice.Value – (Fields!unitPrice.Value*Fields!discount.Value)” |
Total Price | “=Fields!Subtotal.Value” |
Click Preview and sort the order items by Quantity.
Sort the order items by Total Price.
Create Host and NodeJS Apps
Now, with the report defined, you can create a responsive web display forit — a display that will look good on both desktops and mobile devices. ActiveReportsJS offers this capability by default.
Your sample application will use Node.JS, a JavaScript runtime environment, to execute the web app on the server side.
Follow the steps necessary to create a web application in PureJS, a template engine that uses the ActiveReportsJS Viewer component:
Create a new OrdersReport folder for your application.
Install the Node.JS Express package by typing the following line in the command prompt:
npm install express
To Install the ActiveReportsJS modules, which are required to view and embed the monthly sales report on the client side of your web application, enter the following command:
npm install @grapecity/activereports
Create the index.html file with the following content:
ActiveReportsJS Viewer
Note that the above code uses ActiveReportsJS Viewer to display the report on your web page at the client side.
Create the web server logic by adding the server.js file with the following content:
const express = require('express'); //import Express.js module
const app = express();
const path = require('path');
app.use(express.static(path.join(__dirname)));
app.listen(8085);
Run the Node.js App
Now that you’ve embedded ActiveReportsJS Viewer in the client-side JavaScript code, you can run your web app to see some of its features in action:
node .server.js
Navigate tohttp://localhost:8085
in your desktop browser.
Your application starts running with ActiveReportsJS Viewer embedded in the index.html home page:
The bar chart at the top displays the order volume month to month.
Chart reporting is particularly useful for transforming large volumes of data into a visual map that immediately makes sense to the user by highlighting maximum and minimum values and offering visual clues about trends based on categories or periods.
The report features a tooltip that displays data for the bar you hover over.
Remember the Visibility and Toggle Item properties you have configured for some of the report elements? These properties come handy when you implement a drill-down to multiple data levels in your report.
In your app, the drill-down feature allows your user to navigate from a month to an order in that month, and then to items within that order. You can also sort columns based on the sorting formulas you have configured while designing the report. Drill-down and sorting are examples of the ActiveReportsJS interactive capabilities.
The ActiveReportsJS Viewer component is responsive: it is rendered appropriately on a variety of devices, in a wide range of screen sizes. You can use the developer tools available in your web browser to quickly emulate the various mobile devices, screen sizes, and page orientations.
Compare the views of the same report page on a desktop and on mobile emulators available from the Chrome and Safari browsers.
Creating a Monthly Sales Report
In this article, we provided a hands-on approach to creating a monthly sales report using GrapeCity’s ActiveReportsJS Designer. We explained how to aggregate information to draw a sales chart grouped by month, in addition to a drill-down, interactive tabular report that consolidates sales by month and order ID.
Start exploring ActiveReportsJS capabilities, including design of more complete and visually appealing reports, integrated with popular web frameworks.
To keep experimenting with the code we’ve created for this article, feel free to download or clone the repository.
As an exercise, we suggest that you expand on the drill-down idea to add a new level of data to your report.
For example, you can drill-down from a product to product details. You can also create a new report with a chart showing sales volumes per product category, and a tabular product listing with the corresponding sales.
This article was originally published on GrapeCity’s blog.
If you’re interested in developing expert technical content that performs, let’s have a conversation today.