Getting Started
Developers Deployment Guide Get Help

Form.io Reporting Module

Introduction

What is the Form.io Reporting Module?

The Form.io Reporting module is a powerful tool that allows the user to design and generate reports based on the submission data of the Forms and Resources within a Project. Reports can be generated from one or multiple form sources and provide features such as form aggregation, field data calculations, sorting and filtering, and more.

How does it work?

The Reporting Module sits on top of the MongoDB Aggregation Pipeline providing the report designer with powerful reporting features and configurations. From the joining of submissions between different forms to powerful operators powered by MongoDB, the Reports can range from simple to sophisticated depending on the requirement. Reports are created and stored within the Project as a Resource and can be individually embedded into your application for end users to access and utilize. Report designers can configure the Report's UI, enabling the end users to interact with the report by arranging, filtering, and exporting data as needed.

Why should I use it?

The Reporting Module relieves developers of complex and time-consuming tasks that come with designing data aggregated reports. The Reporting UI empowers developers to streamline and customize data transformations effortlessly using the power of MongoDB. With quick embed capabilities, these reports can easily be integrated into an application quickly and efficiently. Here are some examples of how the Reporting Module can provide end users the flexibility of reporting across your entire database:

  • joining of submissions from multiple form sources;

  • Unique calculations derived from MongoDB operator system

  • grouping of submission data using operators and configurations criteria set by the Report designer

Reports allow the management of report data in several ways, which can all be configured by the developer creating the report:

  • sorting

  • filtering

  • addition/removal of columns in the end user UX

  • pagination

  • selection of data rows

  • Execution of actions on selected rows of data. Default Actions include Export to CSV and Download to PDF. Custom Actions can also be defined, for addition to any report.

Reports Availability

The reporting functionality is available for self-hosted environments containing a license with the Reporting Module enabled.

Contact sales@form.io to acquire a License

Once the reporting is enabled for your license key, you will see the Reports section in the navigation sidebar of your projects.

Reports Loading Error

This error typically occurs when accessing the Report page in Projects that were created before the Reporting Module was activated for the environment License.

In such instances, the Project lacks the essential Reporting UI configuration Resource necessary for the creation and management of Reports within the Project

To resolve this, Import the reporting UI configuration Resource to the project resources using the following embed URL and ensure the Resource is titled Reporting UI: {{ yourServerUrl }}/reportingui/config The resource name and path must be reportingui.

For newly created projects, the Reporting UI configuration resource is added by default.

The reports section enables the creation of reports and a preview mode that excludes the report data fetch. This is an intentional preview for the UI / UX of the report, while all external scripts and APIs are not executable from the context of the Portal Application.

To see the fully functioning report inclusive of Data Fetch, embed the Report into an Application as described below.

Report Embedding

The Reporting Module can easily be added to your application using terminal commands to install the necessary files. Once installed, individual Reports can be embedded into your application for end-users to view and utilize.

  1. Use one of the following terminal commands to install the @formio/reporting module into your application:

npm i --save @formio/reporting

or

yarn add @formio/reporting

  1. Connect the reporting module in your application:

import {Formio} from 'formiojs';
import reporting from '@formio/reporting';

Formio.use(reporting, {license: 'yourLibraryLicense'});

Please note that you need a library license with the reporting enabled to use the @formio/reporting module.

Contact sales@form.io to acquire a License

Once you connect @formio/reporting module in your app, the Report class is available in global Formio.

  1. Create a report. For the report embedding you can use the report URL or the report JSON schema.

  • embed report URL:

Copy the report URL from the report Embed tab and use it for report creation.

import {  Formio } from 'formiojs';

Formio.Report.create(element, 'reportUrl').then(report => {
 // some custom code if needed
})

  • embed report JSON:

import {  Formio } from 'formiojs';

Formio.Report.create(element, reportJSON, { projectEndpoint: 'projectURL' }).then(report => {
    // some custom code if needed
});

Report Structure

After embedding a Report within an application, it will include the following user interface elements:

  1. Reporting Grid. This is the main part of the report that displays the report data. It provides the ability to paginate and sort data as well as select rows. Additionally, when the Reporting Controls are disabled, it gives the ability to filter data by columns and control the visibility of columns.

  2. Reporting Controls. This part is responsible for the report data management, such as filtering, column visibility, and actions on the rows data of the Reporting Grid. Reporting Controls are optional and can be enabled/disabled in the report settings.

