Dynamic Report

For the one-off question the fixed report applets do not answer: you write a SELECT against the tenant database, run it, and the result is kept as rows and a CSV file under a run, with dashboards that chart a report’s stored result. It writes nothing to the ledger, the stock balance or any business document. Nor does it share or hide: every dashboard is visible to every user who holds the applet’s dashboard read permission, and nothing on the screen makes one private to its author or to a team. The query runs while you wait for the save, with no row limit and no timeout, and every figure you see afterwards is a stored copy from the report’s last save or refresh — nothing re-runs it on a clock.

Your job, and the screen for it

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 report screens accept only statements that begin with SELECT or WITH, reject the write keywords, and refuse the platform’s login and permission records. 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 sidebar has three entries; the applet lands on Dashboard by default. Owners and admins also see the shared Audit Trail entry.

Two more screens exist and are reachable only by typing the URL — they have no menu entry:

Both are the scheduling half of the product, and scheduling does not work today. An event’s occurrences that are already due when you save it run at once; every occurrence dated in the future is put in a waiting list that nothing ever reads, so it never runs. A run saved without a template is stored and never executed. BigLedger intends scheduling to be visible and working, so that you can have a report run on a schedule and e-mailed to you; until then, refresh a report yourself (save it again, or press a dashboard block’s ↻).

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 the screen only exists under Settings — and Default Selection.

Configuration

Before you can use it

Applet settings

There is no working setting in this applet. All three settings screens are placeholders:

Layout keys read without a control here — they come from your session’s master settings, which this applet never writes: the vertical / horizontal orientation of the view column and the single / double column layout.

One thing that does persist: the chart type you last chose for a report is remembered by your browser. 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 — nothing runs, defers or queues a template because of the value, no listing column shows it and no search filters by it.

Who can change what

This applet defines no client-side permissions, so the client-side permission listing under Settings is empty and nothing in the UI is gated per role. All gating is server-side; each action also accepts the ADMIN and OWNER variants alongside the permission 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 stay disabled while the form is 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 with the dashboard:

Where you drag and resize a block on the canvas is saved with it. 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 document: no signums, no journal posting, no stock movement, no open queue. The applet only reads business data and only writes its own records plus one CSV file per run. The checks on saving a template, run or event confirm the record is well-formed and that the referenced template / event / report exists — nothing validates the SQL text on save.

What happens when you save a dynamic report

1. The server takes the SQL Query, or the template’s SQL 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.
2. It validates the text: it must start with select or with — otherwise “Only SELECT statements are allowed for raw SQL execution.”; it must not name a restricted table (“Access to restricted table detected:”); and it must contain none of the whole words insert, update, delete, drop, truncate, alter anywhere in the text (“Potentially unsafe SQL detected.”).
   A run is recorded, named " _ executed by ".
   The statement is executed — no row limit, no timeout, no parameter binding — and the entire result is stored under the run’s output as a success, or the error message and the SQL as a failure.
   When the query succeeded, the rows are written to report_<run_name>.csv, uploaded to file storage and recorded as the run’s output file.
   The report is saved pointing at that output.

   All of this happens while you wait for the save, in one transaction. A slow query is a slow save.

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

   The restricted tables

   Saving a dynamic report, and EXECUTE on a query template, refuse a statement that names the platform’s login, password, role, permission and applet-configuration records. The check matches whole words anywhere in the statement, so a comment or string literal containing one of those names is also rejected. A Query Run or Query Event does not apply it: saving a template checks nothing, and the run executes the template’s SQL as saved — see the warning under Scheduled runs.

   EXECUTE on a query template

   EXECUTE applies the same three checks and returns the rows to the grid. It stores nothing: no run, no output, no CSV. A refused or failing query shows an empty grid with no message — the server answers with the reason (“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.”, or the database’s own error) inside a reply the screen treats as success. An empty grid from EXECUTE therefore means “no rows or refused”; to see the reason, save the SQL as a dynamic report, where a refusal is shown.

   Scheduled runs (Query Events)

   Creating or updating a Query Event saves the event and expands its recurrence rule into the individual occurrences. Occurrences already due run at once: an event with a template auto-creates a run, then output, CSV and a report header. Occurrences dated in the future are stored in a waiting list that nothing reads, and never run — so a recurring event produces its first due runs and then nothing. A run created with a template runs at once and produces output, CSV and report; a run created without a template is saved and never executed. An event without a template is logged and skipped. Nothing e-mails a result.

   Treat the permission to create query templates together with query runs or query events as access to the whole tenant database. Grant API_BI_REPORT_QUERY_TEMPLATE_HDR_CREATE, API_BI_REPORT_QUERY_RUN_HDR_CREATE and API_BI_REPORT_QUERY_EVENT_HDR_CREATE only to people you would trust with direct access to all of your company’s data, and review who holds them. The checks described above protect the screens; do not rely on them as the only control.

   Export

   Export downloads nothing today. The Report tab’s Export button asks for the result’s file by the wrong reference, the server answers not found, and the screen shows no message. The Report tab’s grid has its own CSV export switched off, so there is no working download on this screen. The save did write the result as a CSV file to file storage — ask support for it — or fetch the rows through the API. Read from the code, not tried.

   Dashboards

   A dashboard and its blocks are saved together. Blocks do not re-run the SQL by themselves: each reads its report’s stored output rows and aggregates them in the browser. A dashboard therefore shows the data as of the report’s last run — its last save, or the last time anyone pressed a block’s ↻, which re-runs the report for everyone.

   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 required and both buttons stay disabled while the form is invalid	Open the Stored Procedure tab and pick any value; it is saved, re-displayed on the template view, and read by nothing that runs a query
   “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 a restricted table, 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 while you wait, with no row limit or timeout, and the full result is stored	Add a LIMIT and a date filter; test with EXECUTE on a template first
   Report tab is empty after a save	The run failed; the error and the SQL are stored with the output and the CSV step is skipped	Fix the SQL and save again — each save creates a new run and output
   Export does nothing — no file, no message	The button asks for the result file by the wrong reference and the failure is not shown	No download works from this screen today. Ask support for the CSV file the save wrote, or fetch the rows through the API
   A colleague can see my dashboard, or I can see everyone’s	The applet lists dashboards through the back-office endpoint, which filters by permission only. Dashboards do carry a private / internal visibility and per-user access rows in the database, but the applet never sets them and only the login endpoint honours them	No setting does it. Treat every dashboard as visible to every user with the dashboard read permission; the visibility model that exists is reachable only through the API
   EXECUTE shows an empty grid for a query you expected rows from	A refused or failing query is answered as success with no rows, so the screen shows nothing	Save the SQL as a dynamic report to see the reason, fix it, then EXECUTE again
   The report’s Code, Description or dates are blank after saving	The save keeps only the name, SQL, template and result	Put what you need into the name; put the date range in the SQL
   A dashboard block shows stale numbers	Blocks read the report’s stored output, not the database	Press the block’s ↻ (re-runs the report for every viewer), or open the report and save it again
   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
   Personalization → Field Settings shows a 404	The screen only exists under Settings	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 not connected to the record they would write	There is no working default in this applet
   Query Run / Query Event screens are missing from the menu	They have no menu entry	Append /query-run or /query-event to the applet URL
   A Query Event with a recurrence rule never produces a run after the first	Future occurrences wait in a list nothing reads — scheduling does not work today. With no template the event never runs at all (“Event … must have template_hdr_guid to auto-create run!”)	Refresh the report yourself (save it, or a block’s ↻). Enabling a job in the Scheduler does not help

   Related documentation

   • Finance applets
   • Financial Report
   • Sales Report