Dynamic Report

Overview

Dynamic Report is the applet for questions the fixed report applets do not answer. You write a SELECT against the tenant database, save it as a query template or straight into a dynamic report, run it, and BigLedger keeps the result set — as rows you can browse in a grid and as a CSV file — under a run. A dashboard is a canvas of blocks (bar, line, pie, area, scatter, donut, column, heatmap, score card or table) that each read one report’s stored result.

It is a read-only tool by design: the backend accepts only statements that begin with SELECT or WITH, rejects the write keywords, and refuses ten login and permission tables outright. It writes nothing to the ledger, the stock balance or any business document. Open it when an accountant or analyst needs a one-off cut of the data; open the Financial Report, Sales Report or Purchase Report applets for the pre-built ones.

Where it fits

Screens and menus

The bundle is served at applet/tnt/wavelet/erp/dynamic-report-applet. Its sidebar has three entries; the applet lands on Dashboard by default.

Two more screens exist in app.routing.ts and are reachable only by typing the URL — their menu entries are commented out in menu-items.ts:

Both are the scheduling half of the product — an event with a recurrence rule and a template enqueues runs — and both work against live endpoints. They are unlinked, not removed.

Settings and Personalization use the shared shell. Settings offers Field Settings and Default Selection; the permission screens (client-side permission listing, Permission Wizard, Permission Set, User / Team / Role Permission), Webhook and Release Notes are all routed. Personalization offers Field Settings — which 404s, because that route is registered under settings and not under personalization — and Default Selection.

Configuration

Before you can use it

Applet settings

Settings are applet-local in name only. app.routing.ts imports this applet’s own FieldConfigurationComponent and DefaultSettingsComponent; the repository carries a vendored copy of shared-utilities (no .gitmodules), so kb/tools/gates.py does not apply.

There is no working setting in this applet. All three settings screens are the dead shapes catalogued in kb/topics/mock-screens-shipped-in-production.md:

models/applet-settings.model.ts declares roughly 250 keys (HIDE_UNIT_PRICE_STD_PRICING_SCHEME, ENABLE_CUSTOM_STATUS_1, HIDE_PURCHASE_ORDER_QUEUE_MENU, …). It is the model of a purchase document applet copied wholesale; none of those keys has a control here.

Keys read at runtime without a control in this applet — they come from the session’s master settings, which this applet never writes:

One thing that does persist: the chart type you last chose for a report is written to localStorage under the key chart_preferences (UserPreferenceService). It is per browser profile, not per user, and it is not on the server.

Document behaviour settings

Not a document applet. execution_mode (IMMEDIATE / SCHEDULED) is saved on a query template and read by nothing in the backend — the only occurrence of the column outside the table class is the Liquibase changelog that created it. Scheduling is done by Query Events, not by this flag.

Feature visibility / permissions

bl_applet_client_side_perm_dfn has no rows for this applet code (checked 2026-09-16), so the client-side permission listing under Settings is empty and nothing in the UI is gated per role. All gating is server-side, on the TntDgmktPermissions BI families; each endpoint accepts the ADMIN and OWNER variants alongside the one named.

Fields

Dynamic Report — Details

The view’s Details tab adds the read-only Created By / Creation Date / Modified By / Modified Date.

Query Template

Both SAVE and EXECUTE are [disabled]="form.invalid", so a template cannot be saved or executed until a value has been picked on the Stored Procedure tab. The tab’s label turns red while it is empty.

Query Run and Query Event (unlinked screens)

Dashboard block

Configured in the Configure Chart panel and saved to bl_bi_dashboard_blocks:

A dashboard is saved only when you press Save; there is no auto-save. Undo / redo on the canvas is in-memory and lost on reload.

Lifecycle and effects

