Click here to Skip to main content
14,643,812 members
Articles » Third Party Products » Product Showcase » General
Article
Posted 11 Sep 2020

Stats

3.4K views
4 bookmarked

Build a Responsive Web-Based Inventory and Invoice App with ActiveReportsJS and Node.js

In this article, we provide a hands-on approach to creating a monthly sales report using GrapeCity's ActiveReportsJS Designer.
Here 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.

This article is in the Product Showcase section for our sponsors at CodeProject. These articles are intended to provide you with information on products and services that we consider useful and of value to developers.

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:

Node.js Web-Based Sales Report Output

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.

ActiveReportsJS Toolbox - Report Controls

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:

ActiveReportsJS Report Header

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:

ActiveReportsJS Designer Explorer

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.

Data Sources Tab

On the tab, click Add.

In the New Data Source dialog that opens, enter “Customers” in the Name field.

Add New Data Source

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:

Data Source Tab - Add Data Set

The Edit Data Set dialog opens. Name the new data set “Customers.”
Edit Data Set Dialog

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:

Data Set Bound Fields

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")

Data Set - Calculated Fields (MonthYear)

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

Data Set - Calculated Fields (Subtotal)

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:

Monthly Orders Report with Chart

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:

Report Explorer - Chart Elements

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.

Monthly Orders Report - Sales Volume By Month

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.

Table - Details Row

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:

Table - Delete Row

Then merge cells for the details row.
Table - Merge Cells

Name the merged cells textbox to txtMonth and set its value to an expression =MonthName(Month(Fields!orderDate.Value)) & “/” & Year(Fields!orderDate.Value).

TextBox Table Cell - txtMonth

Finally, you have to define the table grouping.

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).

Table Group Details

The new table displays the first level of data in the tabular section:

Preview of Table with Grouping

To add another level of data, modify tableMonths and add a new detail row.

Add Detail Row to Table

Increase the height of the newly created row, then create a new table named tableOrders inside that row.

Add Table to New Detail 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.

Preview of Table Grouping for Monthly Orders

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.

Table for Orders

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).

Table Header (Total)

Now, select the tableOrderTotal table in the Explorer and expand the Filters property:

Table Filters Property

Click Add Item and add new filter criteria with the following fields:

  1. Filter Expression: “=Fields!orderId.Value”
  2. Operator: “Equal”
  3. Value: “=Fields!orderId.Value”

Table Filter Properties

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.

Table Preview of Grouped Monthly Orders and Totals

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" 2. "Qty" 3. "Unit Price" 4. "Discount" 5. "Discounted Price" 6. "Total Price"
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:

  1. "Item"
  2. "Qty"
  3. "Unit Price"
  4. "Discount"
  5. "Discounted Price"
  6. "Total Price"

Table Header Row - Column Headers

For the row details, configure the cell formulas so that they display the order items:

  1. Cell 1 formula: "=Lookup([productId], [productId], [productName], "Products")"
  2. Cell 2 formula: "=[quantity]"
  3. Cell 3 formula: "=[unitPrice]"
  4. Cell 4 formula: "=[discount]"
  5. Cell 5 formula: "=[unitPrice] - ([unitPrice]*[discount])"
  6. Cell 6 formula: "=[Subtotal]"

Table Details Row - cell values

Now, configure the footer row so that it represents the order summary. Merge the two right-most cells, then define their formulas as follows:

  1. Cell 1 formula: "TOTAL INVOICE VALUE:"
  2. Cell 2 formula: "=SUM(([unitPrice] - ([unitPrice][discount]))[quantity])"

Table Footer Row - Total Invoice Value

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:

Filters for TableOrderDetails

Click Add Item and add new filter criteria with the following fields:

  1. Filter Expression: "=Fields!orderId.Value"
  2. Operator: "Equal"
  3. Value: "=Fields!orderId.Value"

Filter Properties for TableOrderDetails

Click Preview to see the result.

Preview Table with Groupd Monthly Orders, Order Details and Totals

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:

Visibility Properties of tableOrders Table

Select tableOrderDetails and make it collapsible, too:

Visibility Properties of tableOrderDetails Table

Now we can expand/collapse detail levels by clicking the “+/-” symbol:

Preview of Collapsed tableOrders

We can first expand the month:

Preview of Expanded Month Table

We can then expand the order:

Preview of Expanded Month and Order Table

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:

  1. Contact Name: "=Lookup([customerId], [customerID], [contactName], "Customers").ToUpper()"
  2. 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.

Add Rows to tableOrderDetails Table

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:

  1. 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:

  1. 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:

Preview of tableOrderDetails with Additional Detail Rows

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.

Table with Sorting

Sort the order items by Total Price.

Table Sort Items by 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:

<html>
<head>
    <meta charset="utf-8" />
    <title>ActiveReportsJS Viewer</title>
    <meta name="viewport" content="width=device-width, initial-scale=1">

    <link rel="stylesheet" href="node_modules/@grapecity/activereports/styles/ar-js-viewer.css" />
    <script type="text/javascript" src="node_modules/@grapecity/activereports/dist/ie-polyfills.js"></script> <!--to run in IE-->
    <script type="text/javascript" src="node_modules/@grapecity/activereports/dist/ar-js-core.js"></script>
    <script type="text/javascript" src="node_modules/@grapecity/activereports/dist/ar-js-viewer.js"></script>
</head>
<body onload="load()">
    <div id="ARJSviewerDiv" style="height: 600px"></div>
    <script>
        function load() {
            const viewer = new ActiveReports.Viewer('#ARJSviewerDiv');
            viewer.open('MonthlyOrders.rdlx-json');
        }
    </script>
</body>
</html>

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 to http://localhost:8085 in your desktop browser.

Your application starts running with ActiveReportsJS Viewer embedded in the index.html home page:

View Report in Web Application Hosted in Node.js

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.

Report Tooltip

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.

Drill Down Report with Table Sorting

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.

Mobile Preview of Sales Report in Browser

View Reponsive Sales Report on Mobile

Responsive and Interactive Mobile Report

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author


Comments and Discussions

 
-- There are no messages in this forum --