DEV Community

Explorer
Explorer

Posted on

📊 Generate Dynamic HTML Reports in Joget with BeanShell

#joget #beanshell #java #reports

1. Overview

This article shows how to generate a dynamic HTML report in Joget using BeanShell. The script reads an HTML template, loads related records from form tables, replaces placeholders, appends repeating sections, and returns the final report content.

2. How It Works

  • Load the report template from a database table.
  • Query the main request and related child records.
  • Store placeholder values in maps.
  • Replace template placeholders with form values.
  • Use Jsoup to append repeating HTML sections.
  • Return the final HTML through a FormRowSet.

3. Where to Use in Joget

  • Form Builder: load binder or custom binder for report preview fields.
  • Datalist/Reports: dynamic report generation from form records.
  • Workflow Builder: generate printable HTML before approval or archival.

4. Full Code 

import org.joget.apps.form.model.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.joget.apps.app.service.AppUtil;
import org.joget.commons.util.LogUtil;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;

 Connection conn = null;
 FormRowSet f = new FormRowSet();
 String Result;
 String AssetForeignKey;
 String guarantorForeignKey;
 String AssetHTML;
 String guarantorHTML;
 String OtherIncomeHTML;
 String employmentForeignKey;


  try {
   LogUtil.info("", "Loading Form Data");
DataSource ds = (DataSource) AppUtil.getApplicationContext().getBean("setupDataSource");
Connection conn = ds.getConnection();
String query = "SELECT html,assethtml,guarantorhtml,otherincomehtml FROM report_template where id=1";
PreparedStatement Stmt = conn.prepareStatement(query);
ResultSet resultSet = Stmt.executeQuery();

if (resultSet.next()) {
    Result = resultSet.getString("html");
    AssetHTML=resultSet.getString("assethtml");
    guarantorHTML=resultSet.getString("guarantorhtml");
    OtherIncomeHTML=resultSet.getString("otherincomehtml");
}
Stmt.close();

   Map data = new HashMap();
   Map asset= new HashMap();
   Map guarantor=new HashMap();
   Map OtherIncome=new HashMap();

        String query1="SELECT * FROM app_fd_main_request vla JOIN app_fd_request_profile bi ON vla.id = bi.c_generic_field JOIN "+
    "app_fd_income_details ei ON vla.id = ei.c_generic_field JOIN "+
    "app_fd_request_details li ON vla.id = li.c_generic_field where bi.c_generic_field='#reportParam.recordId#'";
       // String query1="select * from app_fd_request_profile where c_generic_field='e8ecc6a8-e541-472a-a846-2e88cd9fb7ce'";
        PreparedStatement Stmt1 = conn.prepareStatement(query1);

    ResultSet rs = Stmt1.executeQuery();
        if (rs.next()) {
            AssetForeignKey=rs.getString("c_generic_field");
            guarantorForeignKey=rs.getString("c_generic_field");
            employmentForeignKey=rs.getString("c_generic_field");

 //------Borrower Details
    data.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");
    data.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");
    data.put("~template_placeholder~", rs.getString("c_generic_field") != null ? (rs.getString("c_generic_field").equals("1") ? "Male" : "Female") : "");
    data.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");
    data.put("~template_placeholder~", "");
    data.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");
    data.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");
    data.put("~template_placeholder~", "");
    data.put("~template_placeholder~", rs.getString("c_generic_field") != null && rs.getString("c_generic_field") != null ? "+" + rs.getString("c_generic_field") + rs.getString("c_generic_field") : "");
    data.put("~template_placeholder~", rs.getString("c_generic_field") != null && !rs.getString("c_generic_field").isEmpty() ? "+" + rs.getString("c_generic_field") + rs.getString("c_generic_field") : "");
    data.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");
    data.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");

    // Resident Address
    data.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");
    data.put("~~template_placeholder~", "");
    data.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");
    data.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");
    data.put("~template_placeholder~", "");
    data.put("~template_placeholder~ /~template_placeholder~", "");

    // Loan Info
    data.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");
    data.put("~template_placeholder~", "");
    data.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");
    data.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");
    data.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");
    data.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");
    data.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");
    data.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");
    data.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");
    data.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");

    // Employment Info
    data.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");
    data.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");
    data.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");
    data.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");
    data.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");
    data.put("~template_placeholder~", "");
    data.put("~template_placeholder~", "");
    data.put("~template_placeholder~", "");
        }

        for (Map.Entry entry : data.entrySet()) {
            Result = Result.replace(entry.getKey(), entry.getValue());
            LogUtil.info("", entry.getValue());
          }
    String AssetQuery="select * from app_fd_item_details where c_generic_field='"+AssetForeignKey+"'";
    Stmt1 = conn.prepareStatement(AssetQuery);
    rs = Stmt1.executeQuery();
    StringBuilder multiAssetHTML=new StringBuilder();

        while (rs.next()) {
            String initalizeHTML=AssetHTML;
            asset.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");
            asset.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");
            asset.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field"): "");
            asset.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");
            asset.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");

            asset.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");
            asset.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field"): "");
            asset.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");
            asset.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");
            asset.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");
            asset.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");
            asset.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field"): "");
            asset.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");

            for (Map.Entry entry : asset.entrySet()) {
                initalizeHTML = initalizeHTML.replace(entry.getKey(), entry.getValue());
                LogUtil.info("", entry.getValue());
              }
               LogUtil.info("", initalizeHTML);
              multiAssetHTML.append(initalizeHTML);
            LogUtil.info("", "Finished");
        }
        Document doc = Jsoup.parse(Result);
        Element div = doc.getElementById("sectionAsset");

        div.append(multiAssetHTML.toString());
        Result=doc.toString();

 //OtherIncome

 String otherIncomeQuery="select * from app_fd_additional_income where c_generic_field='"+employmentForeignKey+"'";
 Stmt1 = conn.prepareStatement(otherIncomeQuery);
 rs = Stmt1.executeQuery();
 StringBuilder multiIncomeHTMl=new StringBuilder();



     while (rs.next()) {
         String initalizeHTML=OtherIncomeHTML;
         OtherIncome.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");
         OtherIncome.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");

         for (Map.Entry entry : OtherIncome.entrySet()) {
             initalizeHTML = initalizeHTML.replace(entry.getKey(), entry.getValue());
             LogUtil.info("", entry.getValue());
           }
           multiIncomeHTMl.append(initalizeHTML);
     }
     Document doc = Jsoup.parse(Result);
     Element div = doc.getElementById("otherIncomeSection");

     div.append(multiIncomeHTMl.toString());
      Result=doc.toString();

  //guarantor

  String guarantorQuery="select * from app_fd_related_party where c_generic_field='"+guarantorForeignKey+"'";
 Stmt1 = conn.prepareStatement(guarantorQuery);
 rs = Stmt1.executeQuery();
 StringBuilder multiguarantorHTMl=new StringBuilder();



     while (rs.next()) {
         String initalizeHTML=guarantorHTML;
         guarantor.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");
         guarantor.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field"): "");
         guarantor.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field") : "");
         guarantor.put("~template_placeholder~",  "");
         guarantor.put("~template_placeholder~", rs.getString("c_generic_field") != null && rs.getString("c_generic_field") != null ? "+" + rs.getString("c_generic_field") + rs.getString("c_generic_field") : "");
         guarantor.put("~template_placeholder~", "");
         guarantor.put("~template_placeholder~", rs.getString("c_generic_field") != null ? rs.getString("c_generic_field"): "");
         guarantor.put("~template_placeholder~", "");

         for (Map.Entry entry : guarantor.entrySet()) {
             initalizeHTML = initalizeHTML.replace(entry.getKey(), entry.getValue());
             LogUtil.info("", entry.getValue());
           }
           multiguarantorHTMl.append(initalizeHTML);
     }
     Document doc = Jsoup.parse(Result);
     Element div = doc.getElementById("sectionGuarantor");

     div.append(multiguarantorHTMl.toString());
      Result=doc.toString();



        Stmt1.close();
        conn.close();
FormRow r1 = new FormRow();
r1.put("report",Result);
f.add(r1);
  } catch (Exception e) {
       LogUtil.info("", "Error Binding Data: " + e.getMessage());

    } finally {
        try {
            if (conn != null && !conn.isClosed()) {
                conn.close();
            }
        } catch (SQLException e) {
            LogUtil.info("", "Error closing connection: " + e.getMessage());
        }
    }


return f;
Enter fullscreen mode Exit fullscreen mode

5. Example Use Cases

  • Generating agreement or request reports.
  • Building printable summaries from parent and child forms.
  • Rendering repeating table sections into an HTML template.
  • Preparing HTML content for PDF generation.

6. Customization Tips

  • Replace template table and form table names with your own generic app tables.
  • Keep placeholders consistent between your HTML template and BeanShell maps.
  • Use separate maps for parent, child, and repeating-section data.
  • Close database resources in inally blocks.

7. Key Benefits

  • Keeps report layout separate from data retrieval.
  • Supports repeating child-record sections.
  • Works inside Joget without an external report server.
  • Easy to extend for additional sections.

8. Security Note

Do not publish real table names, customer fields, IDs, or personal data. Escape or validate values before rendering them into HTML if the data can contain user-provided content.

9. Final Thoughts

This pattern is useful when a Joget app needs a custom, printable report from multiple related tables. Keep the template clean, keep the mappings explicit, and sanitize public examples before sharing.

Top comments (0)