Skip to content

Authoring V2 reports with a JSON data source

calServer V2 can fill JasperReports templates from a JSON dataset with readable API field names -- instead of SQL embedded in the template that queries the old metrology code columns (I4201, C2303, ...). Such templates contain no SQL and therefore work independently of the database backend (MySQL, PostgreSQL, MS SQL Server).

Background and decision: ADR-009.

When JSON, when SQL?

  • Existing V1 templates (JDBC + code columns) keep working unchanged -- they do not need to be converted.
  • New V2 templates should use the JSON data source: one naming world (template fields = API fields), DB-independent, custom fields included.

Classic SQL template + V2-only data = empty report

A V1 SQL template reads the V1 tables (calibration, inventory, ...). Records created directly in V2 (or seeded demo data) exist only in the V2 tables -- the SQL query then finds no row, and JasperReports still renders a "successful" multi-page but empty PDF. calServer now detects this situation before rendering and aborts with an actionable error message (also visible in the report run log) instead of delivering a blank PDF. Remedy: use the V2 JSON variant of the template (e.g. DAKKS-JSON-SAMPLE instead of DAKKS-SAMPLE).

Activating a template as a V2 JSON report

This happens automatically: create the template as usual under Administration > Report Management (folder, file name, format) and upload the bundle. If the main JRXML contains no embedded SQL (no <queryString> -- the hallmark of every V2 bundle), calServer detects this at generation time and builds the JSON dataset using the grid's default contract:

Grid Default contract
calibration calibration-certificate
inventory inventory-datasheet
booking order-document
repair repair-report
location location-report

Templates with embedded SQL (classic V1 bundles) keep running via JDBC, unchanged.

Override (optional): a report-scoped report variable named data_contract overrides the detection -- either with a different contract name, or with a non-contract value such as jdbc to force the classic SQL/JDBC path for this template.

You can fetch the dataset of a specific calibration directly (useful as a sample file while designing):

GET /api/v2/calibrations/{ctag}/report-dataset

Available contracts

Contract Report family Sample dataset
calibration-certificate (v1.10) Calibration certificate DAKKS-JSON-SAMPLE/main_reports/sample-data.json
inventory-datasheet (v1.4) Device datasheet INVENTORY-JSON-SAMPLE/main_reports/sample-data.json
order-document (v1.0) Order / delivery note ORDER-JSON-SAMPLE or DELIVERY-JSON-SAMPLE (main_reports/sample-data.json)
repair-report (v1.0) Repair report REPAIR-JSON-SAMPLE/main_reports/sample-data.json
location-report (v1.2) Location/loan report (loan receipt / despatch note) LOCATION-JSON-SAMPLE/main_reports/sample-data.json

The calibration-certificate contract returns a root object with the blocks meta, laboratory, accreditation, calibration, device, customer, procedure, environment, report_variant, standards[], results[], decision_rule, signatures[] and texts. Each entity block additionally carries a custom_fields object exposing user-defined fields under their api_name.

Every row in results[] carries the verdict three times:

  • pass_fail -- the decision in MET/TEAM spelling (Pass, Fail, Pass Indeterminate, Fail Indeterminate). The backend normalises the column for the dataset and reads test_status first: the stored column stays binary because it goes back to MET/TEAM verbatim, the dataset carries the nuance. Rows from MET/TRACK or the V1 runtime, which hold the decision in test_status alone, only arrive with a verdict because of this. Unknown, customer-specific values pass through untouched.
  • test_status -- plain text including the guard-band zones Conditional / ConditionalFail.
  • conformity -- the certificate's legend symbol (empty, ?, !?, !).

For the conformity column of a new template, bind conformity -- the plain text fits neither the column width nor the legend. The bundles inherited from V1 (DAKKS-JSON-SAMPLE) bind pass_fail because they are byte-exact copies of the accredited original; that is exactly what the normalisation is for. guardband_meth names the decision rule that was applied.

