Data adapters for dyn. data control

Reports that display repeating data — lists, tables, card grids — need a data source. JasperWho? supports two ways to supply that data at render time: a live SQL connection that queries a database automatically, or a dynamic array delivered directly in the render request. Understanding when to use which approach, and how each one works, is key to getting the most out of JasperWho?.

Two Approaches, One Result

Both approaches produce the same result: a populated report. The choice depends on where your data lives and who is best placed to retrieve it.

Reports without a detail band — purely static layouts driven by parameters — need neither.

SQL Connections

A ReportConnectionConfig defines a live database connection that JasperWho? uses to fetch data at render time. When assigned to a ReportConfig, JasperWho? executes the configured SQL query against that connection, takes the result rows, and feeds them as field data into the report.

Setting Up a Connection

A connection config holds the credentials and driver settings for one database. Supported drivers are MySQL, MariaDB, PostgreSQL, and Microsoft SQL Server.

Before a connection can be assigned to a report, it must be tested and approved. JasperWho? runs a test query against the database to verify connectivity — only connections with a passing test are available in the ReportConfig assignment dropdown.

[SCREENSHOT: JasperWho? — the ReportConnectionConfig form or list, showing a connection with "approved" status badge in green.]

Writing the SQL Query

The SQL query is written and stored in JasperWho? — not in the .jrxml. It lives on the ReportConfig record and is executed against the assigned connection at render time.

The query must return columns whose names match exactly the field names defined in the .jrxml. For a report with fields articleNumber, description, and moq, the query must alias its columns accordingly:

SELECT
    art_no          AS articleNumber,
    art_description AS description,
    min_order_qty   AS moq,
    delivery_days   AS deliveryTime,
    supplier_name   AS supplier,
    barcode
FROM articles
ORDER BY art_no ASC

Column names are case-sensitive. articleNumber and articlenumber are not the same field.

Using Parameters as SQL Variables

Parameters passed in the render request are available as named bindings in the SQL query using the :PARAMETER_NAME syntax. JasperWho? scans the query for :name placeholders before execution and binds only the parameters that are actually referenced — extras are silently ignored.

This makes it straightforward to filter, sort, or paginate the result set based on render-time input:

-- Filter by article number
SELECT
    art_no          AS articleNumber,
    art_description AS description,
    min_order_qty   AS moq
FROM articles
WHERE art_no = :P_ARTICLE_NUMBER

-- Date range filter with two parameters
SELECT
    order_id        AS orderId,
    customer_name   AS customerName,
    order_date      AS orderDate,
    total_amount    AS totalAmount
FROM orders
WHERE order_date BETWEEN :P_DATE_FROM AND :P_DATE_TO
ORDER BY order_date ASC

-- Wildcard search
SELECT
    art_no          AS articleNumber,
    art_description AS description
FROM articles
WHERE art_description LIKE CONCAT('%', :P_SEARCH_TERM, '%')

The render request for the date range example would look like this:

POST /api/v1/report-config/OrderList/render

{
  "outputType": "base64",
  "parameters": {
    "P_DATE_FROM": "2024-01-01",
    "P_DATE_TO":   "2024-03-31"
  },
  "data": [],
  "createHistoryRecord": true,
  "createPrintTask": false
}

Parameter Promotion from Query Results

There is a powerful pattern worth knowing: if a SQL result column has the same name as a registered parameter on the report, JasperWho? automatically promotes that value from the data rows into the parameters map — before the report renders.

This means you can derive parameter values directly from the database without having to pass them in the render request. The query does the lookup; the result feeds both the detail band and the header parameters in a single call.

Consider a report that prints a picking list for a warehouse order. The header shows the order number, the customer name, and the warehouse location — all parameters. The detail band shows the individual line items — fields. Normally you would have to fetch the order header separately and pass it as parameters. With parameter promotion, a single query can deliver everything:

-- First row drives the header parameters, all rows drive the detail band.
-- order_number,P_ORDER_NUMBER, customer_name,P_CUSTOMER_NAME, and warehouseP_WAREHOUSE match registered parameter
names
-- names and will be promoted automatically. The remaining columns stay as field data.

SELECT
    o.order_number      AS order_number,P_ORDER_NUMBER,
    c.customer_name     AS customer_name,P_CUSTOMER_NAME,
    w.location_code     AS warehouse,P_WAREHOUSE,
    ol.sku              AS sku,
    ol.description      AS description,
    ol.quantity         AS quantity,
    ol.bin_location     AS binLocation
FROM orders o
JOIN customers c   ON c.id = o.customer_id
JOIN warehouses w  ON w.id = o.warehouse_id
JOIN order_lines ol ON ol.order_id = o.id
WHERE o.order_number = :P_ORDER_NUMBER
ORDER BY ol.bin_location ASC

The render request only needs the order number:

POST /api/v1/report-config/PickingList/render

{
  "outputType": "base64",
  "parameters": {
    "P_ORDER_NUMBER": "ORD-2024-00451"
  },
  "data": [],
  "createHistoryRecord": true,
  "createPrintTask": false
}

JasperWho? executes the query, detects that order_numberP_ORDER_NUMBER, customer_nameP_CUSTOMER_NAME, and warehouseP_WAREHOUSE match registered parameter names, moves their values from the first data row into the parameters map, and renders the report with a populated header and a fully populated detail band — all from one query, one request.

Dynamic Array

When no ReportConnectionConfig is assigned, JasperWho? expects the data to arrive in the render request itself — as a JSON array in the data field. Each object in the array represents one row in the detail band, with keys matching the field names defined in the .jrxml.

This approach is ideal when the calling application already has the data in memory, when the data comes from a source JasperWho? cannot connect to directly, or when the data structure is too dynamic to express in a fixed SQL query.

A complete render request with inline data looks like this:

POST /api/v1/report-config/A5_KanBan/render

{
  "outputType": "base64",
  "parameters": {
    "P_ARTICLE_NUMBER": "4561287-154"
  },
  "data": [
    {
      "articleNumber": "4561287-154",
      "description":   "Packing Carton Size 1 - 200x150x50mm",
      "moq":           250,
      "deliveryTime":  "3 Days",
      "supplier":      "Ninghao Packaging",
      "barcode":       "5698532145712"
    }
  ],
  "createHistoryRecord": false,
  "createPrintTask": false
}

For reports that print one item per page, the data array typically contains a single object. For list or table reports, it contains one object per row.

Static Reports — No Data Needed

Reports without a detail band require neither a connection config nor a data array. The entire output is driven by parameters alone. Common examples: cover pages, certificates, summary headers, QR code labels, or any document where the layout is fixed and all variable content comes from a handful of input values.

For these reports, the render request simply omits data entirely:

POST /api/v1/report-config/CertificateOfConformity/render

{
  "outputType": "url",
  "parameters": {
    "P_PRODUCT_NAME":   "Industrial Bearing 6205-2RS",
    "P_BATCH_NUMBER":   "BAT-2024-0077",
    "P_ISSUE_DATE":     "2024-03-15",
    "P_INSPECTOR_NAME": "M. Fischer"
  },
  "createHistoryRecord": true,
  "createPrintTask": false
}