Report Creation

To create a report, go to the Reports section of your project and click the New Report button. You will see the report configuration wizard.

Basic Settings

  • Report Title - report title. This is a required field;

  • Report Name - the back end name for the report name. Name is required and must be unique among other project reports. The name must only contain alphanumeric characters, underscores, dots, and dashes and should not end with a dot or dash.

  • Items per page defines how many records to display on each page of the Reporting Grid;

  • Enable to Store Request Result in the Cache. When checked, the requests and their results will be stored in the cache. If the Report attempts to request the same URL with identical parameters, the cached data will be retrieved. This option can aid in increasing server performance. However, if the source's data is frequently changing and the data needs to maintain real-time updates, it is recommended to uncheck this option.

  • Cell max width sets the maximum width of the Reporting Grid cells.

Source Form(s)

  • Form(s) to create Report - project forms that are the data source for the report. You can select one or more forms/resources for your report. Please note that each selected form must have at least one field in common with one other selected form when more than one form aisre selected.

  • Forms Connections. This setting is available when the report has more than one source form - the multiform report. Here you must set connections between pairs of selected source forms by their common fields. If there are 3 source forms in the report, then 2 form connections must be set (1: form1-form2, 2: form1-form3 or form2-form3), for 5 forms - 4 connections, and so on.

    • Base Form - the source form that has a common field with the Joining Form;

    • Connecting Field of Base Form - the Field of the Base Form that can have the same values (common values) as the Connecting Field of the Joining Form has;

    • Base Form Path to Connecting Value - path to the common value in the Connecting Field Value of the Base Form;

    • Joining Form - the source form that has a common field with the Base Form;

    • Connecting Field of Joining Form - the Field (Component) of the Base Form that has the same values (common values) as the Connecting Field of the Base Form has;

    • Joining Form Path to Connecting Value - path to the common value in the Connecting Field Value of the Joining Form.

Example

Our report has two source forms:

  • Department Info that has 'Department Name' and 'Department Id' (key - departmentId) fields;

  • Employee with 'Department' field that is a select component. The 'Department' populates the options from the Department Info resource and saves the Department ID as a value.

Those forms are connected with each other by department ID. The connection setting will be the following:

If the Employee form saves the 'Department' field value as the entire submission object of the Department form, the connection setting will look the following:

Calculated Extra Columns

This tab allows the designer to create custom columns for the Report Grid that would otherwise be unavailable using the default data source form. These custom columns utilize operators from the MongoDB Pipeline to perform certain calculations between field data on the forms, creating entirely new data outputs for the Report.

  • Column Title - the title of the calculated column.

  • Column Key. The key must only contain alphanumeric characters, underscores, dots, and dashes. The Column Keys should not end with a dash or dot character.

  • Operators from the MongoDB Pipeline are used to calculate the column value. Follow the MongoDB link of the Operator for more information on how the operator works. For a numeric value, you can limit the number of decimal places in the calculated value. The following operators are available.:

    • Array Size counts and returns the total number of items in an array;

    • Concatenate Strings concatenates strings and returns the concatenated string;

    • Date Difference returns the difference between two dates;

    • Day of Month returns the day of the month for a date as a number between 1 and 31;

    • Day of Week returns the day of the week for a date as a number between 1 (Sunday) and 7 (Saturday);

    • Day of Year returns the day of the year for a date as a number between 1 and 366;

    • Month Number returns the month of a date as a number between 1 and 12;

    • Week Number returns the week of the year for a date as a number between 0 and 53;

    • Year returns the year portion of a date;

    • Divide divides one number by another and returns the result;

    • Summarize calculates and returns the collective sum of numeric values;

    • Multiply multiplies numbers together and returns the result.

Aggregation

Using this tab, you can create an aggregated report based on the MongoDB $group aggregation. This operator separates the source form submissions into groups according to the group field and criteria set by the Report Designer.

  • Fields to group by - the field(s) of the report source form or calculated extra column used as criteria for grouping.

  • Group Calculated Columns add columns with special calculations performed per group.

    • Column Title - the title of the group calculated column.

    • Column Key. The key must only contain alphanumeric characters, underscores, dots, and dashes. The Column Keys should not end with a dash or dot character.

    • Operator that is used to calculate the column value for the aggregation. Follow the MongoDB link of the Aggregation Operator for more information on how the operator works. The following aggregation operators are available:

      • Average returns the average value of the numeric values in a group;

      • Count returns the number of records in a group;

      • Max returns the maximum value in a group;

      • Min returns the minimum value in a group;

      • Sum computes the total amount of numeric values in a group

