Report Search Datalist Binder Plugin

Introduction

The Report Search Datalist Binder Plugin allows you to create a datalist binder that supports data aggregation and pivoting on raw data using SQL queries. The plugin is best paired with EChart Userview Menu Plugin.

• Pivoting is the process of transforming rows into columns, or reorganizing data from a long format into a wide format. It is useful when data needs to be presented in a different structure. For example, if you have a table with dates, categories, and values, and want to turn categories into columns, you would pivot the data.
  Learn more: https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver16
  
  
• Aggregating involves performing a calculation on a set of values to return a single summary value. Common aggregate functions in MySQL include SUM, COUNT, AVG, MAX, and MIN. Aggregates are typically used with the GROUP BY clause to summarize data, such as finding totals, averages, or maximum values.
  Learn more: https://www.simplilearn.com/tutorials/sql-tutorial/sql-aggregate-functions

Plugin information

• Plugin Available in the Bundle: JDBC Datalist Database Binder (Report)
• Compatibility: Joget DX 8 and onwards

Get started

Where to get the plugin

You can download the Report Search Datalist Binder Plugin from the Joget Marketplace.

How to install

1. Start your Joget server and open the App Center.
2. Log in as an admin.
3. Go to Settings > Manage Plugins > Upload Plugin and upload the downloaded plugin.

How to use it

1. In the Form Builder, create a new form with the Settings configuration as below:
   
   • Form ID: item
   • Form Name: Item
   • Table Name: budget_item
     
     
2. Once the form is created, go to Design and design it with a section called Budget and add the fields Name, Year, Status and Amount using text fields as shown below:
   
3. After completing the form design, click Save and go to Generate App. Select Generate CRUD and click Generate.
   
4. In the List Builder, create a new datalist. Go to the Data > Select Data Store > Select Source of Data and choose JDBC Datalist Database Binder (Report) as shown in the image below.
   

Configure Database SQL Query Properties

When the Configure Database SQL Query form appears, navigate to Datasource. There are two options: 

• Default Datasource – Uses the Joget application’s built-in database. In this example, you’ll be selecting Default Datasource, which means Joget will automatically use its current database without extra configuration.
• Custom Datasource – Used only if you need to connect Joget to an external database. This option requires additional configuration, as shown in the example figure below.
  

Field to configure: 

• Custom JDBC Driver: Enables a Java application to connect to and interact with a database or data source.
• Custom JDBC URL: Crafted connection string used to establish a connection to a database. 
  Example template : jdbc:mysql://<host>:<port>/<databaseName>?characterEncoding=UTF-8&useSSL=false
• Custom JDBC Username: The specific username used to establish a connection to a database.
• Custom JDBC Password: The password used to establish a connection to a database. 
  
  Test Connection
    At the bottom of the page, click Test Connection. If the configuration is successful, a popup will appear with the message “Database connected.”

SQL Query

In this step, you will configure the SQL Query to define how data is retrieved and processed from the database. This involves two parts that work together:

1. The SQL SELECT Query is used to pull raw data from your table. It retrieves the dataset before any Aggregation or Pivot operations are applied. In this configuration, you will need to define the SQL SELECT Query as follows:
   

   SELECT * FROM app_fd_budget_item ORDER BY c_year ASC, c_status ASC

   Note:
   If your column name contains reserved keywords or special characters (like .), encapsulate the identifier with backticks.
   

   SELECT `myAppName.myColumn` 
   FROM app_fd_myTable;

2. The SQL SELECT Query (Data) is used to summarize and group raw data retrieved from the SQL SELECT Query. It performs aggregation on the dataset, such as calculating totals, averages, or counts. In this configuration, you will need to define the SQL SELECT Query (Data) as follows:
   

   SELECT c_name as `name`, c_year, c_status, sum(c_amount) as `amount` FROM #data# GROUP BY c_year, c_status, c_name

   (Here, #data# is a placeholder that injects the raw SQL query above.)

   Note:
   Use GROUP BY to group rows that share the same values in selected columns. It is commonly used with aggregate functions like SUM, COUNT, AVG, MAX, or MIN.

Configure Pivot Data

Set up the pivot settings to transform and summarize your dataset. Example configuration as below:
Field to configure:

• Primary Key: Primary key to uniquely identify each row of the table. In here, we are using name (c_name) as a primary key as GROUP BY will be applied to c_name column.
• Value to Fill Up Empty Cell: The value to fill up if there is an empty cell.
• Pivot Data: When this option is checked, the plugin will perform pivoting on the data. When Pivot Data is enabled, the plugin cannot determine the column names in advance. Instead, it generates them dynamically in the datalist under the columns/filters section, based on the aggregation column. In this example, the column c_name AS name is generated. You need to drag this column into the Datalist, and the other columns will then be generated dynamically.
  
  Warning
  If you choose not to pivot the data, you must drag the required columns into the Datalist one by one.
• Pivot Column Name: Refers to selecting the field or column in your dataset that you want to use as a basis for creating columns or headers in the pivot table. Multiple columns supported. Separate multiple columns using a semicolon (;).
• Pivot Value Column Name: Refers to the field or column in your dataset that contains the data you want to summarize or aggregate in your pivot table. Multiple columns supported. Separate multiple columns using a semicolon (;).

Create UI Design

1. Drag the name under the Column/Filters into the list Design to complete your datalist setup.
2. Go to Preview, you can see the Pivot Table that has been created.
   
3. Create a UI using the UI Builder. Pair it with the EChart Userview Menu Plugin to dynamically change chart values and shapes based on the datalist. Make sure you set Show Filter to Yes under Configure EChart Menu, as shown in the figure.
   

Configure Data Mapping

In this step, you will configure data mapping to define how the dataset fields are linked to the chart. Data mapping ensures that the correct values are displayed along the X-axis and Y-axis in the EChart.

Field to configure:

• List: Select the List you created. (e.g., Report)
• X-axis Value: Choose an existing value from your dataset.
• Number Values: Click Add Row to insert the required numeric fields.

Expected Outcome

After saving, return to the front-end UI. Launch the app and navigate to Report. The List and EChart are now displayed together. The example below shows the chart without a filter applied.

When you choose 2021 in the Year filter, the datalist changes automatically. The EChart also updates to match the filtered data.

Download Plugin

Download the plugin from the Joget Marketplace.

Download Sample App