Not a generic document: no *DataConsistencyObject signums, no JournalPostingTypeHandler entry, no stock processor, no open queue. The applet only reads business data and only writes its own eight tables plus one CSV object per run. The create-time validators on templates, runs and events check GUID, status, revision, created/updated subject and dates, and that referenced template / event / report GUIDs exist — nothing validates the SQL text on save.

What happens when you save a dynamic report

1. The applet calls hdrs/execute-report/backoffice-ep (POST) with the header. The controller takes sql_query, or the template’s filter_query when sql_query is blank, and rejects with “SQL query is required, either directly in ‘sql_query’ field or via a ’template_hdr_guid’.” if both are empty (BiReportHdrController.java:265).
2. BiReportHdrService.createManualRunHeader runs validateRawSql (BiReportHdrService.java:91-112): the lower-cased, trimmed text must start with select or with — otherwise “Only SELECT statements are allowed for raw SQL execution.”; it must not name any of the restricted tables (“Access to restricted table detected:”); and SQLInjectionChecker.isQuerySafe must find none of the whole words insert, update, delete, drop, truncate, alter anywhere in the text (“Potentially unsafe SQL detected.”).
   A bl_bi_report_query_run_hdr row is written with generated_sql_query and run_name = "<context> _ executed by <subject guid>".
   createRunOutputHdrAndGenerateCsv executes the statement with SqlQueryExecutor.executeSelect — jdbi.createQuery(sql).mapToMap().list(), no row limit, no timeout, no parameter binding — and stores the entire result under bl_bi_report_query_run_output_hdr.data_json with query_status = SUCCESS, or the error message and the SQL with query_status = FAILED.
   When the query succeeded, BiReportQueryRunDynamicCsvService writes the rows to report_<run_name>.csv, uploads it to file storage and records a bl_bi_report_query_run_output_file_hdr.
   The report header is saved with sql_query and output_hdr_guid pointing at the output.

   All of this happens inside the HTTP request, in one transaction. A slow query is a slow save.

   Saving changes from the view’s Details tab calls the same endpoint with PUT: it needs the existing output_hdr_guid (“output_hdr_guid is required for update.”), applies the same validation, creates a new run and a new output and CSV, and repoints the header. The previous output rows are left in place, not deleted.

   The restricted tables

   app_login_subject, app_login_password, app_login_principal, app_generic_resources_hdr, app_login_registration, bl_applet_config, bl_applet_catalog_hdr, bl_applet_trigger_config_hdr, app_mst_role, app_perm_set_hdr (RestrictedTables.java). The check is a whole-word match on the lower-cased statement, so a comment or string literal containing one of those names is also rejected.

   EXECUTE on a query template

   query-template-hdrs/execute-query/backoffice-ep applies the same three checks — with slightly different messages (“Only SELECT or WITH queries are allowed.”, “Access to one or more restricted tables is not allowed.”, “Potentially unsafe SQL (INSERT/UPDATE/DELETE/etc.) detected.”) — and returns the rows to the grid. It stores nothing: no run, no output, no CSV.

   Scheduled runs (Query Events)

   Creating or updating a Query Event goes through query-event-hdrs/processors/backoffice-ep, which saves the event, expands rrule into a recurring group (BiReportEventHdrRecurringService), and enqueues BI_REPORT_QUERY_RUN_PROCESSOR. The processor picks one of three scenarios: an event with a template auto-creates a run, then output, CSV and a report header (scenario 1); a run created with a template produces output, CSV and report (scenario 2); a run created without a template executes its own generated_sql_query (scenario 3). An event without a template is logged and skipped.

   The processor path executes without validateRawSql. BiReportQueryRunProcessorHelperMethods takes generated_sql_query, or the template’s filter_query, and hands it straight to executeSelect (lines 155-166). The SQL text got there through the plain CRUD endpoints, whose validators never look at it. So the SELECT-only, restricted-table and keyword guards protect the two interactive paths and not the scheduled one. Recorded as a product defect; until it is fixed, treat API_BI_REPORT_QUERY_TEMPLATE_HDR_CREATE plus either API_BI_REPORT_QUERY_EVENT_HDR_CREATE or API_BI_REPORT_QUERY_RUN_HDR_CREATE as the ability to run any statement against the tenant database.

   Export

   The Report tab’s Export button downloads query-run-output-file-hdrs/export-csv/backoffice-ep/{guid} and saves it as <guid>.pdf (dynamic-report.effects.ts:198). The content is the CSV; rename the file to .csv to open it.

   Dashboards

   A dashboard and its blocks are saved together to dashboard-blocks-hdr/backoffice-ep (bl_bi_dashboard + bl_bi_dashboard_blocks[]). Blocks do not re-run the SQL: each reads its report’s stored output rows through query-output-hdrs and aggregates them in the browser. A dashboard therefore shows the data as of the report’s last run, and refreshes only when the report is saved again.

   Related applets

   • Financial Report — the pre-built financial statements; use Dynamic Report only for a cut those do not offer.
   • Sales Report and Purchase Report — the fixed grids over FINAL documents, with the cost-basis and gross-profit columns Dynamic Report cannot compute for you.
   • Debtor and Creditor Report — ageing and outstanding balances, pre-built.
   • Stock Report — stock balances and movements.

   Troubleshooting

   Symptom	Cause	Fix
   Query Template: SAVE and EXECUTE stay greyed out although Template Name and SQL Query are filled	The Stored Procedure tab’s drop-down is Validators.required and both buttons are disabled on form.invalid	Open the Stored Procedure tab and pick any value; it is saved and never read
   “Only SELECT statements are allowed for raw SQL execution.” / “Only SELECT or WITH queries are allowed.”	The trimmed statement does not begin with select or with — including a leading comment line or a SET	Start the text with SELECT or WITH
   “Potentially unsafe SQL detected.” on a pure SELECT	The keyword check is a whole-word regex over the whole text: a column alias or string literal such as 'delete', AS update, or a table named ..._alter trips it; updated_date does not	Rename the alias or literal
   “Access to restricted table detected: …”	The statement names one of the ten restricted tables, even inside a comment	Remove the reference
   Save on a dynamic report hangs, then fails with the 500 “An internal error occurred during execution.”	The query runs synchronously inside the request with no row limit or timeout, and the full result is serialised into data_json	Add a LIMIT and a date filter; test with EXECUTE on a template first
   Report tab is empty after a save	query_status on the output is FAILED; the error and the SQL are in data_json and the CSV step is skipped	Fix the SQL and save again — each save creates a new run and output
   The exported file will not open	It is downloaded as <guid>.pdf although the endpoint returns CSV	Rename the extension to .csv
   A dashboard block shows stale numbers	Blocks read the report’s stored output, not the database	Open the report, save it again (re-runs the SQL), then reload the dashboard
   A dashboard block shows nothing after picking a report	The report’s last run failed, or its output has no rows with the chosen columns	Check the Report tab of that report first
   Someone else’s dashboard is visible to everyone	visibility_type is never set and the permission controllers are never called	No control today (issues #20 / #21 in the applet’s repository)
   Personalization → Field Settings shows a 404	The field-settings route is registered under settings, not personalization	Use Settings → Field Settings — which is itself unbound and saves nothing
   Changing Default Branch / Location in Default Selection throws in the console and saves nothing	Both Default Selection screens are directly routed components whose @Input() container is never bound	There is no working default in this applet
   Query Run / Query Event screens cannot be found in the menu	Their menuItems entries are commented out	Append /query-run or /query-event to the applet URL
   A Query Event with a recurrence rule never produces a run	No job processor is consuming BI_REPORT_QUERY_RUN_PROCESSOR, or the event has no template (“Event … must have template_hdr_guid to auto-create run!”)	Attach a template; confirm the processor is enabled on the platform

   Related documentation

   • Finance applets
   • Financial Report
   • Sales Report