🔎 Build Dynamic SQL Filters for Joget Reports with BeanShell

Overview

Sometimes a Joget report needs to be rebuilt from filter fields before a datalist or chart displays the result. This BeanShell pattern reads filter values from a form, builds a safe parameterized SQL query, groups records by status, and writes the summarized result into a reporting table.

This is useful when a chart, datalist, or report plugin expects data from a simple table.

How It Works

1. Read filter values from a Joget form.
2. Build the WHERE clause only for filters that have values.
3. Use PreparedStatement parameters instead of SQL string concatenation.
4. Group records by status and count them.
5. Rebuild the report summary table.
6. Close all database resources in inally.

Where to Use in Joget

Use this in Workflow Builder, Form Builder post-processing, Userview actions, or a BeanShell tool that runs before opening a dashboard/report.

Full Code

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.sql.DataSource;
import org.joget.apps.app.service.AppUtil;
import org.joget.commons.util.LogUtil;
import org.joget.commons.util.UuidGenerator;

public void rebuildReportSummary() {
    Connection con = null;
    PreparedStatement selectPs = null;
    PreparedStatement insertPs = null;
    ResultSet rs = null;

    String requester = "#form.report_filter.requester#";
    String status = "#form.report_filter.status#";

    try {
        DataSource ds = (DataSource) AppUtil.getApplicationContext().getBean("setupDataSource");
        con = ds.getConnection();

        con.prepareStatement("delete from app_fd_report_summary").executeUpdate();

        StringBuilder sql = new StringBuilder();
        sql.append("select count(*) as total_count, ");
        sql.append("case when c_status is null or c_status = '' then 'Blank Record' else c_status end as status_label ");
        sql.append("from app_fd_request_data ");

        boolean hasRequester = requester != null && !requester.trim().isEmpty();
        boolean hasStatus = status != null && !status.trim().isEmpty();

        if (hasRequester || hasStatus) {
            sql.append("where ");
        }
        if (hasRequester) {
            sql.append("c_requester = ? ");
        }
        if (hasStatus) {
            sql.append(hasRequester ? "and " : "");
            sql.append("c_status = ? ");
        }

        sql.append("group by case when c_status is null or c_status = '' then 'Blank Record' else c_status end ");
        sql.append("order by status_label");

        selectPs = con.prepareStatement(sql.toString());
        int index = 1;
        if (hasRequester) {
            selectPs.setString(index++, requester);
        }
        if (hasStatus) {
            selectPs.setString(index++, status);
        }

        rs = selectPs.executeQuery();
        insertPs = con.prepareStatement("insert into app_fd_report_summary (id, c_count, c_status) values (?, ?, ?)");

        UuidGenerator uuid = UuidGenerator.getInstance();
        while (rs.next()) {
            insertPs.setString(1, uuid.getUuid());
            insertPs.setInt(2, rs.getInt("total_count"));
            insertPs.setString(3, rs.getString("status_label"));
            insertPs.executeUpdate();
        }
    } catch (Exception e) {
        LogUtil.error("dynamic-filter-report", e, "Failed to rebuild report summary.");
    } finally {
        try { if (rs != null) rs.close(); } catch (Exception e) { }
        try { if (selectPs != null) selectPs.close(); } catch (Exception e) { }
        try { if (insertPs != null) insertPs.close(); } catch (Exception e) { }
        try { if (con != null) con.close(); } catch (Exception e) { }
    }
}

rebuildReportSummary();

Example Use Cases

• Build dashboard summary rows from dynamic filters.
• Refresh chart data before a report page opens.
• Group workflow records by status, category, department, or requester.
• Prepare a lightweight reporting table for datalists.

Customization Tips

• Replace pp_fd_request_data with your source form table.
• Replace pp_fd_report_summary with your reporting table.
• Add more optional filters by following the same hasFilter pattern.
• Avoid TRUNCATE when database permissions are limited; DELETE is easier to run in most Joget setups.
• For multi-user reports, add a session/user key so one user does not overwrite another user's summary data.

Key Benefits

• Keeps report filtering server-side.
• Uses safer SQL parameters.
• Makes chart/datalist data easier to consume.
• Avoids hard-coded filter combinations.

Security Note

Do not publish real table names, field IDs, requester names, or SQL tied to a private business process. Keep public examples generic and map them privately in your Joget app.

Final Thoughts

Dynamic filtering is easier to maintain when the SQL is built carefully and parameters are used consistently. This pattern keeps the report logic flexible without turning the query into unsafe string concatenation.