Columns

Using this tab, you can define which columns are available for display in the Reporting Grid, as well as which columns are displayed by default.

  • Available Columns set the columns that are available for display in the Reporting Grid and which visibility is managed from the controls panel (if enabled) or Reporting Grid UI. The columns appear in the Reporting Grid in the order they are added to the Available Columns data grid. Display Title option is available for not calculated columns. If a Display Title is not set, the selected column component title displays as a column title by default.

  • Default Columns define which Available Columns are displayed in the Reporting Grid by default. If not set, all Available Columns are displayed.

Reporting Controls

In this tab, you can enable the Reporting Controls UI and set the basic configuration for the Report.

Reporting Controls Basic Settings:

  • Control Panel Title sets the title of the Control Panel;

  • Controls Panel Theme sets Bootstrap theme for the controls panel.

  • Initially Collapsed. When the option is enabled, the controls panel is displayed as collapsed when the report is loaded.

Filters

This tab is available only when the Reporting Controls is enabled. Here you can add custom filters for the report data.

  • Number Of Filters Per Row sets the number of filters that will be displayed on the filter tab of the Reporting Controls on each row.

  • Filter Label Position set the position of the filter label for all filters. The Top, Left, Right options are available.

  • Control Panel Filters set custom filters for the Report data.

    • Field defines the source form component or calculated columns to filter by.

    • Filter Type sets the filter operator that is used to determine the values that match the filter. The filter type options depend on the value type of the field, e.g. for the field of number type it can be Less Than/Greater Then/Is Equal To/Is Empty and other.

    • Filter Title sets the filter label. The field component title is used by default.

Controls Actions

This tab is available only when the Reporting Controls is enabled. Here you can add actions that will be executed for the selected Reporting Grid rows.

  • Title - a title of the action that the user will see in the actions dropdown.

  • Type. The following action types are available:

  • Export CSV exports selected rows data as CSV. You can export data for only the visible columns in the reporting table, or for all available columns.

  • Download PDF exports selected rows data as PDF. You can download data for only the visible columns in the reporting table, or for all available columns. Each row's data is displayed on a separate page in the PDF document.

  • Custom action executes custom code for selected rows. You need to assign the promise to a promise variable. If the promise returns true, the Reporting Grid will be redrawn and the Report data reloaded after the action is executed.

Examples

Preconditions

The following examples will highlight the different features and settings of the Reporting UI and how to use them to generate custom reports for your application users. Ensure your environment has the necessary license to enable Reports.

Import Forms

To follow along with the guide, import the example Forms into a project using the following embed URLs.

  1. Import the Resource using the embed URL below into your project https://remote-dev.form.io/itzikzxpqvbvmic/department

  2. Import the Salary Resource using the embed URL below into your project https://remote-dev.form.io/itzikzxpqvbvmic/salary

    • Edit the Department select component and navigate to the Data tab.

    • Click the Resource dropdown and select the Department Resource.

    • Click the Value Property field and select Department

  3. Import the Employee form into your project https://remote-dev.form.io/itzikzxpqvbvmic/employee

    • Edit the Department select component and navigate to the Data tab.

    • Click the Resource dropdown and select the Department Resource.

    • Click the Value Property field and select Department

  4. Import the Employee Evaluation form into your Project https://remote-dev.form.io/itzikzxpqvbvmic/employeeevaluation

    • Edit the Department select component and navigate to the Data tab.

    • Click the Resource dropdown and select the Department Resource.

    • Click the Value Property field and select Department

Form and Resource Submissions

