SQL Building
SQL Building is the mode in the IntelliBoard Report Builder that allows user to write custom sql code that will be processed by Builder engine in order to retrieve data from database and populate each Data Element. Every report created with Report Builder allows the user to review the SQL code generated by the Builder by clicking the SQL Code tab while editing the report.
If the user wishes to edit this code, they must click the “Edit” button on the SQL Code tab. If the user alters code and clicks “Save” the report will be saved in “SQL Mode.
By enabling this mode some features, such as managing columns and filters through visual interface will be disabled. Edits can be discarded and the report can be returned to Visual Builder mode by clicking “Reset” in the SQL Code tab after making changes. This will return the report to the last version editable by the Visual Builder (if any). All edits to the SQL code will be lost.
The sql code supports some special constructions that will be parsed by Builder engine and transfromed before processing as part of SQL code.
The sql code supports most features that are available in PostgreSQL.
The data model is provided in a panel in to the right of the SQL editor:
Single-click items from the list to add them to the code at the cursor.
Placeholders
The following placeholders can be added to any SQL report. Some are required for compatibility with the Report Builder as noted below.
:limit - Used to limit output amount of data. Required for Table charts. When the SQL code runs, this placeholder will be replaced with code supporting the “Per Page“ selector during viewing of Data Element. Add it to your code like this:
LIMIT :limit
:offset - Used to offset the starting row in the display to support pagination. Required for Table charts. When the SQL code runs, this placeholder will be replaced with a formula combining the :limit value and the current page number, that depends on current page: (<current_page> - 1) * <limit>. Used to skip particular amount of data, before retrieving it.
Add it to your code like this:
OFFSET :offset
:order - Used to support sorting during display of the report. Required for Table charts. When the code runs in SQL, this placeholder will be replaced with code for sorting data, if data must be sorted in some way (for example user clicked on header of the column in table). If there is no active sorting imposed by the user, this token will be removed from from the query before it is executed.
:learner_roles - This placeholder will be replaced with the list of enabled learner roles in connection settings. Can be used as a value in filters.
:teacher_roles - This placeholder will be replaced with the list of enabled teacher roles in connection settings. Can be used as a value in filters.
:is_main_account - This placeholder will be replaced with TRUE or FALSE values depending on whether the account viewing the report is the main subscriber or IB User.
:lms_user_email - This placeholder will be replaced with email of current user who is viewing the report.
Assignments
In order to support IB User limitations for SQL reports, user can incorporate special placeholders into filters. These will be replaced with IB User restrictions if this report is viewed by an IB User.
:filter_permission_X, where X - assignment type
available assignments types:
· 1 - course
· 2 - user
· 3 - category
· 4 - term
· 5 - org units
· 6 - cohorts
· 7 -
· 8 - MWP tenant (Moodle)
· 9 - MWP program (Moodle)
· 10 - Learning plan (Moodle)
· 11 -
· 12 - Parent (Moodle)
Filters
Runtime filters can be added to SQL queries by the use of the following constructions: courses.id in (:course) - This construction is used to create select filters in SQL reports. For example, the Report Builder engine will parse the column courses.id and create a select filter on
the report that will be populated with the names of courses based on the values of courses.id. The column that will be used as labels in select filters in SQL report is defined in the product code for each table. For the Courses table it will be Course Name. For select filters the name of the placeholder (:course in our example) has no visible effect but should be unique within the SQL report. The token can be entered as any non-reserved word with colon before it.
COALESCE(users.signup_at, 0) BETWEEN :created_at_start and :created_at_end - such constructions are used to create Date filters. In this construction it’s important to use the correct column name before the BETWEEN operator the same text for the two tokens, with the postfixes _start and _end. The first part of placeholder can be any word.
(users.user_name LIKE :filter_search_column__users_user_name OR <other columns>) … - with this approach user can define search filters. The list of columns will be separated by OR statements and the whole filter should be wrapped in parentheses.
SQL Advanced Filters
In cases where the above constructions for select filters are not sufficient, there is a way to define a select filter by creating special JSON construction. It allows, for example, to create a filter with custom select options. To add a this filter use the next construction
({FIELD_NAME}) IN (:{"id": {FILTER_ID}, "sql": "{FILTER_SQL}", "label": "FILTER_LABEL"})
FIELD_NAME - a field by which the report data should be filtered FILTER_ID - unique identifier in the report’s query. you can use the same identifier for several filters in one query, but on the page it will be one filter FILTER_SQL - SQL, which will be used to select data for the filter. The SQL must return two columns: id and title FILTER_LABEL - Label of the filter on the page
some SQL
...
WHERE (t.status) IN (:{"id": 1, "sql": "SELECT 'Completed' AS id, 'Completed' AS title UNION SELECT 'In Progress' AS id, 'In Progress' AS title UNION SELECT 'Not Started' AS id, 'Not Started' AS title", "label": "Status Filter"})