From v1.8 every row also carries the uncertainty budget (Measurement uncertainty per GUM): uncert_method, cov_fac and deg_free as text like the other columns, plus budget with the method, k and where it came from (dof, plan, fixed), the effective degrees of freedom, u, U, a raise to the CMC, a stand-in from an uncertainty model, the reason for a missing U and one line per contribution, so a template can print the budget as an annex. budget is null for rows without a method (imported or recorded before GUM). texts.uncertainty follows the actual k; with k = 2 it stays the previous sentence. Every contribution line names its degrees of freedom (degrees_of_freedom, null for infinite): N − 1 for S1 and, where the uncertainty model states them, those of a model contribution (addendum 6.2 of the ADR, same shape, no new field).

From v1.9 the block decision_rule names the calibration's decision rule (Decision rule, ISO/IEC 17025:2017 7.8.6.2): key (non_binary, binary_guarded, simple), reference and section in ILAC-G8:09/2019, guard_band (w = U or w = 0), binary, origin (lab, customer, manual, points), agreement (where the rule is agreed), points_decided and points_without_uncertainty, plus the three texts of the "Conformity" section in the certificate's language: statement, symbols, legend. The JRXML templates receive the same texts as the parameters Conformity_description_1 to _3 once calServer has judged points of the calibration; a report variable of the same name wins. The default of texts.conformity_legend is corrected to * nicht im Akkreditierungsumfang. Before, it read * im Akkreditierungsumfang, the opposite of what the asterisk marks; a text of the laboratory's own stays untouched.

From v1.10 decision_rule.guard_band also names a guard band other than w = U, such as w = 1.5 · U or w for a risk of at most 2.5 %, in the certificate's language; decision_rule.guard carries the band as data: mode (u, factor, risk), factor and risk in percent (The guard band w). Every row in results[] also carries accred_source, the origin of its statement on the accreditation scope: derived (from the annex, frozen at capture), plan (calScript accred no), manual, import (MET/TEAM) or empty for rows from before this column (Accreditation scope). The meaning of results[].accred changed, not its shape: the capture now writes the value from the annex in the quality specifications. Before, every row captured in calServer carried the database default 1 and so claimed to be in scope; without a maintained annex the column now stays empty (no statement).

Designing templates in Jaspersoft Studio

  1. Create a JSON data adapter pointing at the sample file (sample-data.json or the output of the report-dataset endpoint).
  2. Bind fields via their JSON path, not via SQL. In JRXML this is the fieldDescription:
<field name="serial_number" class="java.lang.String">
  <fieldDescription><![CDATA[device.serial_number]]></fieldDescription>
</field>
  1. Render repeating lists (measurement results, reference standards) via a subreport with subDataSource -- no connection, no own SQL:
<subreport>
  <dataSourceExpression><![CDATA[
    ((net.sf.jasperreports.engine.data.JsonDataSource)$P{REPORT_DATA_SOURCE})
      .subDataSource("results")
  ]]></dataSourceExpression>
  <subreportExpression><![CDATA[$P{Reportpath} + "/subreports/results.jasper"]]></subreportExpression>
</subreport>
  1. Custom fields are available under their api_name in the custom_fields block, e.g. fieldDescription = device.custom_fields.customer_tag.

JasperReports version

JasperReports 6.20.6 remains mandatory (see robots.md in the repository). Do not use features that require a newer version.

Describe parameters

A V2 JSON bundle can ship a parameter catalog so calServer offers the configurable parameters with description, type and default value when creating report variables. Details: Describing parameters in the report package.

Reference template

The pilot bundle DAKKS-JSON-SAMPLE in the calhelp/calServer-reports repository shows main and subreports (results, standards) fully on a JSON basis and includes the matching sample-data.json.

Code-to-API-name mapping

The mapping from old code columns to readable api_names is authoritative in the api_field_mappings table and available per language via the FIELD-NAMES report. Excerpt of the core fields:

V1 code api_name Meaning
I4201 asset_number Asset/device number
I4202 serial_number Serial number
C2301 calibration_date Calibration date
C2303 next_calibration_date Next calibration date
K4601 name Customer name