SQL Building

Owned by Aprile Keith, created
Last updated: Jun 14, 2024
5 min read

Search our help articles

SQL Building is a mode in the IntelliBoard Report Visual Builder that allows users to write custom sql code that is processed in order to retrieve data from a database and populate each Data Element. Within this mode, users can review the generated SQL code by accessing the SQL Code tab while editing the report.

To access SQL code in a report:

  1. Open the report you wish to edit SQL code in “My Datasets.”

  2. Click the “Edit” button in the top right corner.

  3. Click the “SQL Code” tab in the top left corner next to “Preview.”

  4. The SQL window will open displaying all the SQL code in the report. Click “Edit” in the top right corner to edit the code.

  5. Click “Save” to save your changes or “Reset” to discard edits and return to the last version of the report.

SQL Builder.mp4

The sql code supports some special constructions that will be parsed by Builder engine and transformed 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. Click items from the list to add them to the code at the cursor.

SQL Editor.png

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.

This placeholder will be replaced with the list of enabled teacher roles in connection settings. Can be used as a value in filters.

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.

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.

available assignments types:

  • 1 - course

  • 2 - user

  • 3 - category

  • 4 - term

  • 5 - org units

  • 6 - cohorts

  • 7 - deprecated

  • 8 - MWP tenant (Moodle)

  • 9 - MWP program (Moodle)

  • 10 - Learning plan (Moodle)

  • 11 - deprecated

  • 12 - Parent (Moodle)

Filters

Runtime filters can be added to SQL queries by the use of the following constructions:

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.

Such constructions are used to create Date filters. In this construction it’s important to use the correct column name before the BETWEEN operator and the same text for the two tokens, with the postfixes _start and _end.

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 - 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

 

Additional Visual Builder Articles:

 

For additional support, email us at helpdesk@IntelliBoard.net
Provide Feedback on Support Articles