Reports rely on common field types and data to join together submissions between different forms. To simplify the process, these examples will utilize the Department field and Employee ID field within each form as the connecting field. To ensure there is continuity between submission data, follow the steps below

  1. Make several submissions against the Department Resource. (E.G. aaa111-IT, bbb222-Quality Assurance, ccc333-Sales, and ddd444-Customer Support)

  2. Make several submissions against the Salary Resource (E.G. IT - $75,000, Sales - $65,000, Customer Support - $45,000, QA - $55,000)

  3. Make several submissions against the Employee form

    • Ensure you have at least one employee for each department.

  4. Make several submissions against the Employee Evaluation form

    • Ensure you have an evaluation submission for each employee submitted against the Employee form.

    • Ensure the Employee ID and Department match the Employee submission record

Single Form Report

This example will use the Employee form submission data to generate a basic single-form report. The Calculated Column feature will be used to merge string data and count data rows inside an array.

Ensure you have followed the Precondition steps for the appropriate Forms, Resources, and submission data.

  1. Within the Project, click the Reports button from the left-hand navigation sidebar

  2. Click the New Reports

  3. Basic Settings tab - Give the Report a Title - Employee Overview

  1. tab - select Employee from the form dropdown list

  1. tab - Click the +Add Column button and set the of the column to Name

  2. Click the Operator dropdown and select Concatenate Strings Once an Operator has been selected, the options will become available

  3. Within the Arguments section, select First Name,

  4. Click the +Add Another button, and select Last Name

  1. Save the Calculation

  2. Click the +Add Column to add a new Calculated Column and set the Title of the column to Languages Spoken

  3. Click the Operator dropdown and select Array Size This calculation will count the number of languages (arrays) in the Language field

  4. Within the Arguments section, select Languages Spoken

  1. Click the tab and Select the following columns Employee ID, Name (calculated), Department, Start Date, Languages Spoken (calculated)

  2. Click the Control Panel tab and click Submit Form to save the Report.

  1. Log in to your application and View the report

    • Click the up and down arrows next to the column header to sort

    • Click the 3 dots to select column visibility and add filters to the column

Multi-Form Report

This example will Join the Employee form, Employee Evaluation form, and Salary Resource. The Report will use different data points between the forms and resources to generate a multi-form report.

Like the previous example, the Calculated Column feature will be used again to merge string data. We will also use two new operators for this report: Date Difference to calculate employee tenor Multiply to calculate employee salary

The Control Panel will be configured, enabling end users to execute operations such as downloading reports in CSV/PDF format, executing custom JS to the report table, applying filters, and more.

Report Creation

  1. Within the Project, click the Reports button from the left-hand navigation sidebar

  2. Click the New Reports

Basic Settings

  1. Give the Report a Title - Employee Evaluation

Selecting Source Forms

  1. Click the tab

  2. Select Employee, Employee Evaluation, and Salary from the form dropdown list In this example, there will be two Source forms that will provide the submission data for the Report.

Now that the Source forms have been selected, a connection needs to be made between the forms. This connection is made by mapping fields between the Base form and Joining form that share the same field type and data structure.

In this example, all three forms share a common field of Department as well as the same Department submission data. The common Department field and the underlying data will establish the link between the Employee, Employee Evaluation, and Salary forms.

Adding Form Connections

  1. Click the +Add Connection button

  2. Select Employee Application as the Base Form and Employee Evaluation as the Joining Form.

  3. Select Employee ID for the and dropdown

  4. Save the connection

  1. Click the +Add Connection button

  2. Select Employee as the Base Form and Salary as the Joining Form.

  3. Select Department for the and dropdown

  1. Save the connection

Configuring Calculated Columns

  1. Click the tab

  2. Click the +Add Column button and set the Title of the column to Name

  3. Click the Operator dropdown and select Concatenate Strings

  4. Within the Arguments section, select First Name and Last Name

  5. Click the +Add Column button and set the Title of the column to Position

  6. Click the Operator dropdown and select Concatenate Strings

  7. Within the Arguments section, select the Department field and Position field

  8. Add a '-' to the Department and Save the argument

  1. Click the +Add Column button to add a new Calculated Column called Tenure

  2. Click the Operator dropdown and select Date Difference This Operator will calculate the Starting date and End Date of the Employee submission (if End Date is empty, it will calculate based on the current date)

  3. Within the Arguments section, select the Start Date and End Date fields

  4. Select months for the

  5. Save the Argument

  1. Click the +Add Column button and set the Title of the column to Salary

  2. Click the Operator dropdown and select Multiply This Operator will multiply the base department Salary with the employee's incremental pay number to determine their salary.

  3. Within the Arguments section, select the Salary and Incremental Pay field

  4. Set the to 2

