Edit Reports: Edit SQL

Features: Code Editor

Syntax Highlighting: Colour-codes SQL keywords, comments, numbers and strings for better readability.
Line Numbers: Displays line numbers for easy reference.
Tab Support: Uses a tab width of two for consistent indentation.

Edit SQL interface with the Tables, Functions and Procedure tabs, a query in the SQL editor and the buttons labelled — Edit SQL Interface

Buttons Explained

Button	Description
Template	Inserts a starting SQL for the field being edited (for example, for a report filter, a query that returns `filter_key` and `filter_value` with an All row). The button appears only where a template exists: when you edit the SQL of a report filter, an attribute or an action. It is hidden when you edit the report’s own SQL. If the editor already contains SQL, the message “SQL Template will replace existing SQL, Continue?” appears first.
Test	Tests the entered SQL by running it. If the SQL runs, the message “Test passed. No Parameters found.” appears, or “Test passed.” followed by a list of the parameters the SQL uses. If the SQL fails, the database error message appears.
Clear	Clears the field.
Save	Saves any changes.
Cancel	Discards the changes.
X (top corner)	Closes the SQL popup and generates the fields. Warning: Depending on how complex the query is, there may be a few seconds’ delay between closing the popup and the fields being generated.

Tabs Explained

Tab	Description
Tables	Lists all the tables in the database (excluding system tables). Includes a context menu (right-click) with these options: Create Select SQL: Creates the Select SQL for that table (a list of fields from which you can select the fields you need). Show Fields: Opens a column showing all the fields that are linked to the table. Double-click a field to add it to the note section (that is, the section where the SQL is edited).
Functions	Lists user-defined, selectable PostgreSQL functions. Double-click a function to insert its name. (Functions are queries that return specific values.) Includes a context menu (right-click): Create Select SQL: Creates the Select SQL for the query. Unlike on the Tables tab, the SQL also includes the action.
Procedures	Lists the user-defined PostgreSQL procedures. Double-click a procedure to insert its name. Includes a context menu (right-click): Generate SQL: Replaces the SQL with a `CALL` statement for the procedure, with one parameter per line and its data type as a comment. If the editor already contains SQL, the message “Select SQL will replace existing SQL, Continue?” appears first.
Params	Lists query parameters (e.g. `:active_user_id`) and user-defined parameters from a provided list. Double-click a parameter to insert it into the query.

Tips: Creating SQL Queries

Parameter Handling: Parameters use a colon prefix (e.g. :param_name). Test queries to confirm parameters are valid.
Performance: A wait cursor appears during database operations to indicate processing.

Limitations: SQL

The editor is designed for PostgreSQL and may not support other database systems.
The Test feature may not catch all runtime errors.
Navigation buttons are hidden if no grid or tree view is linked.

Troubleshooting: SQL

Error on Test: Check SQL syntax and ensure parameters are valid. The error message provides some details.
Empty Table/Function List: Confirm the database connection is active and the schema is accessible.
Save Button Disabled: The query matches the saved version. Edit the query to enable the Save or Cancel buttons.