Setting Report Columns

  1. Click the tab

  2. Select the following fields Name, Position (calculated), Salary (calculated), Tenure, Evaluation Score, Status, Start Date, End Date

  3. Add some Default Columns that will display when the report is initially rendered

Enabling the Control Panel

  1. Click the tab

  2. Check the Enable Control Panel setting

Filters

  1. Click the tab

  2. Select Position (calculated) from the field dropdown

  3. Click the and select

  4. Add Department/Position for the

Actions

  1. Click the tab

  2. Click the +Add Action titled Export To CSV and select Export CSV type

  3. Save the Action

  4. Click the +Add Action titled Download PDF and select Download PDF type

  5. Save the Action

  6. Click the +Add Action titled Export To CSV and select Export CSV type

  7. Click the +Add Action titled Set Dismissed Status and select Custom Action type

  8. Add the following custom JS Code This code will change the status of the selected employee row to dismissed and add the current date to the End Date field. 

const formioInstances = [];
_.each(selectedRows, (row) => {
  formioInstances.push(new Formio(`${Formio.getProjectUrl()}/employee/submission/${row._id}`))
});

promise = Formio.Promise.all(_.map(formioInstances, inst => inst.loadSubmission()))
  .then(submissions => {
    const transformedSubmissions = _.map(submissions, subm => {
      _.set(subm, 'data.status', 'dismissed');
      _.set(subm, 'data.endDate', new Date().toISOString());
      return subm;
    });
    return Formio.Promise.all(_.map(formioInstances, (inst, ind) => inst.saveSubmission(transformedSubmissions[ind])));
  });

  1. Click Submit Form to save the Report

Report Review

Log in to your application to review the Report

Using Filters

Use the Columns tab to add or remove the available columns you see on the Reporting Grid

Click the Action tab to fire off the configured actions for the Report

Aggregated Report

In this example, we will generate an aggregation report that groups submissions from the Department, Salary, Employee, and Employee Evaluation forms based on common fields within each form. This type of aggregation utilizes the MongoDB $group operator and separates the source forms submissions into groups according to the selected group field.

Report Creation

  1. Create a New Report

  2. Give the report the title Department Overview

Source Forms

  1. Click the Source Forms tab and select the following Forms and Resources Department, Salary, Employee, and Employee Evaluation

  2. Make 3 Form Connections

    1. Base Form: Department Joining Form: Employee. Connecting field of Base Form: Department Name Connecting field of Joining Form: Department

    2. Base Form: Department Joining Form: Salary. Connecting field of Base Form: Department Name Connecting field of Joining Form: Department

    3. Base Form: Employee Joining Form: Employee Evaluation. Connecting field of Base Form: Employee ID Connecting field of Joining Form: Employee ID

Calculated Columns

  1. Click the tab

  2. Click the +Add Column button and set the Title to Total Salary

  3. Click the Operator dropdown and select Multiply

  4. Within the Arguments section, select Incremental Pay and Salary

  5. Set the Decimal Places to 2

Aggregation

  1. Click the tab

  2. Select Department Name from the dropdown This means all of our submissions will be grouped by the Department

  3. In the click +Add Column button Here we will add 3 Calculations based on the aggregation

  4. Set the Title of the column to Total Employees

    1. Click the Operator dropdown and select Count

    2. Save the Calculation

  5. Click the +Add Column button and set the Title to Average Salary

    1. Click the Operator dropdown and select Average

    2. Click the Argument dropdown and select Total Salary

    3. Set the Decimal Places to 2

    4. Save the Calculation

  6. Click the +Add Column button and set the Title to Average Evaluation Score

    1. Click the Operator dropdown and select Average

    2. Click the Argument dropdown and select Evaluation Score

    3. Set the Decimal Places to 2

    4. Save the Calculation

Columns

  1. Click the Columns tab

  2. Select the following calculations from the Column dropdown Department Name, Total Employees, Average Salary, Average Evaluation Score

Control Panel

  1. Click the Control Panel tab and check the Enable Control Panel setting

  2. Click the Control Panel tab and Submit the Report

  3. Add some Filters to the Control Panel

  1. Add some Actions

  2. Submit the Report

Report Review

Log in to your application to view the Report

Use a Filters

Perform an Action

Report Styles

Import the reporting module styles in your application:

import '@formio/reporting/reporting.css';

Data Persistence

Report settings such as filters, columns, and sorting are persistent and stored in local storage. If you want to reset settings (e.g., when the user leaves the page, logs out), you need to add the appropriate logic to your application.

Reports Access & Permissions

Reports are the submissions of the Reporting UI resource. Access to reports is managed on the Access tab of the Reporting UI resource. Access to the Report data is managed in the Access tab of each report source form.

The correct permissions must be set to ensure the availability of the Report and reporting data within the application. The correct user-base role accessing the application and report must be added to the following permissions:

In the example below, the userbase accessing the application and viewing the report carries the Authenticated role. Your user-base role may differ.

Project Level Access Permission

Read All: Authenticated

Reporting UI Resource

Read All Submission: Authenticated

Any Forms/Resources being used as a Source Form in a report

Read All Submission: Authenticated

Report Events

You can subscribe to the Reporting Grid events and perform some custom logic on them:

import {  Formio } from 'formiojs';

Formio.Report.create(...).then(report => {
  report.on('eventName', (arg1, arg2 ...) => {
   // custom code
  });
 });

Events:

  • rowClick (args: rowData, rowIndex) - emitted each time the Reporting Grid row is clicked;

  • rowSelectChange (args: selectedRows, reportingGridInstance) - emitted each time the Reporting Grid row is selected/deselected ;

  • fetchDataErrors (args: error, reportingGridInstance) - emitted when there is an error fetching the Reporting Grid data;

  • page (args: currentPage, reportingGridInstance) - emitted each time the Reporting Grid page changes;

  • changeItemsPerPage (args: itemsPerPage) - emitted each time the number of items for the Reporting Grid page changes.

FAQ

Why am I receiving a Reports Loading Error when loading the Report tab?

This error typically occurs when accessing the Report page in Projects that were created before the Reporting Module was activated for the environment License.

In such instances, the Project lacks the essential Reporting UI configuration Resource necessary for the creation and management of Reports within the Project

To resolve this, Import the reporting UI configuration Resource to the project resources using the following embed URL and ensure the Resource is titled Reporting UI. The resource name and path must be reportingui. {{ yourServerUrl }}/reportingui/config

Is there a required formio.js version to render the Report inside my application?

The Reporting Module renderer requires a formio.js version of 4.18.0 or higher and needs to match the application renderer the Report is being embedded into.

Why does my report display items in the incorrect order?

This can sometimes occur when databases are updating. To resolve the issue, simply re-sort the Reporting Grid or refresh the page.

Why are fields inside my EditGrid/DataGrid unavailable to add to my Report columns?

Because of the complex nature of array data, the Reporting Module does not support fields inside an Edit Grid / Data Grid at this time.

Stay tuned, we plan to implement MongoDB's unwind feature in a future update of the Reporting Module to support complex data structures.

Why are my date fields unavailable to use for certain operators?

Form.io offers multiple component options for Date/Time. These components serve different purposes and also save their data in different formats.

The Date/Time saves its data as an ISO format while the Textfield Calendar saves the data as a string. Because of these differences, certain reporting Operators will only be available for certain Date/Time components.

When configuring a Report, ensure you are comparing the same Date/Time component types when comparing data values using the Reporting Operators.

I have updated my Report but I am not seeing the changes inside my application.

For performance reasons, the Reporting Module is stored in the Applications local storage. If updates on the Report are not reflected in the application, clear the cache within your Local Storage and perform a hard refresh on the browser.

Why do I receive an Unauthorized error when viewing the Report inside my application?

Just like forms, the correct permissions must be set to ensure the availability of the Report and reporting data within the application. The correct user-base role accessing the application and report must be added to the following permissions:

In the example below, the userbase accessing the application and viewing the report carries the Authenticated role. Your user-base role may differ.

Project Level Access Permission

Read All: Authenticated

Reporting UI Resource

Read All Submission: Authenticated

Any Forms/Resources being used as a Source Form in a report

Read All Submission: Authenticated

Using this tab, you can add additional columns to your Reporting Grid that are calculated based on the fields of the source forms. Previously defined calculated values can be used as arguments in subsequent calculations.

PreviousSubmissionsNextUser Authentication

Last updated